Add years to a date dependant on age

Hello.

I'm trying to create a formula that looks at a date of birth in column B, and a date in column C.

What I need is in column D: if the persons age is less than 45, add 5 years to the date in Column C. If 45 and over add 3 years. However if the person is 44, add 4 years (because once they are 45, the 3 year rule will be enforced)

The formula I've got is incredibly lengthy and a bit tricky to understand. But mainly the problem I may have is these rules could change soon to: if less than age of 45, add 8 years. Then it will mean if they are 44, add 4 years; 43, add 5 years; 42, add 6 years; and if 41, add 7 years.

So I'm just wondering if anyone can come up with an easier formula (macros and UDF's not allowed).

It might be better to have a little table that shows those ages and the years to add, then you can use a VLOOKUP formula to determine what to add on to the age.

Hope this helps.

Pete

It never occured to me to do that. Thanks, this makes it much easier.

Thanks for the Rep. Perhaps you can mark the thread as SOLVED if you think it is - please select Thread Tools from the menu above your first post and choose Mark Thread as SOLVED.

Pete