Google Sheets 根据出生日期计算年龄：年、月、日

Tested in: Live formula checks in Microsoft Excel 16.114.4 for Mac (English UI; system region zh-Hans_US), November 8, 2026; Google Sheets (spreadsheet locale zh_CN), November 9, 2026. Results and platform differences are noted below; this is not a certification of every menu workflow.

Which versions this applies to

Google Sheets on the web and mobile. DATEDIF also exists in Excel, where Microsoft lists it as kept for compatibility with older Lotus 1-2-3 workbooks and warns about the “MD” unit. See the edge-case section.

Use a fixed “as of” date while you build

TODAY() changes every day, so a sheet built with it gives different answers tomorrow. That’s what you want for a live roster, but it makes checking formulas hard. Put the reference date in a cell instead:

• B1: 2026-11-04 (or =TODAY() once you’re done testing)

All examples below use $B$1 and return the results shown for November 4, 2026. A fixed date is also the right choice for reports, for example “age at the start of the program” or “age on the policy date”.

Example data

Dates of birth in column A, real dates (not text):

Step 1: Age in whole years

In B3:

=DATEDIF(A3, $B$1, "Y")

Google’s DATEDIF documentation describes “Y” as “the number of whole years between start_date and end_date”. A person only gets a year older when the birthday is actually reached: row 6 returns 15, not 16, because the birthday is tomorrow.

Google also notes that months and years “are only counted if they are equal to or go past the day”. That rule is what makes the edge cases below behave the way they do.

Step 2: Years, months and days

The three units work together:

Combined into one cell:

=DATEDIF(A3, $B$1, "Y")&" years, "&DATEDIF(A3, $B$1, "YM")&" months, "&DATEDIF(A3, $B$1, "MD")&" days"

Row 3 returns 36 years, 5 months, 20 days. For a compact label such as 36y 5m 20d, swap the text pieces: &"y "& … &"m "& … &"d".

Keep the numeric DATEDIF results in their own columns if you’ll filter, sort or chart by age. A joined text label can’t be sorted by age.

Step 3: Other ages you’ll need

Age in months (for babies, or for a “months of service” column). Row 3 returns 437:

=DATEDIF(A3, $B$1, "M")

Age in days (just subtract; row 3 returns 13322):

=$B$1 - A3

Age on a specific date, for example a school cutoff on September 1, 2026:

=DATEDIF(A3, DATE(2026, 9, 1), "Y")

A child born on 2015-09-02 returns 10 here, because the 11th birthday is one day after the cutoff.

Next birthday and days until it. EDATE adds whole months to the birth date:

=EDATE(A3, 12*(DATEDIF(A3, $B$1, "Y")+1))

=EDATE(A3, 12*(DATEDIF(A3, $B$1, "Y")+1)) - $B$1

Row 3: next birthday 2027-05-15, 192 days away. Row 6 returns 1 day. Format the first result as a date and the second as a number. On the birthday itself (row 5), the “next” birthday is a year away (365 days). If you’d rather show “Today”, wrap it: =IF(DATEDIF(A5,$B$1,"YD")=0, "Today", …).

Age group (with the age in years in B3):

=IFS(B3<18, "Under 18", B3<35, "18–34", B3<55, "35–54", TRUE, "55+")

Step 4: One formula for the whole column, with guards

A blank date of birth counts as day zero (December 30, 1899), which is why row 8 shows 126 years. A birth date after the as-of date can’t produce an age. Guard both, and fill the whole column at once:

=ARRAYFORMULA(IF((A3:A="")+(A3:A>$B$1), "", DATEDIF(A3:A, $B$1, "Y")))

(A3:A="")+(A3:A>$B$1) is an OR test: blank or in the future. Keep the cells below the formula empty, or the result can’t expand. See Fix “Array Result Was Not Expanded Because It Would Overwrite Data” in Google Sheets.

Edge cases that change the answer

Leap-day birthdays (February 29)

In a non-leap year there’s no February 29, so when does the person turn a year older? DATEDIF counts the year only when the day is “reached”, and that happens on March 1:

EDATE handles the same date differently: it moves to the last day of the month, so the next-birthday formula returns 2027-02-28. If you show both on one sheet, they disagree for one day. Decide which date counts for your purpose. Legal rules for leap-day birthdays vary, so follow the one that applies to you.

Born on the 29th, 30th or 31st: “MD” can go wrong

For someone born on 1992-01-31, measured on 2026-03-01:

• "YM" returns 1 (February counts as one month).
• "MD" returned −2 in LibreOffice, because February has no 31st.

