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.
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??
Thanks for the response however I am unable to open the attached workbook.
Which data you are using?.
Please attach it again.
You are right.
Year is easy however other calculations are complicated.
Try to take some time out to achieve this.
I may have it... Here is a sample... It needs checking!
You first need Retirement Date
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
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
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
STR(SUM([Year]))+' Years '+STR(SUM([Month]))+' Months '+STR(SUM([Days]))+' Days'
It needs checking!
Sample Data.twbx 187.2 KB
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.
Please check and advise.
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.
ankit narula.twbx 1.2 MB
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.
Finally an attachment that I can access.
Let me implement the calculations in my workbook and will update about this.
1 of 1 people found this helpful
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):
DATE(DATEADD ('year',65,[Birth Date]))
today (gives you today's date):
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):
Months Integer (the integer number of the previous calc):
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.