How to Calculate Age in Excel from Date of Birth
Before using the formula, make sure the birth date has been entered into the Excel cell using the correct date format.
As an example:
- Cell A2 contains the date of birth: 08/15/1995
- You want to display the age in cell B2
To calculate age, you can use the following formulas as needed.
Calculating Age in Excel with the DATEDIF Formula
One of the most practical ways to calculate age based on date of birth is to use the DATEDIF function.
Use the following formula: =DATEDIF(A2,TODAY(),”Y”)
The formula calculates the number of full years between the birth date in cell A2 and today's date.
For example, if a person hasn't reached their birthday in the current year, Excel won't add a year to their age. Therefore, the result is more accurate than simply subtracting the current year from their birth year.
Formula Explanation
The formula consists of several parts:
- A2 is the cell containing the date of birth.
- TODAY() retrieves today's date automatically.
- “Y” asks Excel to calculate the number of full years between the two dates.
The advantage of using TODAY() is that the age will be automatically updated according to the date on your device.
How to Calculate Age in Years and Months in Excel
Sometimes age in years alone isn't enough. For example, if you want to know someone's age in years and months, you can use the formula:
=DATEDIF(A2,TODAY(),”Y”)&” Year” &DATEDIF(A2,TODAY(),”YM”)&” Month”
The result can look like: 31 Years 1 Month
The “Y” part counts full years, while “YM” counts the remaining months after the number of full years is subtracted.
This method is useful when you need more detailed age information.
Calculating Complete Age in Years, Months, and Days
If you need more detailed results, Excel can also display age in years, months, and days. Use the following formula:
=DATEDIF(A2,TODAY(),”Y”)&” Year “&DATEDIF(A2,TODAY(),”YM”)&” Month “&DATEDIF(A2,TODAY(),”MD”)&” Day”
The result will be in the form of: 31 Years 1 Month 15 Days
This format can be used when you need more complete age information than just the number of years.
For a quick check without creating your own formula, you can also use an online age calculator by entering your date of birth and the desired calculation date.
Calculating Age on a Specific Date in Excel
Not all age calculations need to use today's date. In some situations, you may need to know someone's age on a specific date.
For example:
- A2 = date of birth
- B2 = date to be used as a reference
Use the formula:
=DATEDIF(A2,B2,”Y”)
Excel will calculate the number of full years between the birth date in A2 and the reference date in B2. This method is useful for various purposes, such as determining age on a registration date, exam date, school start date, or other specific dates.
Calculating Age with YEARFRAC
Besides DATEDIF, Excel has a YEARFRAC function that can be used to calculate the difference between two dates in years.
For example: =INT(YEARFRAC(A2,TODAY(),1))
YEARFRAC calculates the fraction of a year between your birth date and today's date. The INT function, meanwhile, removes decimals, resulting in a full year.
If the YEARFRAC result is 30.65, for example, the INT function will return 30.
Calculating Age from Multiple Data at Once
One of the advantages of using Excel is that you can quickly calculate the ages of many people. For example, suppose you have a list of birth dates:
| Name | Date of birth | Age |
| Andi | 10/05/1998 | 28 Years |
| Budi | 21/09/2001 | 25 years |
| Siti | 07/12/1995 | 30 years |
If the date of birth is in column B, enter the following formula in column C:
=DATEDIF(B2,TODAY(),”Y”)
Then, copy or drag the formula down to apply it to other rows. Excel will calculate each person's age based on the provided birth dates.
Why Not Just Subtract Years?
You might think that age can be calculated using a simple formula like:
=YEAR(TODAY())-YEAR(A2)
This formula may seem simpler, but the results can be less accurate. The problem is, it only compares the year and doesn't consider whether a person's birthday has already occurred in that year.
For example, a person was born in December 2000. If it is now September 2026, subtracting 2026 – 2000 results in 26. Even though the person has not yet reached his 26th birthday.
Therefore, using DATEDIF is more appropriate when you want to calculate the number of full years based on the date of birth.
How to Fix an Error in the Excel Age Formula
There are times when the entered formula doesn't produce the expected age. Several common causes can be checked first.
Make sure the date format is correct
Excel must recognize the data as dates, not plain text. If the dates are stored as text, the date calculation functions may not work correctly. Check the cell format and change it to Date format if necessary.
Check Date Sequence
In DATEDIF, the start date must be before the end date. If the birth date is after the reference date, the formula may produce an error.
Pay Attention to Excel Regional Settings
Date formats may vary depending on device or Excel settings.
For example: 08/12/2000
can be read as December 8th or August 12th, depending on the date format used. Make sure the date entered matches your regional settings.
Check Formula Separator
In some Excel settings, the argument separator uses a semicolon (;) instead of a comma (,).
If the following formula doesn't work: =DATEDIF(A2,TODAY(),”Y”)
You can try: =DATEDIF(A2;TODAY();”Y”)
Which Excel Formula Should I Use?
The choice of formula depends on the results you need.
If you only want to know your age in full years, use: =DATEDIF(A2,TODAY(),"Y")
If you want to know your age in years and months, use the combination “Y” and “YM”.
Meanwhile, if you need a more complete age, you can combine years, months, and days in one formula.
To calculate age on a specific date, replace TODAY() with a cell reference containing the reference date.
Tips for Calculating Age in Excel Correctly
For more consistent results, use the complete date of birth, including the day, month, and year. Avoid calculating age based solely on the year of birth, as the results may not reflect a person's full age.
Additionally, ensure that the date data is formatted consistently if you're working with multiple rows. This can help reduce errors when formulas are applied to the entire data.
If you need to calculate age based on a specific date, store the reference date in a separate cell. This way, you can change the reference date without having to manually adjust each formula.
Frequently Asked Questions about Calculating Age in Excel
What is the formula for calculating age in Excel?
One formula that can be used to calculate age in full years is: =DATEDIF(A2,TODAY(),"Y")
A2 is the cell that contains the date of birth.
How to calculate age automatically in Excel?
Use the TODAY() function as the end date. This function follows the current date, so the age calculation results can change automatically over time.
How to calculate age in years and months?
You can combine DATEDIF for the year and the remaining months:
=DATEDIF(A2,TODAY(),”Y”)&” Year” &DATEDIF(A2,TODAY(),”YM”)&” Month”
Can Excel calculate age on a specific date?
Yes. Replace TODAY() with a specific date or a cell reference containing the reference date.
For example: =DATEDIF(A2,B2,"Y")
Why is the age calculation result in Excel wrong?
Common causes are the date format is not recognized correctly, the date is stored as text, the date order is reversed, or the date format in Excel is different from the data entered.
Conclusion
There are several formulas for calculating age in Excel, but the DATEDIF function is a practical option when you want to get the number of full years based on your date of birth.
A simple formula like: =DATEDIF(A2,TODAY(),”Y”)
can be used to find current age. If more detailed results are needed, the formula can be extended to display years, months, and days.
By understanding how dates work and the functions used, you can calculate the age of one person or multiple data sets at once more quickly and consistently in Excel.