MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Date validation between specified dates

Posted by Garry on June 18, 2000 11:54 PM

I am trying to restrict dates entered in Cells A1:A20 to the range between the values specified in Cells B1 and B2.

Cell B1 picks up todays date, B2 is calculated to be 91 days earlier than B1.

I am using the Data/Validation, Date, Between, B1, B2 method.

This doesn't work. Under test, changing the Between option to a Greater than option, does work and allows only dates greater than that in cell B1.

What's wrong !!

Posted by mads on June 19, 0100 12:59 AM

Are you entering the validation as between B1 & B2
(which won't work) or between B2 & B1 ?


Posted by JAF on June 19, 0100 2:32 AM


You need to make your cell references absolute (i.e. $B$1 and $B$2)

I've tried this and it solved the problem.

Posted by JAF on June 19, 0100 2:39 AM


Just had another idea.

Instead of specifying cell references for which your date must fall between, you could do this directly in the Data Validation settings.

Select the range you want to restrict, and select Data/Validation. Choose Date and Between and set the start and end dates as folows:

Start: =today()-91
End: =today()


Posted by mads on June 19, 0100 2:45 AM

It makes no difference whether the refs are absolute or relative. Either way it will not work if the start date is later than the end date.