Home > Validation > Rules > Dates Excel Data Validation for DatesHow 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 - IntroductionBy 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:
Written instructions, and the sample file, are below the video. |
Set Date Validation RulesAfter you select the Date option, 3 new boxes appear in the Data Validation dialog box.
For this example, we want to set a date range for an entire year.
|
Enter Start and End DatesFollow these steps, to enter the date range rule settings:
|
Set up the Data Validation
|
Test the Data Validation
|
Create Dynamic Drop Down List of DatesIf 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, TomorrowIn this example, the dynamic list shows the dates for yesterday, today and tomorrow. |
Create the List of Valid DatesOn another sheet in the workbook, you'll create a list of valid dates. In this example, the sheet with the list is named AdminLists.
|
Create Data Validation Drop Down List
|
Test the Data Validation
|
Example 2: Next 3 Working DaysIn this example, the dynamic list shows the dates for the next 3 working days.
|
Create the List of Valid DatesOn another sheet in the workbook, you'll create a list of valid dates. In this example, the sheet with the list is named AdminLists.
|
|
Create Data Validation Drop Down List
Test the Data Validation
|
Get the Sample FileTo get the sample file, click here: Data Validation for Dates The zipped file is in xlsx format, and does not contain any macros. |
Prevent entry of weekend dates
Last updated: August 7, 2023 11:58 AM