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.

datavaldates02

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

datavaldates01

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.

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.

_____________

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

2 comments to Limit Date Range with Excel Data Validation

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>