← Guides

How to Calculate Age from a Date of Birth (Excel, Sheets and SQL)

Updated: 2026-09-29

Age is two dates plus a calendar, and almost every wrong age in a spreadsheet came from treating it as a division. One Excel formula answers the head version of this question, but the variants behind it — years and months, a specific reference date, no DATEDIF, Google Sheets, SQL Server — each need a different answer, and the SQL Server one is systematically wrong until you correct it. Every formula below is written exactly as you type it, and the approximations are labelled as approximations.

Tool

Age Calculator

Calculate age from date of birth

Runs entirely in your browser — your birthdate and age are never transmitted anywhere.

Tool

Date Difference Calculator

Calculate age from date of birth

Runs entirely in your browser — your birthdate and age are never transmitted anywhere.

The short answer

If A2 holds the birth date, then =DATEDIF(A2,TODAY(),"Y") returns the exact age in completed years. It is the same formula in Excel and Google Sheets, it counts real calendar years rather than an average year length, and it subtracts the not-yet-arrived birthday for you. It is the only age formula on this page you should trust for a person's actual age today.

Everything else here is a variant of it. Three DATEDIF calls with "Y", "YM" and "MD" spell the age out as years, months and days. Replacing TODAY() with a cell reference gives the age on a specific date. A YEAR-based formula does the job where DATEDIF is undocumented or missing. In SQL Server, DATEDIFF(year, ...) is not an age at all until corrected.

Advertisement

Why age is not a division

Subtracting two dates gives a day count, which is the only exact thing a birth date yields. Any age in years derived from that count is a judgement about the length of a year, and the judgement is where the errors live.

The tempting derivation is =INT((TODAY()-A2)/365.25). The constant exists to absorb leap years: a four-year block containing one leap day spans 365 x 4 + 1 days, which is 1461, and 1461 divided by 4 is exactly 365.25. That is the Julian year, and it is why the formula looks reasonable enough to be everywhere.

The calendar in use is not Julian. The Gregorian rule drops the leap day in century years that are not divisible by 400, so 1900 was not a leap year and 2100 will not be, while 2000 was. Across 400 years that is 97 leap days instead of 100, so the mean Gregorian year is 365 + 97/400 days, which is 365.2425. Multiply 365.25 by 400 and you get 146,100 days; the calendar gives 146,097.

The divisor is a little too large, so the estimate runs behind the true age, and when it falls a whole unit behind, the truncated integer is one year short. Someone born on 28 February 1900 had lived 36,524 days on her hundredth birthday, because those years contain 24 leap days rather than 25. 36,524 divided by 365.25 is 99.997, so INT prints 99 for a hundred-year-old.

Dividing by 365 instead of 365.25 fails in the opposite direction, because 365 is shorter than a real year. =INT((TODAY()-A2)/365) reports the next birthday as already reached for as many days before it as the person has accumulated leap days — roughly the final 10 days of each year of age for a 40-year-old whose years span 10 leap days. Neither defect is a rounding artefact, so ROUND cannot repair it. Use a division for population buckets and chart axes, where one year either way changes nothing; use calendar counting when the number describes a person.

Calculate age in Excel

DATEDIF(start_date, end_date, unit) returns the whole number of complete calendar units between two dates. The unit that turns it into an age is "Y": complete years, which takes care of month lengths, leap years and the birthday gate without any logic from you.

Whole years with DATEDIF

Type =DATEDIF(A2,TODAY(),"Y") beside the birth date and format the result as a general number. TODAY() keeps it live: the column recalculates when the workbook opens, so the ages age themselves.

Two failure modes are worth knowing. If the start date is later than the end date — a future birth date, or day and month transposed — DATEDIF returns an error instead of a negative number, which is honest. A blank A2 is worse: Excel reads the empty cell as the start of its date calendar rather than as missing data, so you get a large meaningless year count instead of an empty cell. Guard it as =IF(A2="","",DATEDIF(A2,TODAY(),"Y")).

The other counts fall out of the day difference directly. Total days lived is =TODAY()-A2 formatted as a number. Whole weeks are =INT((TODAY()-A2)/7).

