|
|
Excel Data Validation -- Dates
By using data validation, you can
limit the entries for one or more cells in an Excel worksheet. For
example, you could
Limit Entries to Specific Date Range
In this example, employees will fill in a Vacation Request Form for
the year 2013. In cell C4, the employee will enter a starting date
for the vacation.
To ensure that they enter a date in the year 2013, you can apply
data validation to that cell.
- Select cell C4, and on the Excel Ribbon, click the Data tab
- Click Data Validation (click the upper section of the command)
- On the Settings tab of the data validation dialog box, from the
Allow drop down, click Date
- In the Data drop down, leave the default setting of Between, because
we want to limit the entries to dates between specific start and
end dates.
- Enter the Start Date and End Date for your date range. In this
example, any date in the year 2013 is valid, so January 1, 2013
is the start date and December 31, 2013 is the end date.
- Click OK, to close the data validation dialog box.
Test the Data Validation
- To test the data validation, enter a date that is not in the year,
such as January 1, 2014, and press Enter.
- An error message appears, and blocks the invalid entry. Note:
You can customize the message that appears for invalid entries,
by using the data
validation Error Alert tab.
- Click Retry or Cancel, and enter a date that is in the year 2013.
- The entry is accepted, and no message appears.
Create Dynamic Drop Down List of Dates
If only a short range of dates is valid, such as yesterday, today
and tomorrow, you can create a drop down list of those dates. By using
formulas, the drop down list will be dynamic, and will show the correct
dates whenever the workbook is opened.
Create the List of Valid Dates
On another sheet in the workbook, you'll create a list of valid dates.
In this example, the sheet with the list is named AdminLists.
- On the AdminLists sheet, in cell C2, enter this formula, to calculate
yesterday's date: =TODAY() - 1
- In cell C3, enter the formula for the current day's date: =C2
+ 1
- Copy the formul from C3, down to C4, to complete the list of dates.
- To name the list, select all three date cells -- C2:C4 -- then
click in the Name Box, and type the one-word name -- DateList --
then press the Enter key
Create the Data Validation Drop Down List
- On the data entry sheet, select cell C3, where the drop down list
of dates will be added.
- On the Excel Ribbon, click the Data tab
- Click Data Validation (click the upper section of the command)
- On the Settings tab of the data validation dialog box, from the
Allow drop down, click List
- In the Source box, type an equal sign, and the name of the list:
=DateList
- Click OK to close the data validation dialog box.
Test the Data Validation
- Cell C2 now has a drop down arrow, so you can select one of the
valid dates.
- If you type an invalid date in the cell, you'll see an error message.
|