Microsoft’s DATEDIF page warns that “MD” “may result in a negative number, a zero, or an inaccurate result”, and recommends not using it. A safer way to get the leftover days is to subtract the date reached after the whole months:

=$B$1 - EDATE(A3, DATEDIF(A3, $B$1, "M"))

For the 1992-01-31 case this returns 1: EDATE gives 2026-02-28 (the last day of February), and March 1 is one day later. For all the rows in the sample table it gives the same result as “MD”. Here’s the full label with this safer days part:

=DATEDIF(A3, $B$1, "Y")&" years, "&DATEDIF(A3, $B$1, "YM")&" months, "&($B$1 - EDATE(A3, DATEDIF(A3, $B$1, "M")))&" days"

Dates of birth stored as text

Google’s documentation says DATEDIF’s start date must be a date, a function that returns a date, or a number. Text that only looks like a date (left-aligned, or imported from a CSV) gives an error. Check with =ISNUMBER(A3). Convert with DATEVALUE(A3) if Sheets understands the format, or with DATE and LEFT/MID/RIGHT if it doesn’t. The full walk-through, with day-first and yyyymmdd examples, is in Excel Dates Stored as Text: 4 Ways to Convert Them to Real Dates. The formulas work in Sheets too.

DATEDIF vs. YEARFRAC

You’ll also see =INT(YEARFRAC(A3, $B$1, 1)) suggested for ages. YEARFRAC returns a fraction of a year based on a day-count convention. DATEDIF counts whole calendar years, which is how ages are normally stated. The two agreed for every row in the sample table, but they’re different calculations, so use DATEDIF when an exact birthday boundary matters.

Common mistakes

1. Dates in the wrong order. DATEDIF needs the birth date first and the later date second. Swapped, it returns an error.
2. Result cell formatted as a date. Google’s documentation shows a DATEDIF result appearing as 1/4/1900 for this reason. Set the cell to Number.
3. Dividing by 365. Leap days add up, so =INT(($B$1-A3)/365) adds a year too early. Someone born on 2000-11-10 is still 25 on 2026-11-04, but 9490 days ÷ 365 = 26.0. Use DATEDIF.
4. No guard for blank rows. Empty birth dates show ages of 120+. Wrap the formula in IF(A3="", "", …).
5. Relying on “MD” for people born late in the month. Use the EDATE-based days formula.

FAQ

How do I calculate age in Google Sheets from a date of birth?

Use =DATEDIF(A2, TODAY(), “Y”), where A2 holds the date of birth. It returns the number of complete years.

How do I show age in years, months and days?

Use DATEDIF three times with the units “Y”, “YM” and “MD”, joined with &. For people born on the 29th to the 31st, calculate the days as TODAY() - EDATE(A2, DATEDIF(A2, TODAY(), “M”)) instead of “MD”.

Why does my age formula show a date from 1900?

The cell is formatted as a date, so the number is displayed as a date. Change the format to Number with Format > Number > Number.

How do I calculate age on a specific date instead of today?

Replace TODAY() with that date, for example =DATEDIF(A2, DATE(2026, 9, 1), “Y”), or point it at a cell that holds the date.

How does DATEDIF handle February 29 birthdays?

In non-leap years, DATEDIF counts the new year of age from March 1, because February 29 doesn’t exist. EDATE moves the birthday to February 28 instead, so decide which date counts for your purpose.

Related tutorials

• Excel Dates Stored as Text: 4 Ways to Convert Them to Real Dates
• How to Sum Time Over 24 Hours in Google Sheets (Duration Format Fix)

Live-test findings

With the article’s fixed as-of date 2026-11-04, a blank birthday produced residual days 5 in Sheets (Excel returned 4). This is a date-zero difference, not a valid age: keep the blank guard. DATEDIF(DATE(1992,1,31),DATE(2026,3,1),"MD") returned -2 in Sheets, while the EDATE-based residual-day calculation returned 1. The leap-day examples also passed; do not use MD alone for reliable month-end residual days.

Sources

• Google Docs Editors Help: DATEDIF (units Y, M, D, MD, YM, YD; months and years counted only when the day is reached; date-formatted result shows as 1/4/1900): https://support.google.com/docs/answer/6055612
• Google Docs Editors Help: EDATE (adds whole months to a date): https://support.google.com/docs/answer/3092974
• Microsoft Support: DATEDIF function (kept for Lotus 1-2-3 compatibility; “MD” known issues; #NUM! when start is after end): https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c