If the birth date is text instead of a date

Subtraction and DATEDIF both need real serial dates. Rows pasted from an export or a CSV often arrive as left-aligned text, and the tell is an error from =A2+0. Convert at the point of use: =DATEDIF(DATEVALUE(A2),TODAY(),"Y").

DATEVALUE reads the text using your regional settings, so 03/04/1990 is 4 March in London and 3 April in New York. If a whole column is off by months, rebuild the date from its pieces with =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)), adjusted to the order you actually have, instead of hoping the parser agrees with you.

Excel age in years and months

The full spelling uses three DATEDIF calls with three units, because each unit answers a different question. "Y" counts complete years since birth, "YM" counts complete months since the last birthday so it is always 0 through 11, and "MD" counts days since the last monthly anniversary:

=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days"

For years and months only — the usual ask for a young child or a service anniversary — drop the third term: =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months". The common mistake is using "M" for the month component. "M" returns total complete months since birth, in the hundreds for an adult, not the remainder.

Total months is still worth knowing as a cross-check: for ordinary dates, =DATEDIF(A2,TODAY(),"M") integer-divided by 12 agrees with "Y", and MOD(DATEDIF(A2,TODAY(),"M"),12) agrees with "YM". When those match your three-part age, the formula is fine and the input date is what surprised you.

One caveat on the day component. "MD" measures against the anniversary day-of-month, and Excel's handling of a start day-of-month later than the end month allows is a long-standing quirk: births on the 29th, 30th or 31st can report an odd trailing day count. The years and months parts are dependable; if the days look wrong, compute them yourself from the shifted anniversary instead of trusting "MD" on that row.

Calculate age in Google Sheets

Sheets implements DATEDIF with the same three arguments and the same unit codes, so every formula on this page pastes into Sheets unchanged. There is no separate Sheets age formula to learn — only environment differences.

The same formulas

Exact age: =DATEDIF(A2,TODAY(),"Y"). Full age: =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days". As at a date in B2: =DATEDIF(A2,B2,"Y"). Without DATEDIF: =YEAR(TODAY())-YEAR(A2)-(TODAY()<DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))).

YEARFRAC, DATE, DATEVALUE and MOD all exist with the same meanings, and TODAY() takes no arguments as in Excel. To stop an age moving — a snapshot other people read — paste the result back over the formulas as values, since nothing makes TODAY() static.

Sheets-specific gotchas

A result cell can inherit a date format and show the age as a day count from the start of the calendar, which looks like broken data and is only formatting. Set Format to Number.

Text that Sheets has not read as a date behaves as in Excel: wrap it in DATEVALUE or build it with DATE(1990,5,15). Sheets decides what is a date from the spreadsheet's own locale, not the viewer's, so test a suspicious column by multiplying it by 1 — a real date yields a five-digit number, text yields an error. And as in Excel, DATEDIF errors when the start date is after the end date, so a future birth date and a swapped day-and-month entry look identical.

Age on a specific date in Excel

Often the question is not today's age but the age at a hire date, a maturity date, a school-year start or a form deadline. Replace TODAY() with the reference date and everything above works unchanged, because DATEDIF never cares where its second date came from.

Using a reference cell

Birth date in A2, as-at date in B2, then =DATEDIF(A2,B2,"Y"). For the full spelling, swap the second argument in all three calls: =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days". Keeping the date in B2 rather than typing it into the formula is what lets one column answer several as-at dates at once.

For a fixed date, build it with DATE so regional settings cannot reinterpret it: =DATEDIF(A2,DATE(2030,6,15),"Y"). Avoid =DATEDIF(A2,"15/06/2030","Y"), because Excel resolves that text against the machine reading the file and the workbook changes behaviour when someone else opens it.

The formula that avoids DATEDIF follows the same substitution, with the reference date twice: =YEAR(B2)-YEAR(A2)-(B2<DATE(YEAR(B2),MONTH(A2),DAY(A2))).

When the two dates are not birth and today

An age is a special case of a span between two dates: birth plus today. When the real question is elapsed time between any two dates — a contract period, a stay, a project — DATEDIF with the earlier date first works, but nothing about it is age-specific. The date difference calculator on this site is the tool built for two arbitrary dates; the age calculator is built for birth to today.

