
1. Re: Calculate Date of Retirement
Pablo Saenz de Tejada Sep 15, 2015 4:44 AM (in response to ankit.narula)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

Sample Data.twbx 228.7 KB


2. Re: Calculate Date of Retirement
Mark Fraser Sep 15, 2015 4:59 AM (in response to ankit.narula)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
ankit.narula Sep 15, 2015 5:14 AM (in response to Pablo Saenz de Tejada)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
ankit.narula Sep 15, 2015 5:22 AM (in response to Mark Fraser)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
Pablo Saenz de Tejada Sep 15, 2015 5:23 AM (in response to ankit.narula)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

Test Data 9.0.twbx 196.5 KB


6. Re: Calculate Date of Retirement
ankit.narula Sep 15, 2015 5:31 AM (in response to Pablo Saenz de Tejada)Hi,
Thanks for the prompt response.
Attaching the error and also the Tab version that I am using.
Please attach the workbook accordingly.
Thanks

Tab Version.png 103.7 KB

Error.png 95.7 KB


7. Re: Calculate Date of Retirement
Mark Fraser Sep 15, 2015 5:54 AM (in response to ankit.narula)Hi Ankit
I may have it... Here is a sample... It needs checking!
You first need Retirement Date
DATE(DATEADD('year',65,[Birth 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

Sample Data.twbx 187.2 KB


8. Re: Calculate Date of Retirement
Pablo Saenz de Tejada Sep 15, 2015 5:40 AM (in response to ankit.narula)Ok, let's see if now it works :)

Sample Data.twbx 228.7 KB


9. Re: Calculate Date of Retirement
ankit.narula Sep 15, 2015 6:22 AM (in response to Mark Fraser)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.
Please check and advise.
Thanks


11. Re: Calculate Date of Retirement
Chris Dickson Sep 15, 2015 7:32 AM (in response to ankit.narula)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


12. Re: Calculate Date of Retirement
ankit.narula Sep 15, 2015 11:24 AM (in response to Pablo Saenz de Tejada)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
ankit.narula Sep 15, 2015 11:27 AM (in response to Chris Dickson)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
Pablo Saenz de Tejada Sep 15, 2015 12:13 PM (in response to ankit.narula)1 of 1 people found this helpfulHi 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):
DATE(DATEADD ('year',65,[Birth Date]))
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.