# how to find date of retirement

This is a discussion on how to find date of retirement within the Excel Questions forums, part of the Question Forums category; Dear Friends, kindly help me to solve the following problem in excel i need a excel programme to determine the ...

1. ## how to find date of retirement

Dear Friends,

kindly help me to solve the following problem in excel

i need a excel programme to determine the date of retirement of employees as per their date of birth.

if any employees date of is : 01-07-1990, he should be retired from service on 30-06-2048, taking 58 years of age as retirement year.

2) date of birth: 15-07-1990 date of retirement is:31-07-2048

how to calculate these,

my input in a1 is date of birth, b1 is age of retirement, the result should be in c1 adding date of birth + age of retirement.

thank you

2. ## Re: how to find date of retirement

Hi

What are the rules?

i.e I would have expected scenario 2 date to be 30/06/2048

3. ## Re: how to find date of retirement

Following is the formula if Date of Birth is in A1 cell:

=IF(DAY(A1)<15,EOMONTH(DATE(YEAR(A1)+58,IF(MONTH(A1)=1,12,MONTH(A1)-1),1),0),EOMONTH(DATE(IF(MONTH(A1)=1,YEAR(A1)+58,YEAR(A1)+59),IF(MONTH(A1)=1,12,MONTH(A1)+1),1),0))

Regards,

4. ## Re: how to find date of retirement

Thanks for suggestions.

but date only after 15 is taking for change of month or year

i mean if i enter date as 12/14/1950 mm/dd/yy the date of retirement is coming as 11/20/2008 and 12/15/1950 is as 1/31/20010, why this type of errors are occuring.

date format should be dd/mm/yy

date of retirement : add 58 years to date of birth

date of retiremnet of persons who born on 1 of the month he should retired month before to this date of birth i.e. 1/06/1050 he should retired on 31/05/2008

kinly help

5. ## Re: how to find date of retirement

If you want:
Date of Birth is 01-07-1990 then Date of retirement is 30-06-2048.
Date of Birth is 15-07-1990 then Date of retirement is 31-07-2048.
Date of Birth is 02-07-1990 then Date of retirement is 31-07-2048.

If date of birth is in A1 cell:

=IF(DAY(A1)=1,EOMONTH(DATE(YEAR(A1)+58,IF(MONTH(A1)=1,12,MONTH(A1)-1),1),0),EOMONTH(DATE(YEAR(A1)+58,MONTH(A1),1),0))

If date of birth in A1 cell and Age of retirement in b1 cell, please consider "B1" instead of 58 or the formula in C1 cell should be as under:

=IF(DAY(A1)=1,EOMONTH(DATE(YEAR(A1)+B1,IF(MONTH(A1)=1,12,MONTH(A1)-1),1),0),EOMONTH(DATE(YEAR(A1)+B1,MONTH(A1),1),0))

I hope this will solve your problem.

6. ## Re: how to find date of retirement

The formula is not working, some sytax error is there

7. ## Re: how to find date of retirement

Anything wrong with

=EOMONTH(A1,(12*B1)-1)

8. ## Re: how to find date of retirement

what version of Excel are you using ? If you are using Excel 2003 or earlier, you will need the analysis tool pak to be installed to use EOMONTH. This can be found under Tools, Options (I seem to remember). Thanks

Regards

Kaps

9. ## Re: how to find date of retirement

Originally Posted by jasonb75
Anything wrong with

=EOMONTH(A1,(12*B1)-1)
If the requirement is for 1st of the month to go to last day of previous month but any other date to the end of that month then that still wouldn't work, although this would

=EOMONTH(A1-1,12*B1)

If you get a syntax error try changing , to ;

without EOMONTH you could use

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

10. ## Re: how to find date of retirement

=EOMONTH(A1,(12*58)-(DAY(A1)<15))

Any thoughts for improvement?

Page 1 of 3 123 Last

#### Posting Permissions

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