Does DATEDIF work when the birth date is later than the reference date?

DATEDIF returns a #NUM! error when the start date is after the end date. Wrap the formula in an IF check or reorder the arguments before you build the sheet, especially for "age at death" style questions.

What does the YM argument actually return inside DATEDIF?

YM returns the number of full months between the most recent anniversary and the reference date, after the full years have been subtracted. MD then returns the leftover days after those months. Together they describe the gap inside the most recent year.

Can Excel calculate age for dates before 1900?

The default Windows date system starts at January 1, 1900, with a known leap-year quirk for January and February 1900. Birth dates before that need the 1904 date system on Mac or a custom conversion. Most age calculations on modern records do not hit this limit.

Is there a way to convert age to a number with decimals in Excel?

Yes. YEARFRAC with basis 1 (actual/actual) returns fractional years, and multiplying by 12 turns the result into months. DATEDIF with "M" divided by 12 gives a coarser decimal-year figure. Neither replaces a clean years-months-days string for forms that ask "how old are you today."

Why does the Western age differ from East Asian nominal age?

Western count starts at zero on the day of birth and ticks up at each birthday. East Asian nominal age (虚岁) starts at one on the day of birth and gains another year at every Lunar New Year, so the two systems can differ by one to two years depending on the date you check.

How to Calculate Age from Date of Birth in Excel

In Excel, age from a date of birth is computed with the DATEDIF function: =DATEDIF(birthdate, TODAY(), "Y") returns the number of complete years between two dates, and stacking three DATEDIF calls — one for "Y", one for "YM", one for "MD" — gives the same years-months-days breakdown that the official Age Calculator shows in a single screen. To use DATEDIF, store each date of birth as a real Excel date (not text) in column A, then enter =DATEDIF(A2, TODAY(), "Y") for completed years only, or the stacked version =DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months, " & DATEDIF(A2, TODAY(), "MD") & " days" for a readable string. For an answer that needs no spreadsheet at all, type your date of birth into the Age Calculator and read it off instantly, with totals in months, weeks, and days already worked out for any past or future reference date.

What "age in years, months, and days" actually means

An age in years, months, and days is the gap between two dates — a date of birth and a reference date — counted one calendar unit at a time. You walk forward from the birth date, adding whole years until one more year would pass the reference date, then whole months, then count the leftover days. The order matters. Counting whole years first prevents the negative-day problems that come from subtracting the day numbers directly, especially across months of different lengths.

For example, the gap from January 31 to March 1 is 1 month and 1 day: January 31 plus one month lands on February 28 in a non-leap year, and one more day reaches March 1. Naive day subtraction would return "1 month minus 30 days," which is not a meaningful age. Stepping forward, then, is the only reliable algorithm, and it is the same approach that trusted date libraries such as date-fns use.

How to calculate age from DOB in Excel

Excel ships with several built-in ways to get age from a date of birth, and each one answers a slightly different question. The table below compares them so you can pick the right method before you start typing.

Method	What it returns	Best for	Main drawback
=DATEDIF(A2, TODAY(), "Y")	Complete years only	Most age checks, IDs, forms	Loses the months and days
Stacked DATEDIF "Y", "YM", "MD"	Years, months, days as text	Babies, anniversaries, milestones	Long, easy to mis-type
=YEARFRAC(A2, TODAY(), 1)	Fractional years, actual/actual	Finance, tax, insurance proration	Not calendar-friendly
=INT((TODAY() - A2) / 365.25)	Approximate years	Rough estimates over long spans	Drifts within a year
Age Calculator	Years + months + days, totals, next birthday	Any single age lookup	Browser required

Follow these steps to put an age formula in your own sheet.

