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

 

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

Type the formula in the function bar as follows.

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.

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

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 then use this in combination with the AVERAGE function to find out the average age in 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.

 

Doing so might not return perfect results 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.

Now your ages have been found, you can use the COUNT function to organise people into different age groups!

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.

 

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

 

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.

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.

 

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

If the data you are working with is in the wrong format, you can use the TRANSPOSE function to flip your data from horizontal to vertical!

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

 

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

 

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.

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 7^th and 25^th. The number of months would be ignored.

 

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.

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 any other format, excel wouldn’t recognize it as a date and would pose errors.

Often when working with Excel, formats like percentages linger in cells even after the data is deleted.

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.

 

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.

 

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

 

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