Home > Validation > Rules > 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.

Enter Start and End Dates

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

1. On any sheet in the workbook, type "StartDate" in one cell, and type "EndDate" in the cell below.
2. In the cells to the right of those labels, type a starting date and an ending date for the valid date range.
3. Select all four cells, and on the Excel Ribbon, click the Formulas tab
4. Click Create from Selection, and in the dialog box, check Left Column, then click OK
5. The cells are named, based on the labels in the first column -- StartDate and EndDate

Set up the Data Validation

1. Select cell C4, and on the Excel Ribbon, click the Data tab
2. Click Data Validation (click the upper section of the command)

   

3. On the Settings tab of the data validation dialog box, from the Allow drop down, click Date

   

4. 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.
5. 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.
6. Click in the the End Date box, press F3, then double-click on EndDate, to select it.
7. Click OK, to close the data validation dialog box.

Test the Data Validation

1. To test the data validation, enter a date that is not in the year, such as January 1, 2016, and press Enter.
2. 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.

   

3. Click Retry or Cancel, and enter a date that is in the year 2017.
4. 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.

1. On the AdminLists sheet, in cell C2, enter this formula, to calculate yesterday's date: 
   =TODAY() - 1

   

2. In cell C3, enter the formula for the current day's date: =C2 + 1

   

3. 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.

   

4. 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 Data Validation Drop Down List

1. On the data entry sheet, select cell C4, where the drop down list of dates will be added.
2. On the Excel Ribbon, click the Data tab
3. Click Data Validation (click the upper section of the command)
4. On the Settings tab of the data validation dialog box, from the Allow drop down, click List



5. In the Source box, type an equal sign, and the name of the list: =DateList

   

6. Click OK to close the data validation dialog box.

Test the Data Validation

1. Cell C4 now has a drop down arrow, so you can select one of the valid dates.

   

2. 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 Data Validation Drop Down List

1. On the data entry sheet, select B3, where the drop down list of dates will be added.
2. On the Excel Ribbon, click the Data tab
3. Click Data Validation (click the upper section of the command)
4. On the Settings tab of the data validation dialog box, from the Allow drop down, click List
5. In the Source box, type an equal sign, and the name of the list: =DateListWork
6. 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.

1. On the data entry sheet (the CurrentYr sheet in the sample file), select cell C4, where date will be entered.
2. On the Excel Ribbon, click the Data tab
3. Click Data Validation (click the upper section of the command)
4. On the Settings tab of the data validation dialog box, from the Allow drop down, click Custom
5. 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())
6. 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.