Software

Prevent users from inadvertently entering the wrong year in a worksheet

After typing 2008 for the past year, users are likely to slip up and type it instead of 2009 once January rolls around. Here's a simple Excel data validation trick to help catch those errors.

Old habits die hard. This is especially true when it comes to entering dates in January. Fortunately, Excel provides a way to prevent users from mistakenly entering 1/1/2008 when they should be entering 1/1/2009. Follow these steps when setting up your worksheets for data entry next year:

  1. Select the range of cells where users will enter a date.
  2. Go to Data | Validation. (In Excel 2007, click the Data tab and then click Data Validation in the Data Tools group.)
  3. Click on the Settings tab.
  4. Click the drop-down arrow in the Allow box and select Date.
  5. Click the drop-down arrow in the Data box and select Greater Than Or Equal To.
  6. Under Start date, enter 1/1/2009, as shown in Figure A, and click OK.

Figure A

data validation

Now, when a user enters 2008 instead of 2009, Excel displays the message shown in Figure B.

Figure B

error message

Editor's Picks