Six service calculation types

[Richard Purvey]

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(A2-1,B2-1,”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(A2-1,B2-1,”Y”) and into cell D2, put the formula =DATEDIF(A2-1,B2-1,”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(A2-1,B2-1,”Y”) and into cell D2, put the formula =DATEDIF(A2-1,B2-1,”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(A2-1,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(A2-1,B2,”Y”) and into cell D2, put the formula =DATEDIF(A2-1,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(A2-1,B2,”Y”) and into cell D2, put the formula =DATEDIF(A2-1,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!

1. Richard Purvey February 2024

[Author]

Posts