Results 1 to 2 of 2
2007-05-28, 06:16 #1
- 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 60....eg: 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?
2007-05-28, 07:02 #2
- 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.