ExcelInsider Team
In Google Sheets, we often need to calculate age when date of birth data is included in the dataset to determine eligibility for certain benefits or other criteria, based on age. For this purpose, we usually use DATEDIF or YEARFRAC functions.
In this article, you will learn how to calculate age from date of birth in Google Sheets using both DATEDIF and YEARFRAC functions.
Steps to Calculate Age from the Date of Birth:
➤ Select a blank cell from the header row and use a name, such as age.
➤ Write down a formula:
=DATEDIF(B2, TODAY(), “Y”)
➤ Press Enter, and it will return the age in years.
➤ Replace “Y” with “YM” for months and “MD” for days.
➤ To break down the age in years and months, combine as:
=DATEDIF(B2, TODAY(), “Y”) & ” years, ” & DATEDIF(B2, TODAY(), “YM”) & ” months”.
Note: Cell B2 is the date of birth or starting date, and TODAY () is the end date.
Calculate Age from Date of Birth Using DATEDIF Function in Google Sheets
We will use the following dataset, which includes customers’ names, dates of birth, professions, and genders. We want to calculate the age of the customers using the date of birth column.
Calculate Age in Years Only
Here is how to calculate age from date of birth in years only with the DATEDIF function.
Steps:
➤ First, select a blank cell and write a column name, such as “Age”.
➤ Now select cell E2 and insert the following formula:
=DATEDIF (B2, TODAY (), “Y”)
➤ Press Enter, and you’ll find the age in years only from the date of birth.
➤ Now, select the cell showing the age and keep the cursor on the bottom right corner of the cell (you will see a small circle there).
➤ Then, drag the cursor, and it will fill in the respective ages of other customers.
Calculate Age in Both Years and Months
Now, we will use the DATEDIF function to calculate age in both years and months.
Steps:
➤ First, select cell E1 and name it as “Ages in years and months”.
➤ Now, select cell E2 and insert this formula as:
=DATEDIF(B2, Today (), “Y”) & ” Years ” & (DATEDIF (B2, TODAY (), “YM”) & ” Months”)
➤ Press Enter and drag the cursor to fill in respective ages for all the customers.
Calculate Ages in Years, Months, and Days
Now, to calculate the ages in years, months, and days, we will use a modified version of the DATEDIF function.
Steps:
➤ First, select cell E1 and give it a name.
➤ Now, in cell E2, write down this formula:
=DATEDIF(B2, Today (), “Y”) & ” Years ” & (DATEDIF (B2, TODAY (), “YM”) & ” Months”) & (DATEDIF (B2, TODAY (), “MD”) & ” Days “).
➤ Then, press Enter, and drag the cursor down to fill the respective ages for respective persons.
Modified Version of the DATEDIF Function to Calculate Age for a Particular Date
To calculate the age of someone on a particular date, we have used a modified DATEDIF function.
Steps:
➤ First, select the cell E1 and give it a name.
➤ Now, in the cell E2, write down the particular date.
➤ Now, select the cell F1 and name it as “Age for That Date”.
➤ Then, select cell F2 and write down the formula as:
=DATEDIF (B2, $G$2, “Y”).
Here, the end date, TODAY (), is replaced with the cell reference of the particular date.
➤ Now, press Enter and drag the cursor down to fill in for all the customers.
Calculate Age from Date of Birth Using the YEARFRAC Function in Google Sheets
If you want to calculate the total age with fractions of a year in Google Sheets, the YEARFRAC function is the most suitable one.
Steps:
➤ First, select cell E1 in the header row and give a name, such as “Age in Fraction”.
➤ Now, select cell E2, and write this formula:
➤ Now, to reduce the visible digits after the fraction, use the decreased decimal symbol, i.e., .0 ←.
➤ Finally, drag the cursor to fill the column for the ages of all customers.
FAQ
How to Calculate the Age between Two Dates in Google Sheets?
Using the formula DATEDIF, you can calculate the age between two dates. Write down the two dates in separate cells, say, A2, B2. Then, in another empty cell, write this formula:
=DATEDIF ( A2, B2, “Y”)
Then, press Enter and it will return the age in years between the two dates.
How to Calculate Age from Multiple Rows in Google Sheets at Once?
To calculate age from multiple rows in Google Sheets at once, you need to use ARRAYFORMULA. Write this formula:
=ARRAYFORMULA (DATEDIF (A1:A5, TODAY(), “Y”)).
Now, press Enter and tt will return all the ages in cells A1 to A5 at once.
How to Calculate Age from Multiple Columns in Google Sheets at Once?
For calculating age from multiple columns in Google Sheets, a modified ARRAYFORMULA is used. The formula is:
=ARRAYFORMULA (DATEDIF (A8:D8, TODAY(), “Y”)).
However, remember to keep the cells in the consecutive rows where you wrote the formula empty.
Wrapping Up
In this article, we have explained 5 different methods for calculating age from date of birth in Google Sheets using two functions, DATEDIF and YEARFRAC. You can download the file to practice and also share your thoughts or inquiries with us.
