Data validation in Excel 2007 – Date range

If you want to validate a cell to restrict data entry to a date range then follow these steps:

1. Select the cell you want to validate.
2. On the Data tab, in the Data Tools group, click Data Validation.



3. The Data Validation dialog box will open.



4. In the Data Validation dialog box, click the Settings tab.
5. Click on the Allow box then select Date from the drop-down list.
6. Click the Data box and then select an option from the drop down list e.g. “Between” , “Greater than”, …etc.
7. In the Start Date box enter a valid date compatible with your regional settings for the date.
8. In the End Date box enter a valid date compatible with your regional settings for the date (this box is not applicable for some options).
9. You can enter a formula that returns a date for Start Date or End Date, for example “=Today()+7″.

Notes:

1. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
2. If your allowed values are based on a cell range that has a defined name and there is a blank cell anywhere in that range, selecting the Ignore blank check box allows any value to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, selecting the Ignore blank check box allows any value to be entered in the validated cell.

More Validation options:

1. Display an input message when the cell is clicked.
   1. Click the Input Message tab.
   2. Select Show input message when cell is selected check box.
   3. Fill in the Title and text for the Input message.
2. Display an error message when wrong data is entered.
   1. Click the Error Alert tab.
   2. Select Show error alert after invalid data is entered check box.
   3. Fill in the Title and text for the Error message.
   4. Select one of the following options for the Style box:
      1. Information: Display an information message. Does not prevent entry of invalid data.
      2. Warning: Display a warning message. Does not prevent entry of invalid data.
      3. Stop : Prevent entry of invalid data.

Tip

If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so

1. Open the Data Validation dialog box.
2. Click the Settings tab.
3. Select the Apply these changes to all other cells with the same settings check box.

Related posts:

1. Data validation in Excel 2007 – Drop down list
2. How to validate a cell value
3. How to Find cells that have data validation
4. How to validate a cell based on the value of another cell
5. Find cells that meet specific criteria in Excel 2007