Calculating age without DATEDIF

DATEDIF is unusual: Microsoft never listed it in the function reference, so it has no autocomplete entry and no builder, it survives for spreadsheet compatibility, and some apps do not implement it at all. The portable alternative is a year subtraction with a birthday test:

=YEAR(TODAY())-YEAR(A2)-(TODAY()<DATE(YEAR(TODAY()),MONTH(A2),DAY(A2)))

Three moves. YEAR(TODAY())-YEAR(A2) is the naive answer: the difference in calendar years, correct once the birthday has passed and one too high before it. DATE(YEAR(TODAY()),MONTH(A2),DAY(A2)) rebuilds this year's birthday from the parts of the birth date. Then TODAY()<that date is TRUE while the birthday is ahead, and subtracting TRUE coerces it to 1 while subtracting FALSE coerces it to 0, so the decrement happens exactly in the pre-birthday window and nowhere else.

Two things to respect. The coercion is the mechanism, so in an app that refuses arithmetic on logical values use the verbose form IF(TODAY()<DATE(YEAR(TODAY()),MONTH(A2),DAY(A2)),1,0) in place of the parenthesised test. And for a 29 February birth date, DATE(2023,2,29) normalises to 1 March 2023 because the day overflows a month with no such date, so the gate opens on 1 March in non-leap years. Extending this route to residual months works but gets long; if your app has DATEDIF, use it, and if you want a decimal age, use YEARFRAC below.

YEARFRAC and the decimal age

YEARFRAC(start_date, end_date, basis) returns the fraction of a year between two dates, so =YEARFRAC(A2,TODAY(),1) prints something like 39.42 for a person a little over five months past their thirty-ninth birthday, and INT of it gives whole years.

The basis argument selects a day-count convention, and the conventions are finance conventions rather than calendar ones. Actual/actual measures real days against the average length of the years spanned. Actual/360 and actual/365 divide real days by a fixed nominal year. The 30/360 family pretends every month has 30 days, which disagrees with a real birthday and compounds near boundaries. Name the basis explicitly: =YEARFRAC(A2,TODAY(),1). Do not rely on an omitted default, because Excel and Google Sheets need not agree on it and Sheets accepts fewer basis codes.

=INT(YEARFRAC(A2,TODAY(),1)) is the honest way to pull a whole age out of it, and for most birth dates it matches DATEDIF; where they disagree, DATEDIF is the calendar-exact one. So treat YEARFRAC as the decimal tool: its real use is when you want the fraction itself — age-weighted statistics, a pro-rated allowance, tenure accrual, a model taking age as continuous. And note that an actual/365 basis divides by 365, which is why it drifts upward exactly like =INT((TODAY()-A2)/365) does.

Calculating age in SQL

SQL is where the naive formula is not merely approximate but systematically wrong, because the classic DATEDIFF counts boundary crossings instead of elapsed units. Engines resolved that differently, so the answer depends on which database you are in.

SQL Server: DATEDIFF counts year boundaries, not years

DATEDIFF(datepart, start, end) returns how many times that datepart rolled over between the two dates. Someone born on 31 December 1990 gives DATEDIFF(year, birth, GETDATE()) = 34 on 1 January 2024, because 34 new years were crossed. The person is 33, having turned 33 the previous 31 December. Anyone whose birthday has not arrived yet this year comes out exactly one year too old, and the query looks fine because the function does what it documents.

The standard correction subtracts one when the anniversary has not been reached — the same shape as the Excel formula that avoids DATEDIF, written as one pasteable statement:

SELECT DATEDIFF(year, birth, GETDATE()) - CASE WHEN DATEADD(year, DATEDIFF(year, birth, GETDATE()), birth) > GETDATE() THEN 1 ELSE 0 END AS age FROM people;

The mechanism is DATEADD: adding the naive year count back onto the birth date produces the date that person's Nth birthday falls on. If that date is still ahead of today, the count overshot by one. Prefer this to comparing MONTH and day numbers by hand, because it handles 29 February birthdays without extra branches.

