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

1. ## 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

2. ## Re: How to find the date of retirement if date of birth is 1-April-1980

Change the following...

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

3. ## Re: How to find the date of retirement if date of birth is 1-April-1980

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

or

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

4. ## Re: How to find the date of retirement if date of birth is 1-April-1980

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...

5. ## Re: How to find the date of retirement if date of birth is 1-April-1980

... and what about national holidays ?

6. ## Re: How to find the date of retirement if date of birth is 1-April-1980

Excellent it worked.

7. ## Re: How to find the date of retirement if date of birth is 1-April-1980

do you have Excel 2003, or higher version, and Analysis Toolpak installed?

if so, with 01-APR-1980 in A1, this formula in B1 will give you 30-MAR-2040 (31st is Saturday):

Please Login or Register  to view this content.

8. ## Re: How to find the date of retirement if date of birth is 1-April-1980

Beautiful solution, Ice...

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1