Tom’s Tutorials For Excel: Validating an Entry as a Real Date
One way to verify that a bona fide date is being entered into a cell is to use Data Validation.
In the pictured example, dates are being entered into a list in column E. The attempted entry in cell E6 is being rejected because it is not a date.
Step 1 of 4: Before you enter dates, set up your worksheet:
• Start by selecting the range where dates will be entered.
• Then, from your keyboard press Alt+D+L to show the Data Validation dialog box.
Step 2 of 4: In the Data Validation dialog box:
• Go to the Settings tab.
• Click the drop-down arrow for the Allow field.
• In the list of allowable settings, click to select Custom.
Step 3 of 4: Still in the Data Validation dialog box:
In the Formula field, enter your formula rule.
Notice the range being Data Validated (the selected range in Step 1) is E3:E16.
The Data Validation formula being used, with cell E3 as the active cell in that selection, is
=AND(ISNUMBER(E3),LEFT(CELL("format",E3),1)="D")
Step 4 of 4: Still in the Data Validation dialog box:
• Click onto the Error Alert tab.
• Click to select the option for (that is, click to put a check in the box next to) “Show error alert after invalid data is entered”.
• For the Style field, click the drop-down arrow and select “Stop”.
• In the Title field, enter a short headline such as you see here, and as you see in practice in the picture at the top of this tutorial.
• In the Error message field, enter an informative explanation as to why the attempted entry is being rejected, and what the user should do to correct that action.
Why not just pick the DATE option in the data validation menu; providing possible start and end dates that are well outside any possible dates one may enter?
It only allows dates — no text, or dates that can’t exist like 2/31/2004
Well, you raise a good point. I did not think about that approach to validating for a date. Thanks for your post.
One reason not to use the date option in data validation is because you have to define a start and end date which can be problematic. So I like Tom’s way. I always wonder why MS made the date option like this and not just accept valid dates.
It is useful to have actual start and end dates for validation to prevent dates that, for instance, haven’t yet occurred from being entered in a date-sensitive context. Restricting the range of dates prevents awful mistakes with years, like 2061, or 2106 instead of 2016 (this I see regularly with my own eyes). A bit late replying here, but I just came across this site.
Thanks for your comment. I’ll be adding more examples in 2017.