Calculating someone’s age is a common task that often comes up when working with date data in Google Sheets.

Whether you’re maintaining employee records, analyzing customer demographics, or tracking any other date-related information, being able to calculate age in Google Sheets quickly and accurately is an invaluable skill.

Thankfully, Google Sheets provides a couple of easy-to-use formulas that make this process a breeze, even for beginners.

In this article, we’ll explore how to calculate age in Google Sheets using the DATEDIF and YEARFRAC functions, complete with examples and step-by-step instructions.

## Powerful Formulas to Calculate Age in Google Sheets

When working with date data in Google Sheets, you’ll often encounter scenarios where you need to calculate the age of individuals or groups based on their dates of birth. However, calculating age isn’t as straightforward as simply subtracting the birth year from the current year. There are several factors to consider, such as months and days, to ensure accurate age calculations.

For instance, let’s say you need to calculate age in Google Sheets as of April 6th, 2024, for someone born on August 15th, 2010. If you merely subtract 2010 from 2024, you’ll get 14 years. But this calculation doesn’t account for the fact that the person was born in August, meaning they haven’t yet completed their 14th year.

To address these nuances, Google Sheets offers powerful formulas that can calculate age with precision, taking into account years, months, and even days.

In this section, we’ll explore these formulas in detail, starting with counting the number of years, and then moving on to finding the age in terms of days, months, and years combined.

## Copy Sample Sheet

If you want to learn precisely how to calculate age in Google Sheets, feel free to copy our sample data and follow along with today’s tutorial.

## Calculating Age in Years Using Google Sheets Formulas

Having understood the basics of what it entails to calculate age in Google Sheets, we want to take things up a notch by exploring how to calculate age in years using Google Sheets formula.

To illustrate the process, we are going to be using the following sample data where we have the date of birth in cell B2 and the reference date (such as today’s date or any other date you want to calculate the age from) in cell B3.

In this scenario, you can leverage two powerful formulas in Google Sheets to calculate age in years: DATEDIF and YEARFRAC.

The DATEDIF function calculates the difference between two dates based on the specified date part (years, months, days, etc.). Meanwhile, the YEARFRAC function returns the fraction of the year represented by the number of whole days between two dates.

In the upcoming sections, we’ll dive into the specifics of using these formulas to calculate age in Google Sheets, ensuring you can accurately determine the number of years that have elapsed between any two given dates.

## Using the DATEDIF Formula to Calculate Age In Years

The DATEDIF function stands out as an exceptional resource, effortlessly facilitating age calculation in years.

But rather than just take our word for it, allow us to demonstrate how you can leverage this tool to accurately determine age in years, using our sample data as a guide.

**Step 1: Select an Empty Cell**

Begin by selecting an empty cell within your spreadsheet to display the calculated age in years. For this example, we’ll use cell C6 as our designated location.

**Step 2: Enter the DATEDIF Formula**

Now that you’ve chosen an area in your spreadsheet for the age calculation result, it’s time to input the DATEDIF formula.

To accomplish this, go to the formula bar and enter the following formula:

**=DATEDIF(B2,B3,”Y”)**

The formula above accepts three parameters:

**Start Date:**For our case, this represents the individual’s date of birth.**End Date:**In this scenario, it refers to today’s date.**Unit:**This parameter instructs the DATEDIF function on the specific calculation to perform. In our illustration, we’ve selected “Y” to indicate that the function should compute the total years elapsed between the two dates provided.

**Step 3: Press Enter**

Once you’ve input the formula outlined in Step 2, press the Enter key. Google Sheets will immediately calculate and display the age in years within the chosen cell.

Here is what it looks like:

From the screenshot above, you can see that we have been able to use the DATEDIF function to find the correct age in years almost seamlessly.

## Understanding DATEDIF Function Units: Calculating Age in Years, Months, and Days

From the example we just wrapped up, you’ll notice that we used “Y” for the dated function unit. But guess what? That isn’t the only unit you can use when trying to calculate age in Google Sheets using the DATEDIF function.

In this section, we will explore the various units you can specify for the third parameter in the DATEDIF function.

These units are critical when you don’t only want to calculate the years but also months and days too.

Here are some critical unit options for the DATEDIF function and their respective uses:

- “Y” – Calculates the total elapsed years between two specified dates.
- “M” – Determines the total elapsed months between two specified dates.
- “D” – Computes the total number of days between two specified dates.
- “MD” – Gives the difference in days between two dates, excluding entire months and years that have elapsed.
- “YM” – Offers the difference in months between two dates, disregarding completed years.
- “YD” – Provides the difference in days between two dates, not accounting for full years that have passed.

We understand that this can be a bit confusing right now. But not to worry. You’ll get the hang of it when we begin to use some of these units in subsequent examples.

## Using the YEARFRAC Formula to Calculate Age in Google Sheets

In the previous example, we explored the DATEDIF function and how it can be used to calculate age in years by specifying the “Y” unit. However, Google Sheets offers another useful formula for this purpose: YEARFRAC.

The YEARFRAC function returns the fraction of the year represented by the number of whole days between two dates.

This can be particularly helpful when calculating age with a higher degree of precision, accounting for the exact number of days between the date of birth and the reference date.

