Recently, I had an issue where I needed to calculate the age of a person at specific dates throughout the year. Using a Google Sheet, I thought I could simply subtract one date from the other and divide by 365, but this ended up not being as accurate as I wanted.
To calculate somebody’s age at a specific point in time you need their birth date and a comparison date to calculate the age of the person by. Start by subtracting the years from each other, and then if the month and day of the comparison date are before their birth month and date, then subtract one.
For example, my birth date is the 12th of October 1977. Today’s date is 28th of August 2021, therefore to calculate my age start by subtracting the years (2021 – 1977 = 44) then, as my birth month and day are after the comparison date I need to subtract 1 from our first step (44) which means I am currently 43.
If we wanted to code this up manually on a spreadsheet as a formula we could represent the above using a formula like so:
| A | B | C | |
|---|---|---|---|
| 1 | Birthdate | Current Date | Age (Years) |
| 2 | 12/10/1977 | 28/08/2021 |
43
=MAX(0, (YEAR(B2)-YEAR(A2))-IF((MONTH(B2)*100)+DAY(B2)<(MONTH(A2)*100)+DAY(A2),1,0))
|
The formula used in the spreadsheet example above, being:
=MAX(0, (YEAR(B2)-YEAR(A2))-IF((MONTH(B2)*100)+DAY(B2)<(MONTH(A2)*100)+DAY(A2),1,0))
The cell references in the formula above refer to the following, as shown in spreadsheet view: A2 is the Birthdate value; B2 is the comparison date (all dates in the DD/MM/YYYY format); C2 is the age in years.
As you can see the formula is quite verbose, and should the comparison date be less than 0 the answer will be 0. You could make this number -1 as the first parameter of the
MAX
formula to denote the person has not been born, but for my use case leaving the result as zero is fine.
Is there a function in Google Sheets to calculate the same? Yes, there is!
Google Sheets Formula
In Google Sheets there is a handy function called
DATEDIF
that helps to calculate the age of someone at any point in time. The function accepts three parameter values, with the first parameter being the person’s birthdate, the second parameter being the date to obtain their age, and the third parameter being the value
"Y"
.
Here is an example ( dates below are in the form DD/MM/YYYY ):
| A | B | C | |
|---|---|---|---|
| 1 | Birth Dates | Date | Age (Years Old) |
| 2 | 29/02/2004 | 28/02/2020 |
=DATEDIF(A2,B2,"Y")
15
|
| 3 | 29/02/2004 | 29/02/2020 |
=DATEDIF(A3,B3,"Y")
16
|
| 4 | 29/02/2004 | 28/02/2021 |
=DATEDIF(A4,B4,"Y")
16
|
| 5 | 29/02/2004 | 01/03/2021 |
=DATEDIF(A5,B5,"Y")
17
|
As you can see from the above example, if somebody has a birthday on the rare date of 29th February (on a leap year, like 2004), then we calculate their age according to years that don’t contain a leap year the correct value is given according to the
DATEDIF
formula
.
In rows 2 and 3 we use a year containing a leap year to calculate the age of the person born on a leap day. Using the formula
DATEDIF(BirthDate, ToDate, "Y")
we correctly see that on the 28th February 2020 the person is 15 years old, and then on the next day they turn 16.
In contrast, that same person born on a leap year, using the same formula, correctly has their age calculated when the calendar flicks from the 28th February to 1st March.
Calculate Age: Summary
To calculate the age of somebody using Google Sheets, use the
DATEDIF
formula and set the third parameter of that formula to
"Y"
which denotes the number of years since the start date.
Find out more about the
DATEDIF
function here
.
