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.