How to Calculate Age in Excel and SQL

Tested formulas for completed years, exact years/months/days and elapsed days — with the leap-day and month-end rule each one uses. SQL syntax differs between databases, so each one has its own example.

Excel Age Formulas

Put the birth date in A2 and the as-of date in B2 (or TODAY()). Cells must contain real dates, not text.

Completed years (DATEDIF "Y")

=IF(A2>B2,"Birth date after as-of date",DATEDIF(A2,B2,"Y"))

A2 = birth date, B2 = as-of date (use TODAY() for today). Counts whole years only. Feb 29: DATEDIF waits until the day of month is reached, so a Feb 29 birthday gains the next year on March 1 in common years. DATEDIF returns #NUM! if the start is after the end, hence the check.

Worked examples
Inputs	Result
31 Dec 2000 → 1 Oct 2026	25
15 Jun 2000 → 14 Jun 2020 (day before birthday)	19
29 Feb 2000 → 28 Feb 2023	22 (reaches 23 on 1 Mar 2023)

Tested: LibreOffice Calc recalculation.

Years, months and days (explicit EDATE convention)

C2 (total completed months):
=(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)-(EDATE(A2,(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2))>B2)

Years:  =INT(C2/12)
Months: =MOD(C2,12)
Days:   =B2-EDATE(A2,C2)

A reliable replacement for DATEDIF(…,"MD"). EDATE moves to the last day of a shorter month, so the convention is: Jan 31 + 1 month = Feb 28/29, and a Feb 29 birthday is reached on Feb 28 in common years. Days are never negative. Format the Days cell as a number, not a date. This matches the convention used by our Chronological Age Calculator.

Worked examples
Inputs	Result
31 Dec 2000 → 1 Oct 2026	C2 = 309 → 25 years, 9 months, 1 day
31 Jan 1990 → 1 Mar 2024	C2 = 409 → 34 years, 1 month, 1 day
29 Feb 2000 → 28 Feb 2023	C2 = 276 → 23 years, 0 months, 0 days

Tested: LibreOffice Calc recalculation.

Total elapsed days

=B2-A2

Dates are serial numbers, so subtraction gives exact elapsed days (format the result as a number). This is unambiguous, unlike month breakdowns.

Worked examples
Inputs	Result
31 Dec 2000 → 1 Oct 2026	9,405 days
29 Feb 2000 → 28 Feb 2023	8,400 days

Tested: LibreOffice Calc recalculation.

Approximate decimal years (not completed age)

=YEARFRAC(A2,B2,1)

Basis 1 uses actual day counts, but the result is still an approximation for charts and averages. Do not round it to get completed age: on an exact 24th birthday it can return slightly less than 24. Dividing days by 365.25 has the same problem.

Worked examples
Inputs	Result
31 Dec 2000 → 1 Oct 2026	≈ 25.7488
29 Feb 2000 → 29 Feb 2024 (exact 24th birthday)	≈ 23.9980, not 24

Tested: LibreOffice Calc recalculation (Excel may differ in the last decimals).

Why not DATEDIF "MD"?

=DATEDIF(A2,B2,"MD")

Microsoft’s own documentation warns that the "MD" argument “may result in a negative number, a zero, or an inaccurate result”. It is shown only for comparison — use the EDATE breakdown above instead.

Worked examples
Inputs	Result
31 Jan 1990 → 1 Mar 2024	−1 in LibreOffice (correct remaining days: 1)
15 Jun 2000 → 14 Jun 2020	30 (EDATE method also gives 30)

Tested: LibreOffice Calc recalculation; Microsoft Excel itself was not run.

Dates Typed as DD/MM/YYYY

Whether Excel reads 03/04/2024 as 3 April or March 4 depends on your system’s regional settings. Converting text explicitly avoids that guess.

Parse DD/MM/YYYY text safely

