Table of Contents
This tip is about the how To Calculate Age in Google Sheets. So read this free guide, How To Calculate Age in Google Sheets step by step. If you have query related to same article you may contact us.
How To Calculate Age in Google Sheets – Guide
It may be necessary to calculate the age of a group of people based on their birth dates in various scenarios. You can discover a variety of strategies for doing this if you search the Internet. It’s not as simple as subtracting the birth year from the current year. There are many things to think about. Let’s imagine it’s January 29, 2020 and you need to calculate the age of a person born on November 8, 2007. You can get 13 years by removing 2007 from 2020.
However, one must consider the fact that the person was born in November, so he is not yet thirteen. In this Google Sheets lesson, I’ll teach you how to use formulas to quickly determine your age in Google Sheets using your birth date.
How to Knife
Calculate age in Google spreadsheets – number of years
Suppose you have the dataset as shown below, where you have the date of birth in cell B1 and the current date in cell B2. Note that I used the TODAY function to get the date of birth in cell B2. There are now two formulas you can use to calculate age in Google Sheets in years (ie get the total number of years that have elapsed between the two given dates).
The first formula you can use to do this is the DATEDIF formula (called the date difference formula), and the second is using the YEARFRAC function.
Using the DATEDIF formula
let me show you first how to use the DATEDIF function.
The formula below will calculate the age in the total number of years between the two given dates:
= DATEDIF (B1, B2, “Y”)
The formula above has three arguments:
Note that I used the current date as a cell reference (where I already have the current date in cell B2). But you can also use the formula below to do this (where the current date is automatically calculated using the TODAY function within the formula itself):
= DATEDIF (B1, TODAY (), “S”)
In addition, I’ll provide all the other units you can use in the third argument of the DATEDIF function (we’ll use those units in the next section, where we need to calculate age in years, months, and dates in Google Sheets).
Below are all the values you can use in the unit argument in the DATEDIF function;
This will become much clearer when we look at more examples in the next section.
Using the YEARFRAC formula
Another way to calculate age in years in Google Sheets is to use the YEARFRAC function.
Below is the formula that will give you the age in years when you have both the birth date and the current date:
= INT (YEAR (B1, TODAY ()))
The YEARFRAC function will provide the number of years (including fractional years) between two given dates. Since we only want to get the age in years, I used the INT function, which just gives us the integer part of the age.
Calculate age in Google spreadsheets – number of years, months and days
If you just want to calculate age in years, you can use the formulas discussed above. But to get the age in years, months, and days, slightly different formulas would be needed.
Again, you can use the DATEDIF function to calculate the age, which has been divided into years, months and days.
Using the DATEDIF formula
Suppose you have the same dataset and you want to calculate age in years, months, and days.
The formula below will provide the age in years (that is, the total number of years that have passed between the two given dates):
= DATEDIF (B1, B2, “Y”)
Final note
I hope you like the guide How To Calculate Age in Google Sheets. In case if you have any query regards this article you may ask us. Also, please share your love by sharing this article with your friends.