On multiple occasions, you may want to calculate the ages of people according to their date of birth.
If you’re an educationist or teacher, this feature would be very helpful when you’re trying to figure out the present age group of students in your class.
If you think you can do this manually by easily subtracting the birth year and present year, it might become tiresome when you’re dealing with a large dataset with multiple dates of birth.
By doing so, you’d also have to consider multiple facts like the dates, months and years.
In the quick tutorial, we would learn and discuss how to use Google Sheets to find the date of birth by the use of formulas and most importantly, specify the dates and months too.
Calculate Age in Google Sheets – Number of Years
Here, we are going to learn the formulas used to calculate only the number of years difference between a given date.
In this section, we are using the sample worksheet below to display how to use the formulas. In cell A2, we have a date of birth and in cell B2, we have the present date.
If you don’t want to directly enter the current date to avoid formatting issues, you can simply use the TODAY function which we have used in the example.
Also, the DATE function helps to enter a specific date into a cell. If the date is entered normally, Google Sheets might not understand the input as a date, only you format it or use a date function.
So we want to find the age difference and total time difference between the two dates.
This is done with the use of two formulas. They are.
- The DATEDIF Formula.
- The YEARFRAC Formula.
Using The DATEDIF Formula
The DATEDIF formula is also known as the Date Difference Formula. Now, we are going to see how this formula can be used. The DATEDIF formula takes the syntax below.
=DATEDIF(start date, end date, unit).
Where:
- The Start Date is the date of birth.
- The End Date is the present date.
- The Unit is what you want the formula to find and return. These units are of six known types.
Y– this unit represents the total year difference between the start and end dates.
M – this unit represents the total number of months difference between the start and end dates.
D – this unit represents the total number of days difference between the start and end dates.
MD – this unit represents the total number of days difference between the start and end dates. This unit doesn’t include the completed years and months.
YM.– this unit represents the total number of months difference between the start and end dates. This unit doesn’t include the completed months and years.
YD – this unit represents the total number of days passed between the start and end dates. It doesn’t include the completed years.
In the sample above, the date of birth is in cell A2 while the end date is in cell B2. Since we want to know the year’s difference, the unit would be placed as “Y”.
Therefore, the formula used would be entered like this.
=DATEDIF(A2, B2, “Y”)
Step 1: Enter the above formula in an empty cell. In this sample, we entered the formula in cell C2.
Step 2: Hit the enter key and the elapsed time between the two dates is returned.
Using The YEARFRAC Formula
This is the second formula used to calculate the time difference between the start and end dates. This formula would return the years between the two dates.
The YEARFRAC formula takes the syntax below.
=INT(YEARFRAC(start date, TODAY())).
The YEARFRAC formula would return the number of years between the start and end dates.
The INT function helps to return an integer part of the age.
Let’s say we have the date of birth in cell A2. So we enter the formula in
=INT(YEARFRAC(A2, TODAY())) in cell B2.
Hit the Enter key and the formula returns the time difference between the two dates.
Calculate Age in Google Sheets – Number of Years, Months and Days
The above formulas are only for calculating years in Google Sheets but in cases where you need to specify the month and dates, you’ll need a different formula.
Using the DATEDIF Formula
In cell A2, we have a date of birth and in cell B2, we have the present date. We would use the previous formula but we would change the units of the formula from Y to YM, YD or MD.
In this case, we want to find the months. So we would enter the formula into cell C2 like this.
=DATEDIF(A2, B2, “YM”)
Hit the Enter key and the formula returns a 5. That is 5 months have passed after 30 years.
In case you want to calculate the number of days passed you would change the unit to MD. That is,
=DATEDIF(A2, B2, “MD”)
Hit the Enter key and the formula returns the total number of days elapsed between the start and end dates. It returns a 22, which means 22 days have passed after 30 years.
In case you also use Microsoft Excel, these functions and formulas also perform the same purpose.
Final Thoughts
These are the known ways and formulas used to calculate age in Google Sheets.
If you frequently need to know age differences between a specific group of people, this formula would come in handy for you.
Now you know How To Calculate Ages In Google Sheets. I hope you found this guide helpful. Thanks for reading. Enjoy.
