Home > Validation > > Dates
Excel Data Validation for Dates
How to use data validation in Microsoft Excel to limit the dates that can be entered in a cell, and prevent invalid dates.

|
Data Validation for Dates - Introduction
By using data validation, you can
limit the entries for one or more cells in an Excel worksheet.
In this video, three different methods are used to validate dates. From the Allow drop down in the data validation settings, the following options will be used:
- Specify a starting
date and an ending date. (Date option)
- Show a drop down list of valid dates (List option)
- Create a rule in a custom formula (Custom option)
Written instructions, and the sample file, are below the video.
Limit Entries to Specific Date Range
In this example, employees will fill in a Vacation Request Form for
the year 2017. In cell C4, the employee will enter a starting date
for the vacation.

To ensure that a valid date is entered in cell C4, data validation is used, to set a start date and end date. This example shows two ways to set those dates, and prevent invalid data entry.
Type start and end dates in the rules
Enter start and end dates on a worksheet
Type Dates in the Rules
To ensure that they enter a date in the year 2017, you can apply
data validation to that cell, and type a start and end date in the rules.
To set up the date validation rule, follow these steps:
- Select cell C4
- On the Excel Ribbon, click the Data tab
- In the Data Tools group, click Data Validation (click the upper section of the command)

- On the Settings tab of the data validation dialog box, click the
Allow drop-down arrow
- In the list of validation options, click Date

Set Date Validation Rules
After you select the Date option, 3 new boxes appear in the Data Validation dialog box.
- Date - the "between" setting is automatically entered here
- Start Date - blank
- End Date - blank
For this example, we want to set a date range for an entire year.
- The start date is Jan 1 for that year
- The end date is Dec 31 for that year.
Follow these steps, to enter the date range rule settings:
- In the Data drop down, leave the default setting of Between
- We want to limit the entries to dates between specific start and
end dates.
- In the Start Date box, type the date range start date - January first, in this example
- Use a valid Excel date format, such as 1/1/2017
- In the End Date box, type the date range end date - December 31st, in this example
- Use a valid Excel date format, such as 12/31/2017
- Optional Settings -- see details on Data Validation Messages page:
- Go to the Input Message tab, and add a message to help people input the correct date
- Go to the Error Alert tab, and add a custom error message to help people who have input an incorrect date . Or, turn off the warning message, to allow invalid entries.
- Then, click the OK button, to close the data validation dialog box, and to apply the new data validation rule

Type Dates on a Sheet
To make it easier to see and adjust the data validation rules, you can type the start and end dates on a worksheet. Then, name those cells, and refer to those names in the rules.
Set up the Date Cells
- On any sheet in the workbook, type "StartDate" in one cell, and type "EndDate" in the cell below.
- In the cells to the right of those labels, type a starting date and an ending date for the valid date range.
- Select all four cells, and on the Excel Ribbon, click the Formulas tab
- Click Create from Selection, and in the dialog box, check Left Column, then click OK
- The cells are named, based on the labels in the first column -- StartDate and EndDate

Set up the Data Validation
- 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.
- Click in the the Start Date box, and press the F3 key on the keyboard, to open the Paste Names dialog box, then double-click on StartDate in the list, to select it.
- Click in the the End Date box, press F3, then double-click on EndDate, to select it.
- 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, 2016, 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 2017.
- The entry will be 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.
Example 1: Yesterday, Today, Tomorrow
Example 2: Next 3 Working Days
Example 1: Yesterday, Today, Tomorrow
In this example, the dynamic list shows the dates for yesterday, today and tomorrow.

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. If necessary, format the cells, to show the dates the way you want them to appear.

- 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 C4, 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 C4 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.

Example 2: Next 3 Working Days
In this example, the dynamic list shows the dates for the next 3 working days.
- Weekend dates, for Saturday or Sunday, are not included in the list.
- You can only enter dates that have Monday, Tuesday, Wednesday, Thursday or Friday as day of the week

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 I2, enter this formula, to calculate
the current date:
- =TODAY()
- (optional) Format the cell with a custom number format of ddd mm/dd/yyyy

- In cell H4, type the heading, "Add"
- In cells H5:H7, type the numbers 1, 2, and 3.
- In cell I5, enter this formula, to find the date that is 1 working day after the current date
- =WORKDAY($I$2,H5)
- Copy the date format from cell I2 to cell I5
- Copy the formula down to I6 and I7

- In cell I4, type the heading, DateListWork.
- To name the list, select all three date cells -- I5:I7 -- then
click in the Name Box, and type the one-word name -- DateListWork --
then press the Enter key
Create the Data Validation Drop Down List
- On the data entry sheet, select B3, 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: =DateListWork
- Click OK to close the data validation dialog box.
Test the Data Validation
- Cell B3 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.
Restrict Date to Current Year
In this example, a custom formula is used, and it limits the dates to the current year.
- On the data entry sheet (the CurrentYr sheet in the sample file), select cell C4, where date will be entered.
- 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 Custom
- In the Formula box, type the formula that will compare the year for the date entered in cell C4, with the year for today's date.
=YEAR(C4)=YEAR(TODAY())
- Click OK
To test the data validation, enter a date that is not in the current year.

Get the Sample File
To get the sample file, click here: Data Validation for Dates
The zipped file is in xlsx format, and does not contain any macros.
More Data Validation Examples
Prevent
entry of weekend dates
Data Validation Basics
Create Dependent Lists
Data Validation Criteria Examples
Custom Criteria Examples