In this section, we will show you how to use the YEARFRAC formula to calculate age in years in Google Sheets.

We will use the same sample data we used for the first example to demonstrate the step-by-step process.

**Step 1: Choose a Blank Cell**

Like in the first example, we need to choose an area in our spreadsheet where we want the result to be. For this particular scenario, let’s go with cell B6.

**Step 2: Input the YEARFRAC formula**

After choosing the cell where you want your result replicated, navigate to the formula bar and type the following formula:

**=INT(YEARFRAC(B2,TODAY()))**

**Step 3: Press Enter**

Having executed step 2 as we showed you, go ahead and press the Enter button on your keyboard; you should get the generated result in the selected cell.

Here is what ours looks like:

The YEARFRAC function is designed to calculate the duration between two specific dates in terms of years, including fractions of years. To tailor this output to only show the age in whole years, we included the INT function, ensuring that only the integer portion of the age is returned.

**Bonus Tips: **To enhance accuracy when dealing with dates in Google Sheets, employing the DATE function for entering specific dates into cells is highly recommended. This method reduces the likelihood of encountering errors, such as inputting a date format that Google Sheets fails to recognize as a valid date.

## Calculating Age in Years, Months, and Days

In the previous examples, we covered how to calculate age in years using the DATEDIF and YEARFRAC functions in Google Sheets.

While these formulas provide the age in terms of complete years, there may be situations where you need a more detailed breakdown that includes months and days as well.

To get the age in years, months, and days, we can leverage the versatility of the DATEDIF function by using different units for each component of the age calculation.

Let’s use the DATEDIF function in Google Sheets to calculate age in years, months, and days.

We will also use the same sample data as the previous examples to demonstrate the entire process.

**Step 1: Choose an Empty Cell**

Let’s begin by choosing an empty cell where we want the result for the total number of months that have passed. For our example, we will go ahead and select cell C7.

**Step 2: Input the DATEDIF function**

Now, let’s input our DATEDIF formula in the selected cell. What you want to do is head to the formula bar and type in the following formula:

**=DATEDIF(B1,B2,”YM”)**

**Step 3: Hit Enter**

To see the result, press the Enter button on your keyboard. Google Sheets will generate the result almost instantly in the selected cell.

Here is what ours looks like:

From the screenshot above, you can see that we have been able to get the result for the total number of months passed.

What this means is that 7 months have passed after 13 years completed.

Now, let’s show you how to find the total number of days passed. We will include this as step 4.

**Step 4: Calculate the Number of Days Passed**

To calculate the number of days passed, choose an empty cell where you want the result to be. In our sample sheet, we already have a space for that, which is cell C8.

Having selected where you want the result for days passed generated, navigate to the formula bar and type in the following formula:

=DATEDIF(B2,B3,”MD”)

With the formula entered correctly, go ahead and press the Enter button; you should see the result instantly generated in the selected cell.

For our sample data, we got 22.

In situations where you don’t want to execute these steps one by one and you just want to calculate age in years, months, and days in one cell, you can use the following formula:

=DATEDIF(B2,B3,”Y”)&” Years “&DATEDIF(B2,B3,”YM”)&” Months “&DATEDIF(B2,B3,”MD”)&” Days”

So, let’s input this with our sample data to show you the result.

First, choose a new cell in your spreadsheet and name it accordingly. For our example, we will select cell A10 and name it: Age in Years, Months, and Days.

Having completed that, let’s choose where we want the result to be. For this scenario, we will go with cell B10.

Now that you know exactly where you want the result for age in years, months and days to be, head to the formula bar and type in the following formula.

**=DATEDIF(B2,B3,”Y”)&” Years “&DATEDIF(B2,B3,”YM”)&” Months “&DATEDIF(B2,B3,”MD”)&” Days”**

To generate the result, simply press the Enter button. You should get the result for age in years, months, and days in the selected cell.

Here is what ours looks like:

From the screenshot above, you can see that we have successfully calculated the age in years, months, and days in one cell using a simple formula. This saves us the hassle of repeating steps, one after the other.

## Final Thoughts

Calculating age in Google Sheets is a common task that arises in various scenarios, whether you’re managing employee records, analyzing customer data, or working with any date-related information. By mastering the techniques outlined in this article, you’ll be well-equipped to calculate age in Google Sheets accurately and efficiently.

The DATEDIF and YEARFRAC functions stand out as powerful tools for calculating age in years, with DATEDIF offering additional flexibility to calculate age in months and days as well. By leveraging these formulas, you can ensure that your age calculations are precise, accounting for the intricate details of date components and leap years.

While the process of calculating age in Google Sheets may seem daunting at first, the step-by-step examples and explanations provided in this guide should make it easy for you to implement these formulas in your own spreadsheets.

Whether you need to calculate the age of a single individual or an entire dataset, these methods will streamline your workflow and enhance the accuracy of your date-related calculations.

Remember, the ability to calculate age in Google Sheets is a valuable skill that can be applied across various domains, from human resources and customer relationship management to data analysis and beyond.

By incorporating these techniques into your Google Sheets arsenal, you’ll not only save time but also gain a deeper understanding of how to effectively work with date data in your spreadsheets.