Adding value (5,2,1 year) to a cell value based on another cell value (DOB)

Hello All,
I have multiple excell worksheets with list of columns. Column C is Birth Year,column D is date of Approved, I want in Column C to calculate based on this criteria:

If Column C is <35 years add 5 years to Column D
If Column C is 35-45 years add 2 years to Column D
If Column C is >45 years add 1 years to Column D.
Please See my attached file!!
I used IF funciton but the outcome is not what i want
My formula:
=IF(TODAY()-C2>45,DATE(YEAR(K5)+1,MONTH(D2)+0,DAY(D2)+0),IF(TODAY()-C2<35,DATE(YEAR(D2)+5,MONTH(D2)+0,DAY(D2)+0))).
it only take the first criteria which is TODAY()-C2>45,DATE(YEAR(D2)+1, it won't consider the entire formula? I appreciate greatly for the help.

Very Respectifull

AttachmentSize
Training Roster.xlsx12.69 KB
Nick's picture

You need to use the year of

You need to use the year of today