Do you want to know how to calculate age using the date of birth in Excel? Well, this can easily be done, by learning the steps and formulas to calculate age at a particular date or today’s date. Reading this article will make you able to calculate the age between two dates in Excel. You will also learn how to calculate the age difference in Excel.
Table of contents
How to calculate age using date of birth
There is no specific function in Excel to calculate age, but there are a few different ways to convert the date of birth to age. In Microsoft Excel, you can calculate age from date of birth in years, months, and days.
Using date of birth with TODAY function
There are two formulas in Excel that are similar, and the difference between them is explained below.
• =TODAY() – This function will show you today’s date. Use this function to make your results always up to date
• =NOW() – This function will show you today’s date plus the current time. So it gives you a little more information but is not very useful when calculating the age.
Let’s start using the TODAY function to calculate age in this example. You can start by entering today’s date in cell B2 and write the date of birth in cell B3. Then you have to subtract the date of birth from today’s date. Then divide that number by 365,25 to get the number of years, since every fourth year has 366 years.
Since today’s date is higher, you have to subtract the date of birth from today to find out how many years this person has lived. The example below shows you how to calculate age from today’s date.
If you have entered today’s date in a cell, you can use the formula like the picture below.
If you want to see the results in whole years, then you can decrease the extra decimals. First, you have to click on the cell > Then look for the group Number in the ribbon > Click on the icon that decreases decimals. Each time you click on it, it will remove one decimal. So keep clicking until you get a whole number like the below picture.
Calculate age in Excel with YEARFRAC function
Another formula returns the fraction of the year or the number of whole days between two dates.
=YEARFRAC(start date;end date;basis)
This is how you should use the YEARFRAC function in Excel:
- Start date – Here, you can enter the date of birth.
- End date – Here, you can enter today’s date or any other particular date.
- Basis – This one is optional, so you don’t have to write any number. The default is 0, which means that it counts the days in a 360 days year. But there are a few other options to use.
- 0 – 30/360
- 1 – actual/actual
- 2 – actual/360
- 3 – actual/365
To write the formula, you must understand that the start date should always be lower than the end date. Otherwise, you will get #NUM as a result in your cell.
Excel uses whole days between two dates to work out a year’s fraction as a decimal value. So if the default for basis is 0, you don’t have to fill in a zero in the formula. Try to use YEARFRAC with a start and end date. The formula will look like the picture below.
Remember to write semicolons between the cells to avoid any errors in Excel. If you do not have a particular date or today’s date written in a cell, you can also use the other formula written in cell C4.
If you use basis 1 in the formula, Excel will divide the actual number of days per month divided by the actual days per year. Since we have written today’s date in cell B2, we have to write the formula like the example below.
If you have not written any particular date or today’s date in a specific cell, use the formula below.
=YEARFRAC(date of birth;today();1)
Know that you can always delete the extra decimals by clicking on the icon that decreases decimals, as explained before. Remember to format cells B4:B5 into a Number which are highlighted in the picture below.
Calculate age from date of birth with DATEDIF function
Another way to convert the date of birth to age is to use the DATEDIF function in Excel. This function can calculate the difference between two dates in units such as years, months, or days.
=DATEDIF(start date;end date;unit)
In this example we are going to focus on the age in years, so therefore we are using the “Y” in the formula. This function calculates the complete number of years. First, you have to start by entering the date of birth, as this is the start date. Then write today’s date as the end date, followed by the unit in years.
The start date must be lower than the end date, or you will get #NUM as a result in your cell. The example below shows you how to write the formula.
Remember to write semicolons between the cells to avoid any errors in Excel. If you have not written today’s date in a cell, you can use the formula below.
How to calculate age between two dates in Excel
We have shown you how to use the DATEDIF function in Excel by using the date of birth. With the DATEDIF function, you can also calculate the age between a particular date and the birth date. If you need to calculate the exact number of months or days, then you can choose from different units.
The units in the DATEDIF function are written down as the following:
- Y – the number of complete years between the start and end dates.
- M – the number of complete months between the two dates.
- D – the number of days between the dates.
- YM – the months, ignoring days and years.
- MD – the difference in days, ignoring months and years.
- YD – the difference in days, ignoring years.
With this function, you can calculate the age of a person in years, months, and days. The example below shows how to calculate age from two dates in whole months in Excel.
You can use the same formula for days, but you only have to change the unit into “D”. Don’t forget to format the cell range (C3:E5) into Number.
Days until your birthday
You can also calculate the number of days left until you have your birthday. If you enter today’s date in cell B2, then you can write your next birthday in the cell B3. Since today’s date is higher, you have to subtract your birthday from today’s date.
The example below shows a simple substraction in Excel, without using any functions.
=End date-Start date
Remember to format the cell into a Number, otherwise, the result will look like a date.
You can also use the TODAY function in the formula, which is written in cell C4 in the picture below. Then format the cell, by clicking on the arrow next to Number.
Please read more on How to calculate difference between two dates in Excel
Congratulations! You have managed to calculate the age difference in Excel. It can be done in many ways, by using the TODAY, DATEDIF, and YEARFRAC function. The TODAY function is an easy formula to use. The YEARFRAC function is a more reliable way to convert the date of birth to age. The DATEDIF function is more accurate as it shows the results in years, months and days. It is also possible to calculate the days until your birthday, by using a simple subtraction or by using today’s function.