There are a few methods to calculate age in Excel using formulas. In this tutorial, we will explain how to calculate age in Excel in years from a date of birth.
The formulas detailed can be used to return the difference between two specified dates for other reasons, such as length of service at an organisation, or the difference between two business transaction dates.
Download the calculate age sample file to practise.
How to Calculate Age in Excel?
Let’s get straight into this. The easiest way to calculate age in Excel is to use the method shown in this shorts video. It uses the TRUNC and YEARFRAC functions in a simple age formula.
The formula returns the difference between the date of birth and the current date in years.
=TRUNC(YEARFRAC(C3,TODAY(),1))
How does this work?
The formula uses the YEARFRAC function to return the year fraction between the date in cell C3 and the current date which is returned by the TODAY function.
The number 1 entered for the third argument specifies the use of the actual number of days each month of the year.
=YEARFRAC(C3,TODAY(),1)This results in the number of years difference and a decimal part representing the proportion progressed through the current year.
The TRUNC function is then used to remove the decimal part from the result.
The INT function could have been used instead of TRUNC. It is really just a matter of preference.
The INT function returns the integer from a given value, while the TRUNC function truncates a value to a specified number of decimal places. As no decimal places were offered in this use of TRUNC, no decimals were returned.
So, they are both good options along with the YEARFRAC function.
If this does not work, the first thing to check is that the date is in date format. If not, there a several ways to convert text dates to a real date format (this video shows several methods).
Using the DATEDIF Function
An alternative method to calculate age in Excel is to use the DATEDIF function. This function will compare two dates and return the difference in years, months, or days.
This function requires three pieces of information – the start date, end date, and the interval entered as a string.
The following formula calculates age in years with DATEDIF.
The birth date is provided by cell C3, the TODAY function returns today’s date, and “y” is entered for the interval to calculate age in complete years.
=DATEDIF(C3,TODAY(),"y")
This function is undocumented in Microsoft Excel due to a known issue with the “MD” unit (ignored in this tutorial, so no worries). Because of this, no assistance is provided as you type the formula on the worksheet.
You might find this example simpler than the previous example. But due to being undocumented, the use of the YEARFRAC function with TRUNC is preferred by me.
Calculate Age in Years and Months
A DATEDIF formula that combines two DATEDIF functions can be used to calculate age in years and months.
One DATEDIF function to return age in complete years and another for the difference in months, with the years ignored, can be combined into a single result.
=DATEDIF(C3,TODAY(),"y") & " years, " & DATEDIF(C3,TODAY(),"ym") & " months"
In this age formula, the two dates are provided using the date of birth in column C and the TODAY function for the current date.
The first DATEDIF uses “y” for the unit argument to calculate age in years. The second DATEDIF function uses the unit of “ym” to return the date difference in months with years ignored.
Each DATEDIF formula is joined using the ampersand character “&” along with text to state years and months.
Because the result of this age formula is a string, further calculations such as to return the average age are not possible. For this, each DATEDIF function for the year and month calculations could be entered in different columns, or the example using the YEARFRAC function could be used.
Calculate Age between Two Dates
You may need to calculate age between two specified dates rather than using today’s date as the end date as shown in all examples so far.
Reasons for this include, wanting to calculate someone’s age at a given date, or wanting to calculate the time taken to deliver a project.
The following formula calculates age in Excel using the start date in cell C5 and the end date in cell D2. The reference to the end date is made absolute to prevent the reference from changing when you copy the formula down.
=TRUNC(YEARFRAC(C5,$D$2,1))
Use Power Query to Calculate Age in Excel
Using Excel formulas is great. I love them!
However, there are other approaches, and if your data is coming from an external source, Power Query can be used to calculate age before loading it to the table, PivotTable, or data model.
In the following image, we have a table loaded into Power Query with a date of birth column. This column has the date data type applied.
Let’s calculate age in Excel from date of birth using Power Query.
Click on the [D.o.B] column to select it and click the Add Column tab on the Ribbon. Then click Date > Age.
A new column is added, and the age is returned as the number of days difference between the current date and the person’s date of birth. The column has the duration data type. This is identified by the clock icon in the columns header.
To convert the duration to completed years difference, click the Transform tab, and then click Duration > Total Years.
The [Age] column now shows the age as a decimal value similar to how the YEARFRAC function worked.
To remove the decimal part and calculate age in complete years, click Transform > Rounding > Round Down.
The final age is calculated and shown with the whole number data type.
The query can now be loaded as a table to Excel, or directly to a PivotTable or into the data model.
Calculating age in Excel Power Query is straight forward. A few clicks and no formulas to write. This make it user friendly for all.
The age in Excel will be updated when the queries are refreshed.
Wrap Up
This tutorial looked at how to calculate age in Excel using formulas, and an example that used Power Query.
If you want to master Excel formulas, grab a copy of my Advanced Excel Formulas book. It covers more than 150 Microsoft Excel functions and is complete with more then 600 formula examples including the sample files to practise yourself.
Leave a Reply