=IFERROR(IF(AND(LEN(A2)=10,MID(A2,3,1)="/",MID(A2,6,1)="/",DAY(DATE(--RIGHT(A2,4),--MID(A2,4,2),--LEFT(A2,2)))=--LEFT(A2,2),MONTH(DATE(--RIGHT(A2,4),--MID(A2,4,2),--LEFT(A2,2)))=--MID(A2,4,2)),DATE(--RIGHT(A2,4),--MID(A2,4,2),--LEFT(A2,2)),"Invalid"),"Invalid")

Works the same in any locale because it never relies on DATEVALUE. A plain DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)) silently rolls impossible dates forward (31/04/2024 becomes 1 May 2024); the DAY/MONTH check rejects them. Requires zero-padded text such as 01/02/2024.

Worked examples
Inputs	Result
31/12/2000	31 Dec 2000
29/02/2024	29 Feb 2024
29/02/2023 or 31/04/2024	Invalid
1/2/2024 or 2024-02-29	Invalid (not zero-padded DD/MM/YYYY)

Tested: LibreOffice Calc recalculation.

Common Spreadsheet Errors

Result	Usual cause	Fix
#VALUE!	The cell holds text that looks like a date, or DATEVALUE could not read it in your locale.	Use the DD/MM/YYYY parsing formula or Data → Text to Columns → Date: DMY.
#NUM!	DATEDIF start date is after the end date (e.g. a future birth date).	Check A2<=B2 first, as in the formulas above.
#NAME?	Function name misspelled, or DATEDIF typed in an app that does not support it.	Check spelling; DATEDIF does not appear in Excel’s autocomplete but still works.
Wrong but no error	DATE() rolled an impossible day forward (31/04 → 1 May), or a day count shows as a date like 1900-01-30.	Validate DAY/MONTH after parsing; format result cells as Number.

SQL Age Calculation by Database

There is no single universal SQL age function. These examples are dialect-specific and not interchangeable.

SQL Server (T-SQL) — birthday-adjusted

DECLARE @as_of date = CAST(GETDATE() AS date);

SELECT birth_date,
  CASE WHEN birth_date IS NULL OR birth_date > @as_of THEN NULL
       ELSE DATEDIFF(YEAR, birth_date, @as_of)
          - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, birth_date, @as_of), birth_date) > @as_of
                 THEN 1 ELSE 0 END
  END AS age_years
FROM users;

DATEDIFF(YEAR, …) alone counts year boundaries crossed, so it overstates age before the birthday. The correction subtracts 1 when this year’s birthday is still ahead. Feb 29: DATEADD moves Feb 29 to Feb 28 in common years, so the birthday counts on Feb 28. For a March 1 rule, replace the inner CASE with CASE WHEN MONTH(@as_of)*100+DAY(@as_of) < MONTH(birth_date)*100+DAY(birth_date) THEN 1 ELSE 0 END. Future and NULL birth dates return NULL.

Worked examples
Inputs	Result
31 Dec 2000 → 1 Oct 2026	25 (DATEDIFF alone: 26)
31 Dec 2000 → 1 Jan 2001	0 (DATEDIFF alone: 1)
29 Feb 2000 → 28 Feb 2023	23 (Feb 28 rule) / 22 with the March 1 variant

Tested: Not run on SQL Server. Logic checked in Python against Microsoft’s documented DATEDIFF (boundary count) and DATEADD (month-end) behaviour.

MySQL 8

SELECT birth_date,
  CASE WHEN birth_date IS NULL OR birth_date > CURDATE() THEN NULL
       ELSE TIMESTAMPDIFF(YEAR, birth_date, CURDATE())
  END AS age_years
FROM users;

