Age Calculator in Excel is useful when you have a list of dates of birth and need to calculate current age, age on a particular date, or exact completed years, months, and days. Excel already contains the date and time functions needed for these calculations, so you can build an age calculator without manually counting birthdays.
Microsoft’s documentation specifically describes DATEDIF as useful for calculating age and shows how it can return complete years, months, or days between two dates. Microsoft also documents TODAY() for using the current date in a formula.
This guide explains practical Excel formulas for age calculation from a date of birth, including today’s age, age on a fixed date, years-months-days format, total days, total months, and common mistakes. It also explains an important Excel warning: Microsoft says DATEDIF exists for compatibility with older Lotus 1-2-3 workbooks and may produce incorrect results in some scenarios, particularly with the MD unit.
Quick Answer: The Best Excel Formula for Age
If the date of birth is in cell A2, the simplest exact-age formula in completed years is:
=DATEDIF(A2,TODAY(),"Y")This returns the number of complete years between the birth date and today. Microsoft documents the Y unit as the number of complete years between the two dates.
If you want age on a specific date instead of today, put the reference date in another cell, such as B2:
=DATEDIF(A2,B2,"Y")This is often the better formula for an eligibility list, application cutoff, historical age, or any worksheet where the same reference date must be used for every person.
Excel Age Calculator Setup
Start with a simple worksheet. Put each person’s date of birth in one column and use adjacent columns for the calculated age. For example:
| Column | Purpose | Example |
|---|---|---|
| A | Date of birth | 15-Jun-2000 |
| B | Reference date | 21-Sep-2026 |
| C | Completed age | 26 |
| D | Years, months, days | 26 years, 3 months, 6 days |
| E | Total days | 9,589 |
| F | Total months | 315 |
Make sure the birth-date and reference-date cells are stored as real Excel dates rather than text that only looks like a date. Excel stores dates as serial numbers, which allows date arithmetic and date functions to operate on them.
1. Calculate Current Age from Date of Birth
For an age that should update automatically as the workbook’s current date changes, use TODAY() as the end date:
=DATEDIF(A2,TODAY(),"Y")If A2 contains 15-Jun-2000, the formula returns the completed age based on the date when Excel recalculates the workbook. Microsoft notes that TODAY returns the current date and can be used inside date-interval calculations.
Why this formula is better than subtracting years alone
A formula such as =YEAR(TODAY())-YEAR(A2) only compares the calendar years. It does not check whether the person’s birthday has occurred yet this year. Microsoft lists that simpler YEAR/NOW approach as one way to calculate age, but a complete-years DATEDIF calculation directly measures the completed years between the two dates.
| Date of birth | Reference date | Year subtraction | Completed age |
|---|---|---|---|
| 15-Jun-2000 | 21-Sep-2026 | 26 | 26 |
| 15-Dec-2000 | 21-Sep-2026 | 26 | 25 |
| 31-Dec-2000 | 01-Jan-2026 | 26 | 25 |
The examples show why a birthday check matters. Two people with the same birth year can have different completed ages on the same reference date.
2. Calculate Age on a Specific Date
For a fixed cutoff date, enter the date in B2 and use:
=DATEDIF(A2,B2,"Y")This is useful when calculating age for a historical date or a published eligibility cutoff. It also prevents a worksheet from changing every day simply because today’s date changes.
For example, if A2 is 15-Jun-2000 and B2 is 21-Sep-2026, the result is 26 complete years. If B2 is 01-Jun-2026, the result is 25 because the June 15 birthday has not yet occurred.
3. Calculate Exact Age in Years, Months and Days
If you want a result such as 26 years, 3 months, 6 days, calculate each component separately. Microsoft documents the Y unit for complete years and YM for remaining months after complete years. Its date-difference guidance also shows a method for calculating remaining days.
=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months"For the remaining days, Microsoft warns against relying on the MD argument because it has known limitations. Its documented workaround is to subtract the first day of the ending month from the ending date.
=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&(B2-DATE(YEAR(B2),MONTH(B2),1))&" days"That last formula follows the documented workaround for the remaining-day component rather than using DATEDIF’s MD unit. For workbooks used for important records, test the formula against several known dates before relying on it at scale.
4. Calculate Age in Total Days
If you need the number of elapsed days rather than completed years, Excel can subtract one date from another because dates are stored as serial numbers. Microsoft documents simple date subtraction for finding the number of days between dates.
=B2-A2Format the result as General or Number if Excel displays it as a date. If A2 is the birth date and B2 is the reference date, the result is the number of calendar days between them.
For today’s total days, use:
=TODAY()-A2The result changes as the current date changes.
5. Calculate Age in Total Months
For completed months between two dates, use the M unit:
=DATEDIF(A2,B2,"M")Microsoft defines the M unit as the number of complete months in the period. This is different from multiplying completed years by 12 because the remaining months must be evaluated from the actual dates.
For today’s total completed months:
=DATEDIF(A2,TODAY(),"M")6. Calculate Age in Weeks
Excel does not need a separate age-in-weeks function. Once you have total elapsed days, divide by 7:
=(B2-A2)/7This produces a decimal number of weeks. If you want only completed whole weeks, use INT:
=INT((B2-A2)/7)For today’s completed weeks:
=INT((TODAY()-A2)/7)This is a simple elapsed-time calculation. It is different from a calendar-week or ISO-week calculation, where the week number of the calendar can matter.
7. Calculate Age in Hours, Minutes or Seconds
Excel dates can include time as well as a calendar date. If your birth timestamp is in A2 and the reference timestamp is in B2, the difference can be converted into smaller units.
| Result needed | Formula | What it represents |
|---|---|---|
| Days | =B2-A2 | Elapsed calendar-day fraction |
| Hours | =(B2-A2)*24 | Elapsed hours |
| Minutes | =(B2-A2)*1440 | Elapsed minutes |
| Seconds | =(B2-A2)*86400 | Elapsed seconds |
These calculations are appropriate when both cells contain date-and-time values. If the cells contain dates only, the result represents the elapsed time from midnight on the start date to midnight on the end date.
8. Build a Reusable Age Calculator in Excel
For a reusable worksheet, keep the inputs separate from the formulas. A simple structure is:
- Enter each person’s date of birth in column A.
- Enter one common reference date in B1, such as the application cutoff date.
- Use an absolute reference to B1 in the age formula so it stays fixed when you fill the formula down.
- Format the output columns as Number or General.
- Add validation to the date columns if other people will enter data into the worksheet.
- Test the sheet with birthdays immediately before, on, and immediately after the reference date.
For example, if B1 contains the common cutoff date and A2 contains a date of birth, use:
=DATEDIF(A2,$B$1,"Y")The dollar signs make B1 an absolute reference. When the formula is copied down, every person’s age is still calculated against the same cutoff date.
9. Use an Age Calculator Formula with Today’s Date
If you do not need a fixed cutoff, combine DATEDIF with TODAY:
=DATEDIF(A2,TODAY(),"Y")This is particularly useful for a continuously updated employee list, student list, customer database, or personal age tracker. The workbook will use the current date when Excel recalculates it. Microsoft notes that TODAY can change when the file is opened on a later day.
10. Common Excel Age-Calculation Mistakes
- Using YEAR subtraction when you actually need completed age.
- Forgetting that the reference date affects the result.
- Storing dates as text instead of real Excel dates.
- Using DATEDIF with a start date later than the end date. Microsoft notes that this produces #NUM!.
- Using DATEDIF’s MD unit without considering Microsoft’s documented limitations.
- Mixing date-only values with date-and-time values without deciding what the time component should mean.
- Using a changing TODAY() formula when the calculation needs a historical or official cutoff date.
- Copying a formula down without making a common reference date absolute.
Why DATEDIF Can Return an Unexpected Result
DATEDIF is useful, but it has a few details worth understanding. Microsoft says the function is provided for compatibility with older Lotus 1-2-3 workbooks and warns that it can calculate incorrect results in certain scenarios. In particular, Microsoft does not recommend the MD argument because of known limitations.
Another common issue is reversed dates. If the start date is greater than the end date, Microsoft documents a #NUM! result.
For a robust workbook, validate that the date of birth is not later than the reference date and avoid the MD unit when you can use the documented day-calculation workaround.
Age Calculator in Excel vs an Online Age Calculator
Excel is useful when you need to calculate ages for many rows at once, preserve the data in a spreadsheet, or apply one cutoff date to a large list. An online calculator is usually quicker when you only need to calculate one person’s age.
| Use case | Excel | Online age calculator |
|---|---|---|
| One person’s current age | Useful | Usually faster |
| Hundreds of birth dates | Very useful | Less convenient |
| Fixed eligibility cutoff | Very useful | Useful for individual checks |
| Reusable staff/student list | Very useful | Less convenient |
| No spreadsheet needed | Requires Excel | Useful |
| Exact years, months and days | Possible | Usually straightforward |
For a single date-of-birth calculation, the site’s Age Calculator can provide the result directly. For a more detailed explanation of exact age by date of birth, see Age Calculator by Date of Birth. For date differences, see Days Between Two Dates Calculator.
Example: Create a Simple Age Spreadsheet
Suppose your worksheet contains these values:
| Cell | Value |
|---|---|
| A2 | 15-Jun-2000 |
| B2 | 21-Sep-2026 |
| C2 | Formula for completed age |
| D2 | Formula for exact years and months |
Enter this in C2:
=DATEDIF(A2,B2,"Y")Enter this in D2:
=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months"The completed-age result is 26. The second formula expresses the completed years plus the remaining completed months. To add the remaining days safely, use the documented first-day-of-ending-month approach rather than DATEDIF’s MD unit.
How to Handle Leap Years
Excel’s date system handles dates across leap years, so you generally should not manually add or remove a day simply because February 29 appears between two dates. The important point is to give Excel valid date values and let its date functions perform the interval calculation.
Leap-day birthdays can still create a separate policy question when a person or institution defines a birthday-based rule. An Excel formula calculates the dates you provide; it does not decide which legal, administrative, or organizational rule should apply.
How to Check Your Excel Age Formula
- Test someone whose birthday is today.
- Test someone whose birthday is tomorrow.
- Test someone whose birthday was yesterday.
- Test a February 29 birth date against a non-leap year.
- Test a date exactly equal to the reference date.
- Test a birth date later than the reference date and confirm the workbook handles the invalid input appropriately.
Testing boundary dates is especially important if the spreadsheet will be used for eligibility or official records. A formula can be mathematically consistent while the underlying business rule uses a different cutoff convention.
Excel Age Calculator FAQ
What is the formula to calculate age in Excel?
For completed years from a date of birth in A2, a common formula is =DATEDIF(A2,TODAY(),"Y"). For a fixed reference date in B2, use =DATEDIF(A2,B2,"Y"). Microsoft documents DATEDIF’s Y unit as complete years between two dates.
How do I calculate age in Excel without DATEDIF?
You can use YEAR and TODAY or NOW for a simpler year-based calculation, but that approach needs a birthday check if you need completed age rather than just the difference between calendar years. Microsoft documents YEAR/NOW as one age-calculation method.
How do I calculate age on a particular date in Excel?
Put the birth date in A2, the reference date in B2, and use =DATEDIF(A2,B2,"Y"). This keeps the reference date fixed and makes the calculation reproducible.
How do I calculate age in years, months and days?
Use DATEDIF for complete years and remaining months, then calculate the remaining days using the documented workaround based on the first day of the ending month. Microsoft specifically warns about limitations with the MD unit.
Why does my Excel age formula show #NUM!?
A common cause is that the start date is later than the end date. Microsoft states that DATEDIF returns #NUM! when the start date is greater than the end date.
Does TODAY() update automatically?
TODAY() returns the current date, so a formula using it can produce a different age when the workbook is recalculated on a later day. Microsoft notes that recalculation settings can affect when TODAY updates.
Can Excel calculate total months of age?
Yes. Use =DATEDIF(A2,B2,"M") for the number of complete months between two dates. Microsoft defines M as complete months.
Can Excel calculate age in days?
Yes. Subtract the birth date from the reference date, such as =B2-A2, or use TODAY for the current date. Excel stores dates as serial numbers, which makes date subtraction possible.
Sources and Further Reading
- Microsoft Support — Calculate age.
- Microsoft Support — DATEDIF function.
- Microsoft Support — Calculate the difference between two dates.
- Microsoft Support — TODAY function.
- Microsoft Support — Date systems in Excel.
Microsoft’s documentation is the primary source for the Excel function behavior described in this guide. Formula examples are adapted to a simple date-of-birth worksheet so they can be copied into a practical age-calculation workbook.
Conclusion
An age calculator in Excel does not require complicated VBA or a custom add-in. For most completed-age calculations, DATEDIF with a birth date and either TODAY() or a fixed reference date is enough. For detailed output, combine the Y and YM units and use a safer remaining-days calculation rather than relying on DATEDIF’s MD argument.
The most important choice is the reference date. Use TODAY() when the age should update with the current date; use a fixed cell when the calculation must reproduce a historical or eligibility cutoff. Once the dates are stored correctly, Excel can calculate completed years, months, days, weeks, hours, minutes, and seconds from the same underlying dates.
