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