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

    Calculate Date of Retirement

    ankit.narula

      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
          Pablo Saenz de Tejada

          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
            Mark Fraser

            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

              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

                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

                  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
                    ankit.narula

                    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
                      Mark Fraser

                      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

                      1 of 1 people found this helpful
                      • 8. Re: Calculate Date of Retirement
                        Pablo Saenz de Tejada

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

                        • 9. Re: Calculate Date of Retirement
                          ankit.narula

                          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

                          • 10. Re: Calculate Date of Retirement
                            Mark Fraser

                            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
                              Chris Dickson

                              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
                                ankit.narula

                                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

                                  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

                                    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):

                                    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.

                                    1 of 1 people found this helpful
                                    1 2 Previous Next