Calculating age in Excel is useful when you need to manage several dates of birth at once. Instead of checking each record manually, you can enter one formula and apply it to an entire list of students, employees, patients, clients, or survey participants.
Excel can calculate completed age in years, display a detailed result in years, months, and days, or update age automatically using today’s date. You can also calculate age on a fixed date, such as a school assessment date, admission date, appointment date, or employment start date.
Quick answer: If the date of birth is in cell A2 and the calculation date is in B2, use
=DATEDIF(A2,B2,"Y")to calculate completed age in years.
Why Excel Is Useful for Age Calculation
An online Chronological Age Calculator is convenient when you need one quick result. Excel is more practical when you need to calculate and manage age across many records.
Excel can help you:
- Calculate age for an entire list
- Use the same calculation date for every record
- Update current ages automatically
- Reduce repeated manual work
- Keep age information alongside other data
- Copy the same formula across hundreds of rows
The main requirement is that Excel recognizes both entries as valid dates. The date of birth must also come before the calculation date.
How Excel Calculates Age Between Two Dates
Excel calculates age by comparing two dates:
- The date of birth
- The selected calculation date
The calculation date may be today, a school test date, an admission date, an appointment date, or another official reference date.
For example:
Date of birth: 15 March 2010
Calculation date: 10 June 2026Excel can use these dates to return completed years or a detailed result containing years, months, and days.
How to Arrange Dates in Your Excel Sheet
Before entering a formula, arrange your worksheet clearly:
Enter the date of birth in A2 and the date up to which age should be calculated in B2. The age formula will be entered in C2.
Using a separate calculation-date column makes it easier to calculate age on a fixed event date rather than always using today’s date.
The Fastest Excel Formula for Completed Age
The DATEDIF function is one of the simplest ways to calculate completed age in Excel.
If the date of birth is in A2 and the calculation date is in B2, enter this formula in C2:
=DATEDIF(A2,B2,"Y")The "Y" unit tells Excel to return the number of complete years between the two dates.
| Date of birth | Calculation date | Result |
| 15/03/2010 | 10/06/2026 | 16 |
Calculate Age in Years, Months, and Days
To calculate a detailed age in Excel, you can calculate the completed years and remaining months with DATEDIF, then calculate the remaining days from the adjusted date.
If the date of birth is in A2 and the calculation date is in B2, use:
=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&(B2-EDATE(A2,12*DATEDIF(A2,B2,"Y")+DATEDIF(A2,B2,"YM")))&" days"
For example, if the date of birth is 15 March 2010 and the calculation date is 10 June 2026, the result is:
16 years, 2 months, 26 days
The formula works in three parts:
DATEDIF(A2,B2,"Y")calculates completed years.
DATEDIF(A2,B2,"YM")calculates the remaining completed months.
EDATEmoves the birth date forward by those completed years and months, and Excel then calculates the remaining days.
You may also see formulas that use:
=DATEDIF(A2,B2,"MD")
for the remaining days. However, Microsoft notes that the "MD" argument has known limitations and may return inaccurate results in some situations. For that reason, the formula above avoids relying on "MD" for the final day calculation.
For an important school, assessment, or official record, it is also a good idea to verify the final result with a dedicated chronological age calculator.
Calculate Current Age Automatically with TODAY
If you want Excel to calculate a person’s current age automatically, use the TODAY() function as the calculation date.
If the date of birth is in A2, use:
=DATEDIF(A2,TODAY(),"Y")
This returns the person’s number of completed years as of today.
For example, if A2 contains a date of birth, Excel compares that date with the current date and updates the age automatically whenever the worksheet recalculates.
Current Age in Years and Months
To show completed years and remaining months, use:
=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months"
Use TODAY() when you want the age to stay current automatically.
Do not use TODAY() when you need age on a fixed date such as a school assessment date, admission date, appointment date, or historical record. In those cases, enter the required reference date in another cell and calculate age using that date instead.
Calculate Age on a Fixed Date in Excel
When you need to calculate age on a specific date rather than today, enter that date in a separate cell.
For example:
- A2 = Date of birth
- B2 = Calculation date
- C2 = Age result
- Admission date
- Appointment date
- Employment start date
- Research or survey date
- Historical record date
To calculate completed age in years, use:
=DATEDIF(A2,B2,"Y")
To calculate completed years and remaining months, use:
=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months"
Using a fixed date is useful when age must be recorded as of a particular event, such as:
Unlike TODAY(), a fixed date does not change automatically. This makes it better when you need to preserve the age exactly as it was on a specific date.
For example, if a person was born on 15 March 2010 and the required calculation date is 10 June 2026, enter those two dates in A2 and B2 and use the formula above.
For school or testing situations, use the actual assessment date required by the record rather than today’s date.
Online Chronological Age Calculator vs Excel
Both online tools and Excel formulas are useful, but they are used for different needs.
If you only need one quick result, an online calculator is easier. If you have many records, Excel is better.
Common Excel Age Formula ErrorsAge formulas in Excel can give incorrect results when the dates or cell references are not set up properly. Check these common problems before changing your formula. 1. Excel Does Not Recognize the DateA date may look correct but actually be stored as text. If Excel does not recognize A2 or B2 as a real date, the age formula may fail. Make sure both cells contain valid Excel dates rather than plain text. 2. DATEDIF Returns #NUM!If The date of birth must be earlier than the calculation date. For example:
Then use:
If A2 contains a later date than B2, the formula can return an error. 3. Dividing the Number of Days by 365Avoid calculating age with:
This gives an approximate decimal value rather than a person's completed calendar age. It can also be affected by leap years and different calendar lengths. For completed age in years, use:
4. Using TODAY() When You Need a Fixed Date
If you need age on a school assessment, admission, appointment, or other historical date, enter that specific date in B2 instead of using 5. Copying the Formula with the Wrong Cell ReferencesWhen calculating age for many rows, check that each formula points to the correct date-of-birth cell and calculation-date cell before copying it down the worksheet.
A small cell-reference mistake can produce incorrect ages across an entire list. Best Practices for Accurate Excel ResultsFor reliable age calculations in Excel, follow these practices:
Example Worksheet SetupA simple worksheet can use:
In C2, enter:
Then copy the formula down the column for the remaining records. If everyone in the worksheet should be calculated on the same reference date, you can place that date in one fixed cell and use an absolute cell reference when copying the formula.
For important records, always verify the input dates before relying on the calculated age. FAQsHow do I calculate completed age in Excel?If the date of birth is in A2 and the calculation date is in B2, use:
This returns the number of completed years between the two dates. How do I calculate current age in Excel?If the date of birth is in A2, use:
Because How do I calculate age on a specific date in Excel?Enter the date of birth in A2 and the required calculation date in B2, then use:
This is useful when you need age on an assessment date, admission date, appointment date, or another fixed reference date. How do I calculate age in years and months in Excel?Use:
This returns completed years followed by the remaining completed months. Why does my Excel age formula return #NUM!?
Make sure the date of birth is earlier than the calculation date. Can I calculate ages for an entire list in Excel?Yes. Enter the age formula in the first result cell and then copy or drag it down the column. Excel will adjust the row references automatically for each person. Related Age Calculation GuidesConclusionExcel is a practical way to calculate age when you need to work with multiple dates of birth in the same spreadsheet. For completed age in years, use:
For a current age that updates automatically, use:
If you need age on a specific assessment, admission, appointment, or record date, use that fixed date instead of For accurate results, make sure Excel recognizes your entries as valid dates, confirm that the date of birth comes before the calculation date, and check your cell references before copying formulas across a large list.
Excel is especially useful for managing many records at once, while an online age calculator is often faster for a single calculation.
|