TIMESTAMPDIFF(YEAR, …) returns completed years. Feb 29: the year is gained on March 1 in common years. Without the guard a future birth date returns a negative number. For a Feb 28 rule use (CAST(YEAR(CURDATE()) AS SIGNED) - YEAR(birth_date)) - (DATE_ADD(birth_date, INTERVAL (CAST(YEAR(CURDATE()) AS SIGNED) - YEAR(birth_date)) YEAR) > CURDATE()) inside the same guard (the CAST avoids an unsigned-overflow error). Parse text with STR_TO_DATE(s, '%d/%m/%Y'), which returned NULL for 31/02/2024.

Worked examples
Inputs	Result
31 Dec 2000 → 1 Oct 2026	25
29 Feb 2000 → 28 Feb 2023	22 (Feb 28 variant: 23)
1 Jan 2030 → 1 Jan 2026 (future)	NULL with the guard (−4 without it)

Tested: Run on MySQL 8.4.8.

PostgreSQL

SELECT birth_date,
  CASE WHEN birth_date IS NULL OR birth_date > current_date THEN NULL
       ELSE EXTRACT(YEAR FROM age(current_date, birth_date))::int
  END AS age_years
FROM users;

age() returns a years/months/days interval; EXTRACT takes the completed years. Feb 29: the year is gained on March 1 in common years, and month ends are not clamped (31 Jan → 28 Feb is 28 days, 0 months). For a Feb 28 rule: (EXTRACT(YEAR FROM a)::int - EXTRACT(YEAR FROM b)::int) - CASE WHEN b + make_interval(years => EXTRACT(YEAR FROM a)::int - EXTRACT(YEAR FROM b)::int) > a THEN 1 ELSE 0 END. to_date('31/02/2024','DD/MM/YYYY') raises “date/time field value out of range”.

Worked examples
Inputs	Result
31 Dec 2000 → 1 Oct 2026	25 (age = 25 years 9 mons 1 day)
29 Feb 2000 → 28 Feb 2023	22 (Feb 28 variant: 23)

Tested: Run on PostgreSQL 17.11.

Dates, Datetimes and Time Zones

Store birth dates as DATE. A datetime or timestamp at midnight can move to the previous or next day when converted between time zones.
“Today” depends on whose clock. GETDATE() uses the SQL Server machine’s local time; MySQL CURDATE() uses the session time_zone; PostgreSQL current_date uses the session TimeZone. Near midnight a user elsewhere may already be on a different date — pass the user’s local date as a parameter when it matters, e.g. in SQL Server CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Pakistan Standard Time' AS date).
Excel has no time zones. TODAY() is the computer’s local date; a date-time value is truncated with INT(A2).
Invalid text: SQL Server TRY_CONVERT(date, '31/12/2000', 103) returns NULL instead of failing; MySQL STR_TO_DATE returns NULL; PostgreSQL to_date raises an error.

Sample Workbook

Contains every Excel formula above with the test dates (including 31 Dec 2000 → 1 Oct 2026, Feb 29 and Jan 31 cases), the input checks and the DD/MM/YYYY parser. Recalculated in LibreOffice with zero formula errors; edit the blue cells to try your own dates.

Download sample workbook (.xlsx, 11 KB)

Frequently Asked Questions

Sources

Prefer an Online Calculator?

Our calculator uses the same Feb 28 / month-end rule as the EDATE formula.

Try Age Calculator

Excel Age Formulas

Completed years (DATEDIF "Y")

Years, months and days (explicit EDATE convention)

Total elapsed days

Approximate decimal years (not completed age)

Why not DATEDIF "MD"?

Dates Typed as DD/MM/YYYY

Parse DD/MM/YYYY text safely

Common Spreadsheet Errors

SQL Age Calculation by Database

SQL Server (T-SQL) — birthday-adjusted

MySQL 8

PostgreSQL

Dates, Datetimes and Time Zones

Sample Workbook

Frequently Asked Questions

Why does DATEDIF give a different Feb 29 result than the EDATE formula?

Should I use DATE or DATETIME columns for birth dates in SQL?

How do I handle empty or future birth dates?

Is (TODAY()-A1)/365.25 accurate?

Sources

Prefer an Online Calculator?