Home › Forums › Actuarial Resources & Blogs › Six service calculation types
 This topic has 0 replies, 1 voice, and was last updated 1 month, 3 weeks ago by Richard Purvey.

AuthorPosts

February 27, 2024 at 1:49 pm #24913
How to perform six service calculation types using a spreadsheet
Note;
Calculation of Normal Retirement Date
Put the appropriate headings into cells A1, B1 and C1 and then put the date of birth into cell A2 and the age in years to be added (the normal retirement age, e.g. 60) into cell B2, and into cell C2, put the formula =DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2))
Calculation of Potential Service In Whole Years
Put the appropriate headings into cells A1, B1 and C1 and then put the date of the first day of service into cell A2 and the Normal Retirement Date into cell B2, and into cell C2, put the formula =DATEDIF(A21,B21,”Y”)
Calculation of Potential Service In Years and Whole Months
Put the appropriate headings into cells A1, B1, C1 and D1 and then put the date of the first day of service into cell A2 and the Normal Retirement Date into cell B2. Into cell C2, put the formula =DATEDIF(A21,B21,”Y”) and into cell D2, put the formula =DATEDIF(A21,B21,”YM”)
Calculation of Potential Service In Years and Days
Put the appropriate headings into cells A1, B1, C1 and D1 and then put the date of the first day of service into cell A2 and the Normal Retirement Date into cell B2. Into cell C2, put the formula =DATEDIF(A21,B21,”Y”) and into cell D2, put the formula =DATEDIF(A21,B21,”YD”)
Calculation of Actual Service In Whole Years
Put the appropriate headings into cells A1, B1 and C1 and then put the date of the first day of service into cell A2 and the date of the last day of service into cell B2, and into cell C2, put the formula =DATEDIF(A21,B2,”Y”)
Calculation of Actual Service In Years and Whole Months
Put the appropriate headings into cells A1, B1, C1 and D1 and then put the date of the first day of service into cell A2 and the date of the last day of service into cell B2. Into cell C2, put the formula =DATEDIF(A21,B2,”Y”) and into cell D2, put the formula =DATEDIF(A21,B2,”YM”)
Calculation of Actual Service In Years and Days
Put the appropriate headings into cells A1, B1, C1 and D1 and then put the date of the first day of service into cell A2 and the date of the last day of service into cell B2. Into cell C2, put the formula =DATEDIF(A21,B2,”Y”) and into cell D2, put the formula =DATEDIF(A21,B2,”YD”)
I remember having to do these service calculation types manually, and quite often many at a time(!), in the 1980s. Thank goodness for spreadsheets!
 Richard Purvey February 2024

AuthorPosts
 You must be logged in to reply to this topic.