How to perform five age calculation types using a spreadsheet

Calculation of Age On Last Birthday

Put the appropriate headings into cells A1, B1 and C1 respectively and then put the date of birth into cell A2, the relevant later date into cell B2, and into cell C2, put the formula =DATEDIF(A2,B2,”Y”)

Calculation of Age On Next Birthday

Put the appropriate headings into cells A1, B1 and C1 respectively and then put the date of birth into cell A2, the relevant later date into cell B2, and into cell C2, put the formula =DATEDIF(A2,B2,”Y”)+1

Calculation of Age In Years and Whole Months

Put the appropriate headings into cells A1, B1, C1 and D1 respectively and then put the date of birth into cell A2 and the relevant later date into cell B2. Into cell C2, put the formula =DATEDIF(A2,B2,”Y”) and into cell D2, put the formula =DATEDIF(A2,B2,”YM”)

Calculation of Age In Years and Days

Put the appropriate headings into cells A1, B1, C1 and D1 respectively and then put the date of birth into cell A2 and the relevant later date into cell B2. Into cell C2, put the formula =DATEDIF(A2,B2,”Y”) and into cell D2, put the formula =DATEDIF(A2,B2,”YD”)

Calculation of Age In Years, Months and Days

Put the appropriate headings into cells A1, B1, C1, D1 and E1 respectively and then put the date of birth into cell A2 and the relevant later date into cell B2. Into cell C2, put the formula =DATEDIF(A2,B2,”Y”) and into cell D2, put the formula =DATEDIF(A2,B2,”YM”) and into cell E2, put the formula =DATEDIF(A2,B2,”MD”)

I remember having to do these age calculation types manually, and quite often many at a time(!), in the 1980s. It was mainly the first two types I had to do, sometimes the third type and, occasionally, the 4th type. Thank goodness for spreadsheets!

- Richard Purvey February 2024