Calculate Age from Date of Birth in Excel – Acuity Training

Looking out to compile the age statistics for a large group of people?

Let go of the fatigue to do manual calculations that are likely to be prone to human errors!

There are a number of ways how you can quickly calculate the age of several persons flawlessly and effortlessly.

This is just one of Excels countless usefull features, take a look at our Excel course offerings for more on Excel.

 

Simple Excel formula for age in years

Out of the various ways that can be used to calculate age from date of birth in excel in years, the following formula is the easiest and simplest. This is particularly because it works on common sense.

Syntax:

= (TODAY() – Cell reference)/365

 

• TODAY() represents the date today.
• Cell Reference represents the cell where you must have input the birth date.
• 365 represents the number of days in a year. Dividing the first part of the formula helps you yield the output in ‘years’

How would you normally calculate your age using your mind? By simply subtracting your birth year from the present year. Through this formula, excel does the same with the only difference that it goes a step ahead and also accounts for the months and days in addition to years.

 

Here is a short example of how you may employ this formula to calculate ages by simply editing it in the function bar. Type the above formula in the function bar as follows.

 

=(TODAY()-B2)/365)

 

B2 consists of the birth date of the employee whose age is to be calculated. Here is how excel calculates the age against the birth date given in cell B2.

 

 

Drag the Fill Handle to calculate the age of all the employees appearing in the list as follows.

 

 

Pro Tip: If you want the age to be in round integers and not in decimals, use the INT function to round the age down to the nearest integer as follows.

 

 

This quick function can help you yield age for a large group of people with sheer ease. The formula is easy to remember, and you can edit it to yield different results. For instance, replace the ‘365’ in the above formula with ‘30’ to yield age in months rather than in years.

 

=(TODAY()-B2)/30)

 

Doing so might not return utterly flawless results as some months have 30 days, others have 31 days, and one has 28 or 29 days (during a leap year). However, it can help you have a quick rough idea of the number of months between two dates that are not too far.

Once you calculate someones age, you could send an E-Mail around so everyone knows eachothers birthdays and can celebrate together. Read this guide on how to Email Someone A Sheet Without Leaving Excel for more.

 

How is calculating age in excel of any help?

Calculating age can sometimes be tricky, particularly when you need accurate and fast results. And more importantly, when you have a large set of data to deal with e.g. 1000s of people, each of whose age is to be calculated.

A quick example of how this function may come to handy is in the payroll department. To calculate the employee benefits (amount of provident funds, retirement payouts, and other actuarial estimates, etc.) the age of employees is often required.

Calculating the age of all employees manually can get difficult and is highly prone to human errors. By using the birth date data of all employees coming in from the master personnel file, you can compute the age of each employee till the present date in seconds.

It also helps as you can update the ages anytime later by simply updating the formula without having to redo the entire exercise as you may have done manually. If you have data spread across multiple spreadsheets, use the linking function in Excel for automatic age updates.

The age calculator function in excel not only helps you with calculating ages but can also be employed to calculate the period between two dates or intervals for various other purposes.

 

YEARFRAC Function

The YEARFRAC function allows excel users to yield the fraction of years that lie between two different dates. Syntax of the YEARFRAC function reads as follows.

 

= YEARFRAC (start_date, end_date, [basis])

 

• As the name verily suggests, start date and end_date represent the two dates the period between which is to be calculated
• The basis is an Optional argument that represents the type of day count basis

Basis can range from 0 to 4, and each of them has a different type of day count basis tabulated, as below.

Basis

Day count Basis

0 or if left blank

30 / 360 (US NASD)

1

Actual / Actual

2

Actual / 360

3

Actual / 365

4

30 / 360 (European)

 

If the basis are set as <0 or >4, excel would return the #NUM! error value.

This function is more reliable and tends to offer more accurate results. You can choose the basis for calculation, and the output is in the form of the fraction that represents the actual number of days between two given dates.

Here is a quick example of how the YEARFRAC function works. The YEARFRAC function fed in cell C2 reads as follows.

 

=YEARFRAC(B2, TODAY(),1)

 

B2 contains the birth date which is set as the start date. To calculate the age of each employee till date, the end_date is set as the date today. This can also be changed to any other date. The basis is set as 1 which will give the output in terms of actual number of days in each year.

 

 

The fraction between the two given dates using the Actual / Actual basis gives the actual number of years. It uses the basis 366 for all the leap years falling between 2002 and the present year i.e. 2021 and 365 for all other years.

Calculating the same fraction between the same dates using the basis as 2 gives a different result as the basis used for all the years is 360, irrespective of leap or normal years.

 

 

Calculating the same fraction between the same dates using the basis as 3 also gives a different result as the basis used for all the years is 365, irrespective of leap or normal years.

 

 

Pro Tip: If you want the age to be in round integers and not in decimals, use the ROUNDDOWN function to round the age down to the nearest integer as follows.

 

=ROUNDDOWN (YEARFRAC(B2, TODAY(), 1), 0)

 

You may replace the last ‘0’ with any number being the number of decimal places you want to keep.

 

 

If you want to learn more handy formulas in Excel, read our guide here on The Tranpose Function In Excel.

 

Another way how you can calculate age in excel in years, months, or days is using the DATEDIF Function.

 

Syntax

=DATEDIF (start_date, end_date, unit)

 

Start_date and end_date represent the two dates between which the time period is to be calculated. Whereas, the unit represents the measuring unit in which you want the output. This can take 6 possible forms as listed below.

