What Is The Formula To Calculate Age?

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:

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))
Calculating my age using a basic formula in a spreadsheet

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 ):

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
The formula in column C provides the age of the person according to their birthdate in Column A and the date set in Column B

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 .

Photo of author
Ryan Sheehy
Ryan has been dabbling in code since the late '90s when he cut his teeth exploring VBA in Excel. Having his eyes opened with the potential of automating repetitive tasks, he expanded to Python and then moved over to scripting languages such as HTML, CSS, Javascript and PHP.