How to calculate age in Excel with age formula?
If you want to know how to calculate age in Excel with age formula, or want to find age in years, months and days, here’s the instructions.
Steps to Calculate Age in Excel:
1. Enter the date of birth:
- let’s say the person’s birthdate is: March 5, 1997.
First, you need to enter the person’s birthdate in a cell.
For example, let’s enter this date in cell A2.
2. Enter Today’s Date:
In the next cell, you can either enter today’s date manually, or use the =TODAY() function to always get the current date.
For this example, we’ll assume today’s date is February 22, 2023..
You can use =TODAY() in cell B2 to always calculate based on the current date.
3. Calculate the Age in Years:
Now, you can calculate the age using the DATEDIF function, which calculates the difference between two dates. To calculate the age in years, you would use the age formula:
=DATEDIF(A2, B2, “Y”)
This formula will give you the person’s age in full years.
In our case, the formula calculates the number of years between March 5, 1997 and February 22, 2023, cell D2 results in 25, which means the age is 25 years old.
4. Calculate the Full Age (Years, Months, and Days):
If you want to get more specific, such as to break down the age into years, months, and days, you can use the following age formula:
=DATEDIF(A2, B2, “Y”) & ” years, ” & DATEDIF(A2, B2, “YM”) & ” months, ” & DATEDIF(A2, B2, “MD”) & ” days”
This age formula calculates:
- The number of full years between the birthdate and today’s date.
- The number of months after the last full year.
- The number of days after the last full month.
For our example, with March 5, 1997 as the birthdate and February 22, 2023 as today’s date, the result in D3 is:
25 years, 11 months, 17 days
This method allows you to display the person’s age with much more precision.
Breakdown of the Age Formula:
DATEDIF(A2, B2, "Y"): This part calculates the number of full years between the two dates.DATEDIF(A2, B2, "YM"): This calculates the number of months after the last full year.DATEDIF(A2, B2, "MD"): This calculates the number of days after the last full month.
Or you maybe interested to use our Age Calculator to recheck the result.