Place each date of birth in column A as a real Excel date. Forms like "15-Mar-1990" or "1990/03/15" both work as long as Excel parses them into a serial number.
Confirm the cell is formatted as Date, not Text. Right-click, choose Format Cells, then pick Date. A text-typed date produces #VALUE! errors in every formula below.
In the cell where you want age in completed years, enter =DATEDIF(A2, TODAY(), "Y") where A2 holds the date of birth. Press Enter and drag the fill handle down to copy the formula across every row.
For a richer years-months-days string, replace the formula with =DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months, " & DATEDIF(A2, TODAY(), "MD") & " days" and press Enter.
To freeze the date of calculation instead of using TODAY(), replace every TODAY() with a cell reference such as B2 — a single "as of" date you update in one place to refresh every row.
To change units, swap the third argument of DATEDIF: "M" returns total months between the dates, "D" returns total days, "YM" returns months past the last anniversary, "MD" returns days past the last full month.

Once the formula works on one row, fill it down. Excel recalculates DATEDIF every time the workbook opens, so every age stays current without any extra work on your part.

Why Excel age formulas break on tricky birthdays

Most "wrong age" errors in spreadsheets come from one of three places: short months, leap-year birthdays, and subtracting across calendar boundaries.

End-of-month dates. Subtracting day numbers directly can return a negative day when the birth day is 31 and the reference month is shorter. DATEDIF avoids this because it borrows — when the MD count would go negative, the function reduces the month count by one and adds back that calendar month's real length, whether 28, 29, 30, or 31 days.

February 29 birthdays. Leap years follow the Gregorian rule: a year is a leap year if divisible by 4, except century years, which must also be divisible by 400 — so 2000 was a leap year, while 1900 and 2100 were not. February has 29 days only in those leap years, so a person born on February 29 has a true birthday only once every four years. Most age libraries, including the one in the Age Calculator, observe the birthday on February 28 in common years, the convention used across much of Europe and in many legal systems, so the person still gains a full year each year.

Off-by-one in the YYYY minus YYYY shortcut. =YEAR(TODAY()) - YEAR(A2) returns the wrong answer until the current year's birthday has actually passed. Use DATEDIF instead and you never have to think about which side of the anniversary the calendar sits on.

INT((TODAY() - DOB) / 365.25) is approximate. The 365.25-day year is fine for long spans but drifts by a day or two within a single year. Treat it as a rough figure, not a precise legal age.

Get your exact age in one click

For anyone who would rather skip the formula entirely, the Age Calculator returns the same years-months-days breakdown along with totals in weeks, months, and days, plus a live countdown to your next birthday. Everything runs in your browser, no date ever leaves your device, and the result updates the instant you change a field.

To use it:

Open the Age Calculator and pick your date of birth in the first field. Your exact age appears immediately — there is no button to press.
Leave the second date on today, or set it to any past or future reference date to find how old you were — or will be — on that specific day.
Read your age in years, months, and days, plus totals in months, weeks, and days, and the countdown to your next birthday.

You can also point it at a milestone date to see how old you will be at retirement, on a wedding day, or at the start of a school year — without typing any formula.

Worked example: walking the algorithm forward

To see the forward walk in action, take a date of birth of March 15, 1990 with a reference date of June 6, 2026.

Step 1 — full years. From March 15, 1990, count whole years forward. Each year lands on March 15 of the next calendar year. Thirty-six jumps land on March 15, 2026, which is before June 6, 2026, so those 36 years count. A 37th year would land on March 15, 2027, past the reference date, and does not count. Full years = 36.

Step 2 — full months. From March 15, 2026, count months forward. One month lands on April 15, two months on May 15. A third month would land on June 15, past June 6, so full months = 2.

Step 3 — remaining days. From May 15, 2026 to June 6, 2026: 16 days finish out May (31 − 15 = 16), then 6 days into June. Remaining days = 22.

The exact age on June 6, 2026 is 36 years, 2 months, and 22 days. The same forward-walk algorithm is what the Age Calculator runs in your browser. To see your totals expressed as whole months, whole weeks, or total days across every leap year in between, use the tool — counting calendar days through several Februarys is exactly the kind of arithmetic that goes wrong if done by hand.