One clamping detail: SQL Server rolls an invalid target day back to the last valid day of the month, so DATEADD(year, 23, '2000-02-29') yields 28 February 2023 instead of overflowing into March. A 29 February birthday therefore increments a day earlier here than Excel's DATEDIF does in a non-leap year.

SQL Server: years, months and days

Apply the same correction one level down. The corrected count of complete months is DATEDIFF(month, birth, GETDATE()) - CASE WHEN DATEADD(month, DATEDIFF(month, birth, GETDATE()), birth) > GETDATE() THEN 1 ELSE 0 END. Alias it as fm in a derived table or a CROSS APPLY so the expression is written once.

From fm the parts need no further date logic: years are fm / 12 — both operands are integers, so SQL Server truncates — leftover months are fm % 12, and days are the tail after the shift, DATEDIFF(day, DATEADD(month, fm, birth), GETDATE()). That trio is exact, and it is what to use when an interface shows a mixed-unit age instead of a bare integer.

MySQL and PostgreSQL

MySQL's TIMESTAMPDIFF counts complete units and needs no correction: SELECT TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age FROM people. Total days is DATEDIFF(CURDATE(), birth) — that DATEDIFF takes the later date first and returns days, a different function from SQL Server's despite the name. For mixed units, TIMESTAMPDIFF(MONTH, birth, CURDATE()) / 12 gives years and MOD(TIMESTAMPDIFF(MONTH, birth, CURDATE()), 12) the residual months.

PostgreSQL is the most direct, because AGE returns a calendar interval: SELECT EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth)) AS age gives exact completed years, and dropping the EXTRACT shows the whole years, months and days decomposition with no correction. One trap: AGE is timezone-sensitive when it meets a timestamp with zone, so cast that column to date before comparing it with CURRENT_DATE.

When to use the age calculator instead

A formula is right when age is one column in a table of hundreds of rows. It is the wrong shape when you need the answer once and want every derived figure with it, because that is six formulas assembled to do what one page does.

The age calculator linked below takes a birth date and returns exact age in years, months and days, total days lived, whole weeks, hours, and a countdown to the next birthday. Its day count is the raw difference, so the derived units agree with each other: the hours figure is days lived times 24, which makes it a whole-day measure rather than a precise time-of-birth count. Chinese and Western zodiac signs come alongside the age, the Chinese one resolved against real Lunar New Year dates rather than 1 January, since the Chinese year does not start on the Gregorian new year. That table covers births from 1940 through 2030; outside it the tool falls back to the Gregorian year, so treat an earlier birth date's zodiac as unreliable.

Its edges are worth knowing. It works from dates only, with no hour or minute input. It uses your device's own today, so a machine with a wrong clock yields a wrong age. A future birth date is not rejected; it produces negative counts, so check the date if the numbers look impossible. The page states it is not for legal or official age verification, and nothing you enter is stored or sent anywhere.

Frequently asked questions

What is the best formula to calculate age from a date of birth in Excel?

=DATEDIF(A2,TODAY(),"Y") with the birth date in A2. It counts complete calendar years, so month lengths, leap years and the not-yet-arrived birthday are handled by construction, and it errors rather than returning a negative if the dates are the wrong way round. Format the result as a number, and confirm A2 is a real date: multiply it by 1 and you should get a five-digit serial number.

Why can I not find DATEDIF in Excel's function list?

Because Microsoft never documented it. The function exists for compatibility with older spreadsheets, so it computes correctly but has no autocomplete entry, no Help page in many versions and no function builder — which is why people assume it is unavailable and reach for a division. If you want a documented, portable formula, use =YEAR(TODAY())-YEAR(A2)-(TODAY()<DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))).

How do I show age in years and months in Excel?

Join two DATEDIF calls with different units: =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months". "Y" gives complete years since birth, "YM" gives complete months since the last birthday and is always 0 to 11. Add &", "&DATEDIF(A2,TODAY(),"MD")&" days" for the three-part age. The usual mistake is "M" for the month component: it returns total months since birth, not the remainder.

Is dividing by 365.25 an accurate way to calculate age?

