Calculate Age From Date of Birth With Excel – Quick & Simple!

Find age from a DOB in excelEveryone knows asking up front, “How old are you?” isn’t a great starter!

But if you have someone’s date of birth, working their age out in your head can get a bit tricky too.

So why not use Excel to calculate age based on the date of birth?

We use this example all the time on our Microsoft Excel courses, it’s a great way to use abstract formulas for realistic day to day tasks like calculating age.

Simple Excel Formula For Ages

Out of the various ways that can be used to calculate age in Excel from date of birth in years, the following age formula is the easiest and simplest way to calculate age.

This is particularly because it works on common sense.

Syntax:

= (TODAY() - Cell reference)/365

 

    • TODAY() represents today’s date, crucial for calculating age accurately.
    • 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 age formula helps you yield the output.
    • How would you normally calculate age using your mind?
    • By simply subtracting your birth year from the present year!

Here is a short example of how you may employ this formula to calculate someone’s age by simply editing it in the function bar.

Note: Make sure the specific date in B2 is formatted as a date!

The exact way that data is formatted is very important but easy to overlook when working with spreadsheets.

We cover this in depth on our Excel training.

Type the formula in the function bar as follows.

=(TODAY()-B2)/365)

 

This is going to make it easy to calculate age with just a few clicks.

You could also just type in the current date instead of TODAY! But TODAY will update itself automatically.

B2 consists of the birth date of the employee whose age is to be calculated.

Here is how Excel can calculate age against the birth date given in cell B2, demonstrating how to calculate age effectively.

Calculating age in years

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

Using the Fill Handle to instantly calculate the age of all employees

This quick function can help you calculate age for a large group of people with ease, ensuring you have up to date information on age.

You can also use this in combination with the Average Function to find out the average age for your office!

The formula is easy to remember, and you can edit it to yield different results. This is the method delegates on our course prefer to calculate someone’s age.

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 perfectresults as some months have different amounts of days!

However, it can help you have a quick rough idea of the number of months between dates that are not too far.

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])

 

  • 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)

Here is a quick example of how the YEARFRAC function works:

=YEARFRAC(B2, TODAY(),1)=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.

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.

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.

Using the YEARFRAC function to find age using Basis as 2

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.

Using the YEARFRAC function to find age using Basis as 3

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.

Using the ROUNDOWN Function to round down the age to the nearest integer

If you want to learn more handy formulas in Excel, read our guide here on the tranpose function in excel.

DATEDIF Function

The DATEDIF function is another powerful tool in Excel for calculating age based on specific dates, offering a precise method to calculate age.

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 dates.
    5. YD – gives in output in terms of days only ignoring the number of years between the dates.
    6. MD – gives in output in terms of days only ignoring the number of years between the dates.

This means unlike the other functions, this formula displays age in a number of different formats.

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

The DATEDIF Function is a variation on the DATE Function.

=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.

Excel has calculated the number of months that fall between the start date and the end date.

The number of months between the two given dates is calculated

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”)

 

The number of days between the two given dates is calculated ignoring the months

Note: The DATEDIF function dosent need 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.

For more guides, check out this guide to percentages in Excel!

excel-promo-2

Troubleshooting – Calculating Ages

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 specific date.

If you have input a specific 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.

Choosing the format as ‘Date’

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

More Number Formats for different formatting styles

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”)

 

Age calculated in terms of years by using dates as text strings

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.

You can get the serial number value for a given date by using the DATEVALUE function as follows.

= DATEVALUE ("date_text")

 

Computing the date value by using the DATEVALUE Function

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.

Age calculated in terms of years by using dates as serial numbers

Conclusion:

Excel age formulas can be of great help for several purposes.

Using these 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!

For more tips check out this guide: Excel for SEO training.

 

 

About Ben Richardson

Ben is a director of Acuity Training which he has been running for over 10 years.


He is a Natural Sciences graduate from the University of Cambridge and a qualified accountant with the ICAEW.


He previously worked as a venture capitalist and banker and so had extensive experience with Excel from building financial models before moving to learn SQL, Microsoft Power BI and other technologies more recently.