Google Sheets is a great option when it comes to a spreadsheet work. The cloud software allows you to do a host of things like creating spreadsheets for work, business, and organisations. With it, you can create charts and graphs, do complex calculations, calculate time and date, and many more.
With the functions in Google Sheest, you can do many powerful computations. One of such function is the DATEIF function that give you robust options when calculating age. In this guide, we cover the steps on how to calculate age from birthdate…
Determining Age from Birthdate in Google Sheets
There are two functions that you can use to determine age from a given birthdate: DATEDIF and YEARFRAC. Let’s dive right in to see how to use these functions.
How to Calculate Age in Google Sheets using the DATEDIF Function
First, you’ll need to know how the DATEDIF function works. You can do this by understanding the syntax. There are different part of the syntax that needs to be explained. Let’s get into it:
Syntax: =DATEDIF(start_date,end_date,unit)
start_date: The function has to start with the birthdate.
end_date: The end date is the date that completes the syntax.
unit: Your output can be in different units depending on which you choose. There are various options such as “Y”,”M”,”D”,”YM”,”YD”, or “MD”.
Y stands for the total number of full years between start date and end dates inputted
YM stands for the number of months following the elapsed years for ‘Y’.
D stands for days. It displays that number of days following the fully elapsed years for ‘Y’.
M stands for the total number of fully elapsed months between both start and end dates.
MD shows the number of days following the fully elapsed months for ‘M’.
D displays the number of fully elapsed days between the start and end dates.
Something to note is that the DATEIF function is a more robust and flexible option for arriving at an age from a birthdate.
How to Use the DATEIF Function
Follow the steps below to use the DATEIF Function.
We’ll be using 4/24/1980 for cell A1. The formula will be placed in cell B1.
Now using the most basic form of the formula, we’ll use A1 as our start_date while today’s date will be the end date, while also determining the age in years by using “Y”. The formula will look like this:
=datedif(A1, today(),”Y”)
You can simply copy, paste the above result and hit enter to get the result.
When enter key is pressed, the age calculated is “42” years displayed in B1.
Now, we can apply the same formula to get the the age in months using ‘M’ instead of ‘Y’. The formula is below:
=datedif(A1, today(),”M”)
The calculated results will be “508” months.
You can do it on and on for the other units option like D”,”YM”,”YD”.
How to Calculate Age in Google Sheets using the YEARFRAC Function
While the DATEDIF function has more flexible options, the YEARFRAC function is for simple and straightforward results.
The formula for it is:
=int(YEARFRAC(A1,today()))
You’ll put the birthdate in cell A1 and insert the formula in B1 to get the result. We’ll use the same birthdate as the former example: 4/24/1980.
The result is 42. Simple and straightforward!
Rounding Up…
The DATEDIF and YEARFRAC are useful functions for calculating age. They help you with various options so that you can work with dates and age in a more flexible way, especially that of DATEIF. We hope you found this guide helpful? If you did, kindly leave a comment and don’t forget to share…