A defensible approximation and an unreliable fact. A four-year block with one leap day spans 1461 days and 1461/4 is 365.25, so the constant has a basis — but the Gregorian mean year is 365.2425, because century years such as 1900 and 2100 are not leap years. The divisor is slightly too large, so the estimate runs behind the true age and the truncated integer comes out a year short in spans that lost a leap day: 36,524 days divided by 365.25 gives 99 where the age is 100. Dividing by 365 drifts the other way.

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

Use the reference date wherever the formulas use TODAY(). With the as-at date in B2: =DATEDIF(A2,B2,"Y") for completed years, or =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months" for two parts. For a fixed date write =DATEDIF(A2,DATE(2030,6,15),"Y") rather than a text date, which is re-read according to whoever opens the file's regional settings. For two dates that are not birth and today, a date difference calculator fits better.

Does Google Sheets calculate age the same way as Excel?

Yes. Sheets supports DATEDIF with identical arguments and unit codes, plus TODAY, YEARFRAC, DATE, DATEVALUE and MOD, so the formulas carry over unchanged. The Sheets-specific oddity is display: a cell that inherits a date format shows a small age number as a date near the start of the calendar, which is only formatting. Set Format to Number. Sheets also parses typed dates by the spreadsheet's own locale, not the viewer's.

Can I use YEARFRAC to calculate age?

You can, if you understand the return value. =YEARFRAC(A2,TODAY(),1) gives a decimal age such as 39.42, and INT of it gives whole years matching DATEDIF for most birth dates. Basis picks a day-count convention — actual/actual, actual/360, actual/365, or a 30/360 family — and a nominal 360 or 365 divisor drifts against the calendar the way dividing by 365 does. Name basis 1 rather than trusting a default, since Excel and Sheets need not agree and Sheets supports fewer codes.

Why is SQL Server DATEDIFF wrong for age?

It is not broken, it measures something else: DATEDIFF(year, start, end) counts the year boundaries crossed, not full years elapsed, so a person born 31 December 1990 returns 34 on 1 January 2024 while being 33. The standard correction is DATEDIFF(year, birth, GETDATE()) - CASE WHEN DATEADD(year, DATEDIFF(year, birth, GETDATE()), birth) > GETDATE() THEN 1 ELSE 0 END. Adding the naive count back to the birth date gives that Nth birthday, and if it is still ahead of today the count overshot by one.

Which SQL function counts full years for age?

Depends on the engine. MySQL's TIMESTAMPDIFF(YEAR, birth, CURDATE()) counts complete units and needs no correction. PostgreSQL's AGE returns a calendar-aware interval, so EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth)) is the exact integer age — cast a timestamp-with-zone column to date first. SQL Server's DATEDIFF is the one that needs the CASE correction. Watch shared names: MySQL's DATEDIFF takes the later date first and returns days, unlike SQL Server's three-argument boundary counter.

Is there a free tool that gives exact age, days lived and zodiac?

The age calculator linked on this page takes a birth date and returns exact age in years, months and days, total days lived, whole weeks, hours, and a countdown to the next birthday, plus Chinese and Western zodiac signs. The Chinese sign uses real Lunar New Year dates, dependable for births from 1940 through 2030. It works from dates only, uses your device's date, is not intended for legal or official age verification, and runs in your browser without sending anything to a server.

How do I find how many days, weeks or hours I have lived?

Subtract the birth date from today: the total number of days is the only figure that comes out exact from two dates. Whole weeks are that number divided by 7, and hours are the days times 24. Do not use an age-in-years formula for this: the year is a variable duration (leap years included), while days, weeks and hours are fixed counts. The calculator linked above shows days, weeks and hours lived in one shot.

How can I calculate exact age in years, months and days without writing formulas?

Type your birth date into the age calculator on this page: it returns your age in completed years, months and days using the real calendar, not an approximate division, and adds the countdown to your next birthday. For any two dates that are not a birth (a deadline, an anniversary), use the site's date difference calculator.

All guides

How to Convert JPG to PDF on Any Device
How to Convert PDF to JPG on Any Device
How to Combine PDF Files into One Document
How to Convert PNG to JPG on Any Device
Advertisement