Skip to Content

How to Calculate Age in Google Sheets

If you want to calculate how old someone is using their date of birth, there are a few different ways you can do this in Google Sheets.

You can set up a basic formula that will calculate the difference between two dates to do this.

In this tutorial, I will show you how to calculate age in Google Sheets.

DATEDIF Function

To find out the age of someone using their birthday, we will be using the DATEDIF function.

This function calculates years, months, or days between two dates.

The syntax of this function is:

=DATEDIF(start_date, end_date, unit)

  • start_date – The start date used in calculating the date difference. Can be a cell reference, a function returning a date, or a number
  • end_date – The end date used in calculating the date difference. Can be a cell reference, a function returning a date, or a number
  • unit – This parameter is an abbreviation for the unit of time. These values are accepted in this function: “Y”,”M”,”D” ,”MD”,”YM”,”YD”
    • “Y”: this means the number of whole years between start_date and end_date
    • “M”: this means the number of whole months between start_date and end_date
    • “D”: this means the number of days between start_date and end_date
    • “MD”: this means the number of days between start_date and end_date after subtracting whole months
    • “YM”: this means the number of whole months between start_date and end_date after subtracting whole years
    • “YD”: this means the number of days between start_date and end_date, assuming start_date and end_date are no more than one year apart

Calculate Age in Google Sheets – Years Only

Now that we have taken a look at the basics of the DATEDIF function, let’s look at how to use it to calculate age in years only.

The formula I will be using is:

=DATEDIF(start_date, end_date, “Y”)

As we covered earlier, using “Y” as my unit of time will calculate the number of whole years between my start date and end date, so this will give me how old someone is in years.

Here are the steps to do this:

1. Select the cell where you want to calculate the age

age in years-1

2. Press the equals sign (=) on your keyboard and then type  “DATEDIF” and press Tab to enter the function

age in years-2

3. Next we need to enter our three parameters, select the cell that contains your first date, add a comma (,), then select your second date, add another comma (,), and then place a Y inside quotation marks (“Y”). Add your closing parenthesis and press Enter.

age in years-3

4. Your age should now calculate for the data you have entered

age in years-4

5. If you have additional dates in your spreadsheet you can copy and paste the formula down to repeat the formula on those dates.

age in years-5

Calculate Age – Years, Months, and Days

Calculating just the years is a pretty simple formula, but when you want to calculate years, months, and days the formula gets a little more complex.

First let’s look at months and days individually, and then we will look at combining them all.

To calculate months we want to use this formula:

=DATEDIF(start_date, end_date, “YM”)

Using YM as our unit value will give us the whole months between our start date and end date after subtracting years.

Here is an example of how this is used with our previous data:

age in months

The next thing we need to look at is calculating the days. For that we will be using this formula:

=DATEDIF(start_date, end_date, “MD”)

Using MD as our unit will give us the number of days after subtracting whole months. This is what we want.

Here is an example of this is used with our previous data:

age in days

Now instead of doing three different formulas, we want to combine these all together into one formula to calculate the years, months, and days together in one cell.

Here is the syntax of the formula we will be using:

=DATEDIF(start_date, end_date,”Y”)&” Years “&DATEDIF(start_date, end_date,”YM”)&” Months “&DATEDIF(start_date, end_date,”MD”)&” Days”

Basically, all you are doing with this is combining the three previous formulas we have covered into one formula by using the & symbol. We are also adding the text in to label our numbers by writing the text values of Years, Months, and Days, in quotation marks with the & symbol.

Here is how this formula is used with our example data:

age in years, months, and days

Closing Thoughts

As long as you understand how to use the DATEDIF function, calculating age or any other date difference is relatively easy in Google Sheets.

But when you want to calculate multiple units of time you will have to nest together multiple functions into one.

Now that you have learned this, there are probably a lot of other useful ways you can use this in your spreadsheet with different dates.

More Google Sheets Tutorials:
How to Insert Current Date
How to Calculate Percentage
How to Convert Celsius to Fahrenheit
How to Use the ISDATE Function