Home Forums Actuarial Resources & Blogs Six service calculation types

Viewing 1 post (of 1 total)
• Author
Posts
• #24913
Richard Purvey
Participant

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
Viewing 1 post (of 1 total)
• You must be logged in to reply to this topic.