1. Y – gives in output in terms of years only.
2. M – gives in output in terms of months only.
3. D – gives in output in terms of days only.
4. YM – gives in output in terms of months only ignoring the number of years between the two dates.
5. YD – gives in output in terms of days only ignoring the number of years between the two dates.
6. MD – gives in output in terms of days only ignoring the number of years between the two dates.

Let us see how this function works through an example where the DATEDIF Function is as follows.

 

=DATEDIF (B2, “2021/12/25”, “M”)

 

Start_date is set as B2 as it contains the birth dates of employees. The end_date, however, is set as a random date in the form of a text string. This can also be substituted with the present date by inputting the TODAY() function. The unit is set as M which represents Months.

Excel has calculated the number of months that fall between the start date i.e. 7 June 2002, and the end date i.e. 25 December 2021.

 

 

If the same formula is replaced with a formula containing a different unit, say “MD”, excel would give a different output only considering the number of days between 7th and 25th. The number of months would be ignored.

=DATEDIF (B2, “2021/12/25”, “M”)

 

 

Note: The DATEDIF function needs not to be paired with a rounding-off function like INT or ROUND DOWN as it returns values in a given unit that are already whole numbers.

 

How To Get Age In Years, Months, And Days

While calculating age in excel, you may often want the output (i.e. the age) to be completely represented as let’s say “37 Years, 2 months, 14 days”. To do this, you may have to work out the DATEDIF Function into three different functions.

The ordinary DATEDIF function returns a single value which can be in terms of years, months or days. Let’s stipulate an example to learn the steps that must be followed to get the exact age in terms of years, months, and days.

 

Step 1:

Activate the cell where you want to have the age populated. In the following screenshot, birth dates are populated in Column B, and age is to be populated in Column C.

 

 

To make things simpler, you may name the column containing the birth dates by naming the range.

 

Step 2:

With the DATEDIF Function, using the start and end date, we can yield different outputs. As we need age in multiple terms, we need to employ the following three functions.

To get the output in terms of

Formula

Years

=DATEDIF(B2, TODAY(), “Y”)

Months

=DATEDIF(B2, TODAY(), “YM”)

Days

=DATEDIF(B2,TODAY(),”MD”)

 

Cell B2 consists of the birth dates, and as we need the age till date, the end date is selected as the present date.

 

Step 3:

The next step is to concatenate all the three functions in a single formula to get a single output as follows:

=DATEDIF(B2, TODAY(), “Y”) & DATEDIF(B2, TODAY(), “YM”) & DATEDIF(B2, TODAY(), “MD”)

 

Seems interesting? Learn more about concatenates here.

 

 

Pro Tip:

This might seem a little difficult to understand. To make it more understandable, you can amend the formula as follows.

 

=DATEDIF(B2,TODAY(),”Y”) & ” Years, ” & DATEDIF(B2,TODAY(),”YM”) & ” Months, ” &  “and ” & DATEDIF(B2,TODAY(),”MD”) & ” Days”

 

Doing so excel considers every value in inverted commas as text and adds the same in relevant sequence. Excel would add relevant text after every output and give results as below.

 

If this sounds like a little too long, difficult and error-prone, audit your formula to ensure its correct.

 

Troubleshooting with Calculating Age

Calculating age in excel is pretty simple. However, you may still come across problems that might put you off until you understand the root cause of them. Here are a few troubleshooting tips that you must bear in mind to use the age calculation function of excel effortlessly.

 

1.   Date Format

It is crucial to note how important is the format of the date. If you have input a date into a cell that is in ‘Text’ or any other format, excel wouldn’t recognize it as a date and would pose errors. Here is an example of how this works.

You can change the format of any value to date by going to the Home tab > Number > Number Formats > Short Dates.

 

You can further change the formatting by choosing More Number Formats.

 

 

2.   Dates as text strings

Date formatting is not the only way how you may enter a date into excel. You can alternatively punch in a date manually as a text string by using quotation marks like “2020/01/20”. Using it in any function is as simple as follows.

=DATEDIF(“2002/07/30”, “2021/12/25”, “Y”)

 

 

3.   Dates as serial numbers

Another way how you may punch in dates is as a serial number. Excel recognizes dates as sequential serial numbers where 01 January 1900 is Serial No.1 by default. This means 01 January 1901 is Serial No. 367 and so on.

Don’t begin counting yet – you can get the serial number value for a given date by using the DATEVALUE function as follows.

 

= DATEVALUE (“date_text”)

 

Date text is the given date entered in quotation marks such as = DATEVALUE (“1901/01/01”).

 

Once you have reached the serial number for your desired date, you can use it in the DATEDIF formula as under.

 

=DATEDIF(“Serial No. 1”,”Serial No.2”, “Y”)

 

The below screenshot illustrates how the start and end date is converted into serial numbers (appearing in C2 and D2) using the DATEVALUE function. The same values are then used in the DATEDIF Function.

 

 

Conclusion:

Excel age formulas can be of great help for several purposes. Using the aforesaid functions, you may compute ages for a group of people or simply determine the span between two given dates.

In either case, which function best suits your purpose depends upon the accuracy and detail of the output you are looking for. Keep practicing to master age calculation in Excel!

 

About Ben Richardson

Ben is a director of Acuity Training. He writes about SQL, Power BI and Excel on a number of industry sites including SQLCentral, SQLshack and codingsight.