Function to calculate dates

[Zakynthos]

What would be the form of the function that would allow me to do the following:

Lookup a given date, (say, 8/4/1998) and calculate whether it was

greater than or equal to 5 years before 30/9/2000
greater than or equal to 8 years before 30/9/2000
greater than or equal to 10 years before 30/9/2000
greater than or equal to 18 years before 30/9/2000

Then apply to the result of the above to:

formula 1 (say, *2/99) if result is >=5 yrs
formula 2 (say, *3/101) if result is >=8 yrs
formula 3 (say, *4/202) if result is >=10 yrs
formula 4 (say, *5/303) if result is >=18 yrs

Many thanks

 

[Jim Thomlinson]

Check out DateDif

http://www.cpearson.com/excel/datedif.aspx

 

[Bernd P]

Hello,

=DATEDIF(A1,"30/9/2000","y")*LOOKUP(DATEDIF(A1,"30/9/2000","y"),{-1E
+307;5;8;10;18},{#DIV/0!;2/99;3/101;4/202;5/303})

Regards,
Bernd