Limit Date Range with Excel Data Validation
Yesterday, one of my clients emailed to let me know that she was having trouble entering January dates in a file that I had created. My first guess was that there was an issue with the regional settings, because her company uses the dd/mm/yyyy format.
But when I tried entering a January date, with my mm/dd/yyyy settings, I got an “Invalid date” message too.
Limit Dates with Data Validation
The date that I had entered – 1/3/13 – was a valid date and in a valid format, so I checked the data validation settings. And that’s where I found the problem.
The cell had been restricted to dates from 60 days prior to the current date:
and up to 60 days after the current date:
Fix the Problem
Those date range settings had made sense when we set up the file. The date range limits prevented people from accidentally entering strange dates, such as mistyping a year – 2031 instead of 2013, for example. Do you ever find records like that in your database or workbook? It can really mess things up!
Anyway, a simple change to the data validation formula fixed the problem. Instead of 60 days, I changed the formulas to 120 days.
It still prevents those year typos, but gives my client a bigger window for entering data in the file.
More Data Validation for Dates
For more examples of data validation for dates, you can visit the Excel Data Validation – Dates page on my Contextures website.