data validation

[HGood]

I have two cells in my spreadsheet where field personnel will enter data.
I'm using Data Validation, Allow Date, to control what they enter.

The cells are:
A1 Year Begin
A2 Proposed Year End

They will put the year they began in the "Year Begin", but I want to limit
what they can enter in "Proposed Year End" to no more than 12 years, that's
the max we allow them to take.

Under Data, I chose "between".
For "Start date" I entered "1/1/2004", just so they don't erroneously type
in previous years.
For "End date", what do I need to enter as a formula?

If I enter the formula =A2+12, it limits it to 12 year later, but I'd like
to limit it to any year between 1 and 12 added to Start date. How can I do
this? What formula in "End date" will accomplish this?

Thanks,

Harold

 

[Biff]

Hi Harold!

Are the users entering just the 4 digit year or are they
entering a full date?

2004 or 7/10/2004 ?

If they are entering just the 4 digit year, 2004:

Select cell A2
In Data Validation, select CUSTOM.
Enter this formula: =AND(A2>=A1,A2<=A1+12)

If they are entering a full date, 11/22/2003
Formula is:
=AND(YEAR(A2)>=YEAR(A1),YEAR(A2)<=YEAR(A1)+12)

Biff

 

[HGood]

Hi Biff,

Fantastic, it worked!!!

Thanks very much,

Harold
====================