How to find the date of retirement if date of birth is 1-April-1980

Hi every body,

I want to find date of retirement by adding 60 years with date of birth and subtracting 1 day.
I used the following formula which I learnt from a forum.

Cell A1=date of birth.

Cell B1= Date of retirement

B1=DATE(YEAR(A1)+60,MONTH(A1),DAY(A1-1))

But the problem with this formula is that if the date of birth is 1-April-1980 then it gives 1-May-2040 which is wrong. It should be 31-March-2040.

Secondly if the retirement date is Sunday or Saturday it should give date for Friday as retirement date.

I hope I will get the solution from experts on forum.

Regards
Mmashah

Change the following...

B1=DATE(YEAR(A1)+60,MONTH(A1),DAY(A1)-1)

=DATE(YEAR(A1)+60,MONTH(A1),DAY(A1))-1

or

=15+(365*60)+A1-1

For your second question... it's messy, but it works... I'm sure there are more elegant ways to solve it...

=IF(WEEKDAY(DATE(YEAR(P3)+Q3,MONTH(P3),DAY(P3)-1))=1,DATE(YEAR(P3)+Q3,MONTH(P3),DAY(P3)-3),IF(WEEKDAY(DATE(YEAR(P3)+Q3,MONTH(P3),DAY(P3)-1))=7,DATE(YEAR(P3)+Q3,MONTH(P3),DAY(P3)-2),DATE(YEAR(P3)+Q3,MONTH(P3),DAY(P3)-1)))

Note: WEEKDAY gives you a value of 1 for Sunday, 2 for Monday, ... , 7 for Saturday...

... and what about national holidays ?

Excellent it worked.

Beautiful solution, Ice...