How to Calculate Age in Excel — Common Bugs + Free Alternative
Quick answer: calculate age in Excel
Use DATEDIF(A2,B2,"Y") for whole years, DATEDIF(A2,B2,"M") for completed months, and B2-A2 for total days. For years, months and remaining days, count completed months and subtract an EDATE anchor—without using DATEDIF "MD".
A2 is the birth date; B2 is the reference date. Both must be real, date-only values, with B2 on or after A2. Format numerical results as Number or General.
Worked example: A2 = 15 March 1990; B2 = 16 April 2026.
Enter =DATE(1990,3,15) in A2 and =DATE(2026,4,16) in B2 to avoid ambiguous date text.
Age in whole years
=DATEDIF(A2,B2,"Y")Example result: 36
Completed years, not a rounded decimal age.
Age in completed months
=DATEDIF(A2,B2,"M")Example result: 433
Total completed months, not just the months left after years.
Age in total days
=B2-A2Example result: 13,181
Elapsed days. Format the result cell as Number, not Date.
Years, months and remaining days
=QUOTIENT(DATEDIF(A2,B2,"M"),12)&" years, "&MOD(DATEDIF(A2,B2,"M"),12)&" months, "&(B2-EDATE(A2,DATEDIF(A2,B2,"M")))&" days"Example result: 36 years, 1 months, 1 days
Uses a completed-month EDATE anchor instead of DATEDIF "MD". Labels are fixed, so 1 keeps the plural.
Reference date: today (put in B2)
=TODAY()To calculate years directly against today, use =DATEDIF(A2,TODAY(),"Y"). TODAY() changes when the worksheet recalculates; use a fixed B2 for an assessment date.
How the combined formula works
Excel is suitable for accurate age calculations when the dates, formula and boundary convention are correct. Microsoft documents DATEDIF, including its units and limitations. It advises against the "MD" argument because remaining-day results can be negative, zero or inaccurate. That is a specific warning, not evidence that every Excel age formula is wrong.
The recommended combined formula first counts completed months with DATEDIF "M". QUOTIENT divides that count into whole years; MOD gives the months left over. EDATE then moves the birth date forward by the completed-month count. Subtracting this anchor from B2 gives the remaining days. Each part is visible and reproducible; no fixed 30-day month or 365.25-day year is assumed.
This is our composition of documented functions, not a combined formula endorsed by Microsoft or Google. It uses English function names and does not require LET, so it also suits Excel versions without LET. Fixed text labels keep “years”, “months” and “days” even for 1. If separate columns are more useful, put total months in C2 and use these helper formulas:
Helper: total months (C2)
=DATEDIF(A2,B2,"M")Helper: whole years
=QUOTIENT(C2,12)Helper: remaining months
=MOD(C2,12)Helper: remaining days
=B2-EDATE(A2,C2)Do not interpret the combined result as a universal legal or clinical definition of age. A month-end anniversary or February 29 birthday needs an explicit policy. Our complete guide to age calculation explains the site’s calendar approach, while the chronological age calculator supplies copyable records for a chosen assessment date.
Verified date examples and boundary conventions
The table uses ISO dates: YYYY-MM-DD. Spreadsheet results are from the recommended formula, executed in the independent HyperFormula 3.5.0 evaluator—not a particular Excel release or hosted Google Sheets. The calendar column uses SnapAgeCalc’s clamped month anniversaries and March 1 birthday rule for February 29 births. A difference can therefore be intentional. Remaining days in the spreadsheet decomposition can reach 30; they are not rounded or forced into a 30-day month.
| Birth → reference | Spreadsheet result | Site calendar | What to check |
|---|---|---|---|
| 1990-03-15 → 2026-04-16 | 36 years, 1 months, 1 days 13,181 total days | 36y 1m 1d | Ordinary anniversary: the spreadsheet and site conventions agree. |
| 2026-01-31 → 2026-02-28 | 0 years, 0 months, 28 days 28 total days | 0y 1m 0d | DATEDIF requires the day-of-month to be reached. SnapAgeCalc clamps a January 31 monthly anniversary to February 28. Different conventions, not a proven Excel bug. |
| 2026-01-31 → 2026-03-01 | 0 years, 1 months, 1 days 29 total days | 0y 1m 1d | EDATE places the one-month anchor on February 28, leaving 1 day. Avoid the documented limitations of "MD"; no untested Excel output is claimed. |
| 2024-02-29 → 2025-02-28 | 0 years, 11 months, 30 days 365 total days | 0y 11m 30d | The 29th has not been reached. SnapAgeCalc also uses March 1 for the birthday in a non-leap year. |
| 2024-02-29 → 2025-03-01 | 1 years, 0 months, 1 days 366 total days | 1y 0m 0d | EDATE clamps the 12-month anchor to February 28; the site's birthday anchor is March 1. The remaining-day convention differs. |
| 2023-03-01 → 2024-03-01 | 1 years, 0 months, 0 days 366 total days | 1y 0m 0d | A complete year containing February 29 has 366 days, not 365.25. |
| 2025-03-01 → 2026-03-01 | 1 years, 0 months, 0 days 365 total days | 1y 0m 0d | INT(365/365.25) is 0 on the first birthday; the correct completed-year count is 1. |
| 2026-04-16 → 2026-04-16 | 0 years, 0 months, 0 days 0 total days | 0y 0m 0d | Identical dates: every elapsed count is zero. |
Google explicitly documents that DATEDIF counts a month only after the end date reaches the starting day of month. January 31 to February 28 therefore has zero completed DATEDIF months, even though February has ended. EDATE’s month-end anchoring and the site’s February 29 birthday rule are separate choices. Compare the leap-year birthday calculator and date difference calculator when those conventions matter.
A naive INT((B2-A2)/365.25) can miss a birthday: 1 March 2025 to 1 March 2026 is 365 days, and INT(365/365.25) is 0, although one year is complete. YEARFRAC is a fractional-year function with selectable financial day-count bases; the default is US 30/360, not actual/actual. Microsoft also warns about its default basis when the start is February’s last day. Neither a decimal-year estimate nor rounding is a substitute for choosing the required completed-birthday rule.
Google Sheets age formulas and local settings
Google Sheets documents DATEDIF and EDATE, so the same English-name formulas are available below. These use real date cells, not arithmetic such as 10/10/2000 supplied as a date argument. The supported functions do not establish that every Excel and Sheets edge case is identical. Test your own worksheet against the relevant dates and policy, particularly for February 29 and month-end births.
Google Sheets: Age in whole years
=DATEDIF(A2,B2,"Y")Completed years, not a rounded decimal age.
Google Sheets: Age in completed months
=DATEDIF(A2,B2,"M")Total completed months, not just the months left after years.
Google Sheets: Age in total days
=B2-A2Elapsed days. Format the result cell as Number, not Date.
Google Sheets: Years, months and remaining days
=QUOTIENT(DATEDIF(A2,B2,"M"),12)&" years, "&MOD(DATEDIF(A2,B2,"M"),12)&" months, "&(B2-EDATE(A2,DATEDIF(A2,B2,"M")))&" days"Uses a completed-month EDATE anchor instead of DATEDIF "MD". Labels are fixed, so 1 keeps the plural.
Argument separators depend on local settings. The comma examples suit English-US formula syntax. If your sheet expects semicolons, change only argument separators—not commas inside quoted labels. Localized Excel may also require translated function names; Google Sheets has a separate setting for always using English function names. Changing display language or a date’s appearance does not turn text into a valid date value.
Semicolon example: whole years
=DATEDIF(A2;B2;"Y")Semicolon example: combined age
=QUOTIENT(DATEDIF(A2;B2;"M");12)&" years, "&MOD(DATEDIF(A2;B2;"M");12)&" months, "&(B2-EDATE(A2;DATEDIF(A2;B2;"M")))&" days"Common errors and date inputs
- Ambiguous dates: 01/02/2026 may mean January 2 or February 1. Use DATE(year,month,day) or verify your imported date values before calculating.
- Dates stored as text: changing the cell’s display format alone does not convert text to a date serial. ISNUMBER(A2) helps check whether a cell is numeric, although it does not prove that it represents the intended date.
- Reference before birth: DATEDIF returns #NUM! if the start is after the end. Fix the input; do not swap the dates silently in an age record.
- Time components: spreadsheet dates can include fractional days. B2-A2 can therefore return a decimal. These examples require date-only inputs; deliberately use INT(A2) and INT(B2) if discarding time is appropriate.
- Results displayed as dates: format numeric age outputs as Number or General. The formula may be correct even when the cell display looks like a date.
- Stale TODAY(): check calculation settings if an age does not update. For historical reports, store the report date in B2 rather than a moving TODAY() reference.
For actual elapsed months rather than a decimal estimate based on 30.44 days, see days old and age in months. To compare date intervals manually, the manual age-calculation guide walks through calendar steps without spreadsheet syntax.
Using age formulas in a worksheet
For a list of people, put birth dates in column A and a common report date in B1. Replace B2 in the chosen formula with $B$1 before filling down: the birth-date row changes, but the reference remains fixed. If each person has a different assessment date, keep B2 relative so both row references advance together. Test the first and last rows after copying and include at least one month-end and one leap-day birth in your checks.
HR reports may need whole years; infant records may need completed months; a milestone list may need total days. Choose the output that matches the purpose rather than treating these quantities as interchangeable. Our guide to why exact age matters describes practical distinctions. For legal, insurance or clinical thresholds, follow the applicable rules or test manual; neither a spreadsheet nor this website establishes those requirements.
Excel and Google Sheets can calculate correct ages and scale well to many rows. A dedicated calculator is useful for a quick single-person check without maintaining a workbook—not automatically more accurate. Matching both tools requires matching their date and anniversary conventions. For a day-count milestone rather than a calendar age, try the 10,000-day milestone calculator.
Check an age without a spreadsheet
This age check reuses the site’s existing age engine. Enter the same A2 and B2 dates to compare them. Remember: its clamped month-end and March 1 leap-birthday conventions can differ from the spreadsheet decomposition above. Your dates and results stay in your browser; no spreadsheet account is required.
You can also use the main age calculator, check age on a specific date, or read the birthday countdown and milestones guide. These are alternatives for individual checks, not a reason to abandon a correctly implemented spreadsheet.
Sources and verification
Reviewed against the official documentation on 18 October 2026. The copyable formulas and eight fixed date examples are covered by executable tests in HyperFormula 3.5.0, an independent spreadsheet evaluator. They have not been executed in hosted Excel or Google Sheets during this review; no particular “MD” output from an untested Excel version is claimed. The recommended MD-free composition is tested independently and its boundary convention is shown explicitly.