Dates in IF statements

S

ScottEslick

:confused: I have a small deal I can't figure if I can do... I want to
return a value for a date in a specific range of dates, is this
possible?

TIA,
Scott
 
S

ScottEslick

I would like to enter a date in a cell, then if the date falls between a
range say Aug 1,1987 to July 31,1988, then return the value U-18. I have
ranges from U-4 to U-18.

Thanks!!!! Scott
 
T

Troubled User

If your date is in cell A1, the formula would be:

=IF(AND(A1>31990,A1<32355),+U18,0)

If you type a date in Excel and change the format to "," format, it will
give your the date values as above.
 
B

Biff

I have ranges from U-4 to U-18.

Does that mean you also have a date range for each U value?

If so, then you're best approach would be to build a lookup table.

Post back with all the ranges and their corresponding values.

Biff

"ScottEslick" <[email protected]>
wrote in message
news:[email protected]...
 
S

ScottEslick

It goes like this:
08/01/87 thru 07/31/88 = U-18
08/01/88 thru 07/31/89 = U-17
08/01/89 thru 07/31/90 = U-16
08/01/90 thru 07/31/91 = U-15
08/01/91 thru 07/31/92 = U-14
08/01/92 thru 07/31/93 = U-13
08/01/93 thru 07/31/94 = U-12
08/01/94 thru 07/31/95 = U-11
08/01/95 thru 07/31/96 = U-10
08/01/96 thru 07/31/97 = U-9
08/01/97 thru 07/31/98 = U-8
08/01/98 thru 07/31/99 = U-7
08/01/99 thru 07/31/00 = U-6
08/01/00 thru 07/31/01 = U-5
08/01/01 thru 07/31/02 = U-4

Thanks for Everyones Help!!!!
Scott
 
S

ScottEslick

:) THANKS!!!!

That works perfect...this changes every year, can you explain the
formula so I can adjust it?

Thanks Again, Scott
 
D

daddylonglegs

=DATEDIF(A1,B1,"y")

gives the time difference in years between a date in A1 and a date in
B1
so the formula

=DATEDIF(A1,DATE(2006,7,31),"y")

with a date of birth in A1 gives age at 31st July 2006.

For next year change the year to 2007 or put your date in a cell, e.g
B1 and use

="U-"&DATEDIF(A1,B1,"y")
 
Top