Contextures

Excel Data Validation for Dates

How to use data validation 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.

data validation dates 01

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.

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.

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

    data validation command

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

    data validation dialog box Allow

  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. Enter the Start Date and End Date for your date range. In this example, any date in the year 2017 is valid, so January 1, 2017 is the start date and December 31, 2017 is the end date.

    data validation start date

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

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

data validation start date

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)

    data validation command

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

    data validation dialog box Allow

  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.

data validation start date

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.

    data validation error message

  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.

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

    data validation yesterday

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

    data validation today

  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.

    data validation tomorrow

  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

    data validation name list

Create the 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. data validation date allow list

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

    data validation list source

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

    data validation list

  2. If you type an invalid date in the cell, you'll see an error message.

data validation list error

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.

data validation list error

Download the Sample File

To download 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

Search Contextures Sites

 

DVMSP Kit

 

Pivot Power Free

 

Pivot Power Premium

 

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: August 31, 2017 1:24 PM
Contextures RSS Feed