Excel: value from between two dates

[Manic]

I am trying to write a function that cheks a DOB against peramiters to give me 3 values. This is the first time I have tried to write something like this and I am stumped.

if dob (column A) is >="01/09/1987" +(A2<=31/8/1988) (column B)K2=1999 (column C)K3=2002 (column D)K4=2004

DOB K2 K3 K4
10/09/1987 1999 2002 2004

I have 10,000 records in the current spreadsheet and we are expection about 20 more sheets in the coming months.

If I can't set it up I will have to rely on the formula that I have started but as you can guess it would be huge.

This is working out 1 year and 1 value
=IF(A2>="01/09/1987"+(A2<=31/8/1988),1999,"date not in range")

Any help would be greatly appriciated

___________________
Manic

http://www.rockthenorth.com

 

[danomaniac]

I think this will help..

=IF(A2>=$E$1+(A2<=$F$1),1999,IF(A2>=$G$1+(A2<=$H$1),2002,IF(A2>=$I$1+(A2<=$J$1),2004,"date not in range")))

Put your low and hi criteria dates in cells in say, E1 & F1 for 1999, G1 & H1 for 2002, and I1 and J1 for 2004 - (or wherever you want, but change the references in the formula.

Hope that helps, although I wasn't really sure what you were after.

&quot;It's more like it is now, than it ever has been.&quot;

 

[Manic]

That formula is ace and will definatly help if I cant get the function to work. I have started to break it down into three (one for each value I need). At the moment it is realy no better than psudo-code.


Sub KS2_Value()
If (cell >= "1/9/1987" <= "31/8/1988") Then
K2 = 1999
End If
If (cell >= "1/9/1988" <= "31/8/1989") Then
K2 = 2000
End If
If (cell >= "1/9/1989" <= "31/8/1990") Then
K2 = 2001
End If
If (cell >= "1/9/1990" <= "31/8/1991") Then
K2 = 2002
End If
If (cell >= "1/9/1991" <= "31/8/1992") Then
K2 = 2003
End If
If (cell >= "1/9/1992" <= "31/8/1993") Then
K2 = 2004
End If

End Sub

___________________
Manic

http://www.rockthenorth.com

 

[SkipVought]

You have defined your literals as TEXT.

Replace the ambiguous, "dd/mm/yyyy" with unambiguous, #yyyy/mm/dd#. This will make a date conversion.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]