What is the DATEDIF formula for age in Excel?

=DATEDIF(birthday_cell, TODAY(), "Y") gives age in full years. For a complete breakdown: Years = DATEDIF(A1,TODAY(),"Y"), Months = DATEDIF(A1,TODAY(),"YM"), Days = DATEDIF(A1,TODAY(),"MD"). Combine them: =DATEDIF(A1,TODAY(),"Y")&" years, "&DATEDIF(A1,TODAY(),"YM")&" months, "&DATEDIF(A1,TODAY(),"MD")&" days"

Is DATEDIF reliable in Excel?

DATEDIF works correctly for most dates but has known bugs. The "MD" parameter (remaining days after months) can return negative numbers or wrong values when the birth day is 29, 30, or 31 and the current month is shorter (e.g., February). Microsoft does not officially document or support DATEDIF — it is a legacy function inherited from Lotus 1-2-3.

Does DATEDIF work in Google Sheets?

Yes. Google Sheets supports DATEDIF with the same syntax. It handles the "MD" parameter slightly differently than Excel in some edge cases but is generally reliable. Google Sheets also has DAYS360 and other date functions as alternatives.

What is YEARFRAC for age calculation?

=YEARFRAC(birthday, TODAY()) returns age as a decimal number. For example, someone 30 years and 6 months old shows as approximately 30.5. This is useful for financial calculations where you need age as a single number, but it does not give you the months-and-days breakdown most people want.

When should I use Excel vs an online calculator?

Use Excel when you need to calculate ages for a list of people (employees, students, members) — the formula applies to the whole column. Use an online calculator for a single quick lookup. Excel is a tool for batch processing; an online calculator is a tool for instant answers.

Age Calculator in Excel — DATEDIF Formula vs Free Online Tool

Q: Can I calculate age without DATEDIF in Excel?

Yes. =INT((TODAY()-A1)/365.25) gives approximate age in years. For exact years: =YEAR(TODAY())-YEAR(A1)-IF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))>TODAY(),1,0). These avoid DATEDIF bugs but are harder to read and maintain.

Last updated: April 20267 min readCalculator Tools

Excel can calculate age using DATEDIF(birthday, TODAY(), "Y") — but the function is undocumented, has known bugs with month-end dates, and requires you to open a spreadsheet. For a single age calculation, a browser-based age calculator gives the same answer in 2 seconds.

Excel DATEDIF Formula — The Full Reference

Formula	Returns	Example (Born Mar 20, 1990)
=DATEDIF(A1, TODAY(), "Y")	Full years	36
=DATEDIF(A1, TODAY(), "M")	Total months	432
=DATEDIF(A1, TODAY(), "D")	Total days	13,164
=DATEDIF(A1, TODAY(), "YM")	Months after years	0
=DATEDIF(A1, TODAY(), "MD")	Days after months	15
=DATEDIF(A1, TODAY(), "YD")	Days after years	15

To combine into a single cell: =DATEDIF(A1,TODAY(),"Y")&" years, "&DATEDIF(A1,TODAY(),"YM")&" months, "&DATEDIF(A1,TODAY(),"MD")&" days"

The Problem with DATEDIF

DATEDIF is inherited from Lotus 1-2-3 (a 1983 spreadsheet program). Microsoft kept it for compatibility but never documented it in Excel help. Known issues:

"MD" bug: When the birth day is the 31st and the current month has fewer days (February, April, June, September, November), the "MD" result can be negative or wrong. Example: born January 31, calculating on March 1 — DATEDIF may return -2 days instead of 1 day.
No autocomplete: Excel does not suggest DATEDIF when you type. You must know the exact function name and syntax.
No documentation: You cannot access DATEDIF help within Excel. Google is the only way to find the syntax.

Excel vs Online Calculator — When to Use Each

Scenario	Excel	Online Calculator	Winner
Single quick age lookup	✗ Open Excel, type formula	✓ Open page, enter birthday	Online
Calculate ages for 500 employees	✓ Formula applies to entire column	✗ One at a time	Excel
Need years + months + days	~Works but DATEDIF has bugs	✓ Handles edge cases correctly	Online
Age on a specific date (not today)	✓ Replace TODAY() with any date	✓ Built-in target date field	Tie
No Excel/Sheets license	✗ Need software	✓ Works in any browser	Online
Audit trail for HR/legal	✓ Formula in spreadsheet is auditable	✗ No saved record	Excel
Phone or tablet	✗ Mobile Excel is clunky	✓ Works on any device	Online

For a single age calculation, the browser tool is faster than opening Excel.

Open Age Calculator

Google Sheets Alternative

Google Sheets supports the same DATEDIF syntax and is free. If you work in Google Workspace, Sheets is often the better choice over Excel for age calculations:

Free with a Google account (no Microsoft license needed)
DATEDIF works identically: =DATEDIF(A1, TODAY(), "Y")
The "MD" bug is less common in Sheets but still exists for some month-end dates
Sheets auto-updates TODAY() in real time — Excel only updates when you open the file

Alternatives to DATEDIF (If You Avoid the Bug)

If you do not trust DATEDIF, here are two DATEDIF-free formulas:

Age in years (exact):

=YEAR(TODAY())-YEAR(A1)-IF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))>TODAY(),1,0)

This checks whether your birthday has occurred yet this year and subtracts 1 if not.

Age as decimal:

=YEARFRAC(A1, TODAY())

Returns a decimal like 36.04 — useful for financial formulas but not for human-readable "years, months, days" output.

Related Tools and Guides

Age Calculator — instant exact age, no formula needed
Date Calculator — days between any two dates
Percentage Calculator — percentage math without Excel
Compound Interest Calculator — financial projections by age
Excel Viewer — view .xlsx files in your browser
Excel to CSV — extract birthday columns for batch processing
Column Operations — process birthday columns mathematically

One birthday. One click. Exact age — no formula, no bugs.

Open Age Calculator

Age Calculator in Excel — DATEDIF Formula vs Free Online Tool

Excel DATEDIF Formula — The Full Reference

The Problem with DATEDIF

Excel vs Online Calculator — When to Use Each

Google Sheets Alternative

Alternatives to DATEDIF (If You Avoid the Bug)

Related Tools and Guides

Related Posts

How to Calculate Exact Age

How Old Am I? Age Calculator

Percentage Calculator vs Excel

Loan Calculator vs Excel