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:

=TODAY()-60

and up to 60 days after the current date:

=TODAY()+60

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.

=TODAY()-120

and

=TODAY()+120

It still prevents those year typos, but gives my client a bigger window for entering data in the file.

Video: Prevent Invalid Dates

In this video, three different data validation methods are used to validate dates. From the Allow drop down in the data validation settings, the following options will be used:

• Specify a starting date and an ending date. (Date option)
• Show a drop down list of valid dates (List option)
• Create a rule in a custom formula (Custom option)

Video Timeline:

• 0:00 Drop Down List of Dates
• 3:04 Set a Date Range
• 5:43 Check the Year
• 6:55 Get the Sample 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.

_____________