Excel 2007 date calculation.

[aparunkumar]

I have to calculate a date of retirement of teachers. The date of retirement is the last day of attaining the age of 56. But teachers whose date of birth falls on 2nd july on wards will retire only on the last day of the financial year in which their 56 th age falls due.Ihave applied the following formulai

Cell C1= date of birth of employee
to work out 56th age completion in cell C2... =DATE(YEAR(C1)+56,MONTH(C1),DAY(C1)-1)
to work out last day of retirement in cell C3... =DATE(YEAR(C2),MONTH(C2)+1,0)

What is next to find out the last day of financial year for whose DOB falls on 2 nd july onwards

[daddylonglegs]

When does the financial year end? Can you give some sample DOBS with required retirement date?

[aparunkumar]

Financial year ends on 31st march every year.see my examples


Mr A having dob 16/04/1969 would attain 56th age on 15/04/2025 and will retire on the last day ie 30/04/2025
Mr B having dob 01/07/1969 would attain 56th age on 30/06/2025 and will retire on the same day ie 30/06/2025
Mr c having dob 02/07/1969 would attain 56th age on 01/07/2025 and normal retirement be 31/07/2025, but being a teacher, he would retire only
on 31/03/2026, the last day of the respective financial year. pl help