Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Thanked 1 Time in 1 Post

    Calculating dates and the passage of time (Excel 2003)

    Hi all....I will try to keep this short, but can't promise that I won't be back with follow-up questions...I am making a small spreadsheet that I will give to employees so that they can calculate their pension entitlement (instead of always coming to me with a series of requests and retirement scenarios). Here is my example & question: there are 2 forms pf pension that people accumulate, the basic and the enhanced. Even though people can work till age 65, they do not accumulate any basic pension after December 31 of the year in which they turn my birthday is Sept 1, 1950; I started work on Oct 15, 1980; I will turn 60 on Sept 1, 2010....although I can continue to work until Aug 31, 2015, I stop accumulating basic pension on December 31, 2010......I need a formula that will calculate the 'time' between Sept 1, 1950 and Dec 31, 2010, and, between Oct 15, 1980 and Dec 31, 2010 (the problem being that the length of these periods change with every employee). I need such a formula, so that I can always show Dec 31, yr?? as the 'end date' for the period in which pension has been acquired.....any suggestions?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Calculating dates and the passage of time (Excel 2003)

    Say that the date of birth is in B2. The formula =DATE(YEAR(B2)+60,12,31) returns December 31 in the year the employee turns 60. This fornula can be filled down.
    The period in days between two dates can be calculated by subtracting the earlier one from the later one. Excel will result the format as a data by default, by clearing the format you'll see the number of days.
    If you want the period as decimal years (e.g. 30.2 year), you can divide the number of days by 365.25.

    See attached sample workbook.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts