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.


You may also like...

2 Responses

  1. AlexJ says:

    An alternative approach might be to keep the limits at +/- 60 days, but change the Data Validation Error Alert from “Stop” to “Warning” (with appropriate text), so that out of range dates could be entered, but the user would do so consciously.

Leave a Reply

Your email address will not be published. Required fields are marked *