how to find date of retirement

[cnu nagula]

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

 

[barry houdini]

It's not clear to me either way, Jason.

If days 1 to 14 go to end of previous month and 15 and above goes to end of the current month (in the relevant year of course) then I think you can do that by just changing the -1s in my suggested formulas to -14s, i.e. assuming "retirement age" in B1 these formulas will give the same result

=EOMONTH(A1-14,12*B1)

or

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

 

[jasonb75]

You're right with changing -1 to -14 barry, think I'm having a brain malfunction day missing something so simple.

 

[cnu nagula]

The formula :=EOMONTH(A1,(12*58)-(DAY(A1)<15)) is working, when we are giving date after 15 only the month and years chaning. from date 1 t0 14 it is not working, after 15th date only it is working.

=DATE(YEAR(A1-1)+B1,MONTH(A1-1)+1,0), this is working.

i am using office 2007

Thank you both.

Thank you once again

 

[cnu nagula]

Dear Friends,

when we take 1st as the date of birth, retirement is to be before the month, from 2nd on wards, the month is same

Ex: Date of birth : 01-10-2000 his date of retirement is 30-09-2058

02-10-2000 31-10-2058

taking 58 years as age

Thank you

 

[jasonb75]

Try using this basic principle

Excel Workbook
	A	B	C	D
1	DOB	Ret Age	Cut Date	Ret Date
2	01/10/2000	58	1	30/09/2058
3	02/10/2000	58	1	31/10/2058
4	01/07/1990	58	14	30/06/2048
5	15/07/1990	58	14	31/07/2048
Sheet

Cut date is the last date that should be taken to the previous month.

 

[cnu nagula]

ok,

the cut date is only 1, rest all the date in the month will be the same month only.

thanks for your suggestions.

 

[jasonb75]

In your original request the cut date was on or before 14th for previous month, I've used this to show how you can modify the formula to match any date required without having to ask again and wait for someone to reply.

 

[abmati]

cnu nagula,

I have checked and confirm that my formula is working fine using Excel 7.
Examples are given below (A and C columns are in date format dd-mmm-yy).
A B C
1 01-Jul-90 58 30-Jun-48
2 15-Jul-90 58 31-Jul-48
3 02-Jul-90 58 31-Jul-48

Where C1=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))

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

C3=IF(DAY(A3)=1,EOMONTH(DATE(YEAR(A3)+B3,IF(MONTH(A3)=1,12,MONTH(A3)-1),1),0),EOMONTH(DATE(YEAR(A3)+B3,MONTH(A3),1),0))

 

[BEST88771]

Dear Friends In the same context how would I calculate retirement date on their 65 birthday example = date of birth 12/01/1958 - so retirement date will be 12/01/2023

 

[barry houdini]

For exactly 65th birthday you can use a simple EDATE formula like

=EDATE(A1,65*12)

where A1 contain DOB