1 2 Previous Next 16 Replies Latest reply on Sep 15, 2015 11:41 PM by ankit.narula

# Calculate Date of Retirement

Hi Experts,

Need some help as stuck on a query.

I want to calculate the age of retirement from the birth date considering the age of retirement is 65.

The display for age of retirement field should be like '5 years 6 months 6 days'

Attaching is the sample data.

Thanks

Ankit

• ###### 1. Re: Calculate Date of Retirement

hi Ankit,

You can create a new calculated field to have the concrete date of retirement of each person using a DATEADD calculation, create also another calculated field with today's date using the function TODAY() and then use the DATEDIFF to calculate the difference between Today and the date of retirement in days.

Have a look at the workbook I attach, and tell me if it's helpful. The only thing left will be to transform the total number of days to the concrete format that you want.Maybe you have to create some additional calculations but using DATEADD and DATEDIFF you will be able to get finally what you are looking for.

Regards,

Pablo

• ###### 2. Re: Calculate Date of Retirement

Hi Ankit

An interesting problem

I guess first you need to work out the retirement date

So we'll add 65 years to DoB >> DATE(DATEADD('year',65,[Birth Date]))

Next week need to work out how long they have to go... I'm using today() but you could use an alternative date

Year is easy > DATEDIFF('year',TODAY(),[Retirement Date])

I'm still working on the rest < the problem is you have to exclude the time before. i.e. when calculating months remaining - I need to first remove the whole years. This is easier as 12 months per year. But days is worse...

Or work out in days and calculate the days back into Year, month, day....

Its interesting - wonder if there is a better way??

Cheers

Mark

• ###### 3. Re: Calculate Date of Retirement

Hi Pablo,

Thanks for the response however I am unable to open the attached workbook.

Which data you are using?.

Please attach it again.

Thanks

• ###### 4. Re: Calculate Date of Retirement

Hi Mark,

You are right.

Year is easy however other calculations are complicated.

Try to take some time out to achieve this.

Thanks

• ###### 5. Re: Calculate Date of Retirement

Hi,

I was using your Test Data but I think the issue was that I create it 9.1. Please try this other one create with 9.0.

Cheers,

Pablo

• ###### 6. Re: Calculate Date of Retirement

Hi,

Thanks for the prompt response.

Attaching the error and also the Tab version that I am using.

Please attach the workbook accordingly.

Thanks

• ###### 7. Re: Calculate Date of Retirement

Hi Ankit

I may have it... Here is a sample... It needs checking! You first need Retirement Date

Then

Year

IF DATEPART('year',[Retirement Date])-DATEPART('year',TODAY()) <0 THEN (DATEPART('year',[Retirement Date])-DATEPART('year',TODAY()))*-1 ELSE DATEPART('year',[Retirement Date])-DATEPART('year',Today()) END

Month

IF DATEPART('month',[Retirement Date])-DATEPART('month',TODAY()) <0 THEN (DATEPART('month',[Retirement Date])-DATEPART('month',TODAY()))*-1 ELSE DATEPART('month',[Retirement Date])-DATEPART('month',TODAY()) END

Day

IF DATEPART('day',[Retirement Date])-DATEPART('day',TODAY()) <0 THEN (DATEPART('day',[Retirement Date])-DATEPART('day',TODAY()))*-1 ELSE DATEPART('day',[Retirement Date])-DATEPART('day',TODAY()) END

Output

STR(SUM([Year]))+' Years '+STR(SUM([Month]))+' Months '+STR(SUM([Days]))+' Days'

It needs checking!

Cheers

Mark

1 of 1 people found this helpful
• ###### 8. Re: Calculate Date of Retirement

Ok, let's see if now it works :-)

• ###### 9. Re: Calculate Date of Retirement

Hi Mark,

I think there is something wrong with the Month and Days

For eg:if you take an empl code i.e E03614 for that employee the period left is 1 yr 11 months and 21 days.

Thanks

• ###### 10. Re: Calculate Date of Retirement

Hi Ankit

Sorry - I don't see that record - is that in my copy?! I'm using my attachment from

I haven't anyone below 3 years... this is the smallest I have Cheers

Mark

• ###### 11. Re: Calculate Date of Retirement

I tried to simplify by taking the days to retirement calculation (as described above) and then simply added this number of days to 1/1/1900, this way it does take into account a leap day every 4 years, but maybe not at the right point so every 4 years the calculation will be off by a day at most.

take a look at the attachment to see what i mean.

1 of 1 people found this helpful
• ###### 12. Re: Calculate Date of Retirement

Hi,

Sorry however I am still unable to open the workbook that you are attaching.

Please attach a screen shot of calculation or write the calculation as reply.

Thanks

• ###### 13. Re: Calculate Date of Retirement

Hi Chris,

Finally an attachment that I can access.

Let me implement the calculations in my workbook and will update about this.

Thanks

• ###### 14. Re: Calculate Date of Retirement

Hi Ankit,

what I did is change ink your dataset the Birth Date to a Date field, and then create this calculated fields:

Date of retirement (gives you the Date when each person will be 65 years old):

today (gives you today's date):

TODAY()

Total Days to Retirement (gives you the number of days left from today to the retirement date for each person):

DATEDIFF('day',[today],[Date of retirement],'monday')

Then you can create additional calculated fields to have finally in the format you want. For example:

Years Whole (years with decimals until retirement):

[Total Days to Retirement]/365

Years Integer (integer number of the nearest integer):

IF INT([Years Whole])

Months Left (this will give you the decimal part of the year left)

[Years Whole]-[Years Integer]

Months Whole (if 1 year have 12 months, then Difference Months multiplied by 12 will give you the whole number of months left):

[Difference Months]*12

Months Integer (the integer number of the previous calc):

INT[Months Whole])

with this you will have now the Integer Years and Months left to retirement, and the only thing left will be the days. You can calculate this with additional calculated field, but you will need to have in mind if the day of the retirement date is higher than todays day number. But I'm sure you can finish solving this with an IF statement.

1 of 1 people found this helpful
1 2 Previous Next