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

7 comments
Willie11
Willie11

I'm thinking you should compare the information entered to the current date and insure that it is in the same year or in the future. Otherwise you'll be editing the spreadsheet every year to make sure they don't screw up.

kris.willison_klm
kris.willison_klm

Various different programs, I have been at the wrong end of dealing w/ incorrect dates such as PO dated 20 to 100 years in the future & other items dated in the distant past. What about a test for a valid date range BETWEEN =today() - ## AND =today() + ## Use an error message that indicates that the date entered is outside the valid range. This would allow for dates near the 1st of the year that might appropriately be last year's data & work near the end of the year that might be next year's data w/out having to re-modify the workbook.

david.hanshumaker
david.hanshumaker

The point is to give an example of data validation, Excel defaults to the current year if only M/D is entered. Saves keystrokes too!

michael_boardman
michael_boardman

Mmm I wonder if something like =YEAR(A1)=YEAR(TODAY()) using the Custom option in the Data Validation line might do the job more flexibly?

abhay.sequeira
abhay.sequeira

Great option for an end user to validate data. Thanks & keep us the good work.

mark.fowler
mark.fowler

How about giving the user a sensible error message that spells out the problem rather than the generic one offered in the article. All it requires is clicking on the Error Alert tab and typing something like "Year must be 2009".

demhart
demhart

I get caught on the other side of that one, especially right at the year change. I wanted 12/31/07, but I just typed 12/31, and Excel gleefully changed it to 12/31/08. There's a case where the validation can help make sure the right year is entered.