how to find date of retirement

cnu nagula

Board Regular
Joined
Aug 28, 2008
Messages
83
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi

What are the rules?

i.e I would have expected scenario 2 date to be 30/06/2048
 
Upvote 0
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,
 
Upvote 0
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
 
Upvote 0
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.

Please apply the following formula:
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.
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
Barry, I think we both misread the requirement, reading through the thread again carefully I'm suggesting

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

Any thoughts for improvement?
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top