To calculate age between two dates in Excel, use the DATEDIF function with the syntax =DATEDIF(start_date, end_date, "Y") for whole years, or swap the unit argument to "M" for months and "D" for days; the function counts completed intervals between the two dates and is the most reliable built-in method for age math. A complete years/months/days breakdown requires three DATEDIF calls combined with concatenation, or you can skip the spreadsheet entirely and use a free Age Calculator that shows your exact age in years, months, and days the moment you pick a birth date. Both approaches solve the same problem — finding the time elapsed between two calendar dates — but they differ in setup time, flexibility, and the depth of the breakdown they produce.
Excel is the right tool when you already have a spreadsheet full of birth dates, hire dates, contract start dates, or any column of dates that needs to be turned into an "age" or "tenure" column. An online tool is faster for one-off lookups ("how old am I in days?"), for verifying a manual calculation, or when the reader does not have Excel at all. The two approaches complement each other rather than compete, so this guide covers the Excel formulas in detail and points to the browser tool for the cases where formulas are overkill.
Why DATEDIF Is the Standard Excel Age Formula
DATEDIF (which stands for "Date Difference") is a hidden-but-supported Excel function that returns the gap between two dates in completed years, months, or days. Unlike a simple subtraction, DATEDIF knows the calendar well enough to handle month lengths, leap years, and the fact that February has 28 or 29 days rather than a flat 30. Microsoft documents DATEDIF for legacy reasons — it predates the modern function library — which is why it does not appear in the formula autocomplete dropdown, but it works in every modern version of Excel and in Google Sheets under the same name.
The function takes three arguments: the start date, the end date, and a unit string in quotes. The unit string is what controls the output:
| Unit | Returns | Example (1990-06-15 to 2024-03-20) |
|---|---|---|
| "Y" | Completed full years | 33 |
| "M" | Completed full months | 405 |
| "D" | Total days | 12,329 |
| "YM" | Months past the last full year | 9 |
| "MD" | Days past the last full month | 5 |
| "YD" | Days past the last full year | 279 |
The most useful variant for a "years, months, days" breakdown is the Y/YM/MD combination, which is what most age calculators and HR systems display. The MD and YD units are slightly quirky — Excel documents them as known approximations because months have different lengths — but for typical age lookups they give the answer people expect.
Calculating Age in Excel Step by Step
Assume column A holds birth dates (with A2 as the first entry) and column B holds the reference date you want to age against (or you can use TODAY() in the formula directly). Then follow these steps to build a working age formula:
- Enter or paste your dates. Put the start date in cell A2 and the end date in cell B2. Format both as real dates (Home → Number Format → Short Date or Long Date). DATEDIF returns a #VALUE! error if either cell is stored as text.
- Calculate completed years. In cell C2, type
=DATEDIF(A2,B2,"Y")and press Enter. This is the whole-year age, the way most people would say it ("33 years old"). - Calculate the leftover months. In cell D2, type
=DATEDIF(A2,B2,"YM"). This counts how many full months have passed since the last birthday, ignoring the day of the month. - Calculate the leftover days. In cell E2, type
=DATEDIF(A2,B2,"MD"). This counts days past the last full month, again ignoring longer months. - Combine the three into a readable string. In cell F2, use
=C2 & " years, " & D2 & " months, " & E2 & " days"to show "33 years, 9 months, 5 days". - Use TODAY() if you want a live age. Replace B2 with
TODAY()in any of the formulas above and the age will update each time the spreadsheet is opened or recalculated. - Copy the formulas down. Select C2:F2 and drag the fill handle down to apply the same age calculation to every row in your date list.
That is the canonical "how to calculate age between two dates in Excel" recipe. The DATEDIF function does the calendar arithmetic; the concatenation builds a human-readable summary. The same pattern works in Google Sheets and in LibreOffice Calc with identical syntax.
Alternative Excel Formulas: YEARFRAC and the Quick Approximation
DATEDIF is the right default, but two other formulas show up often enough to be worth knowing. The first is =(YEAR(B2)-YEAR(A2)) combined with a date check, which is the quick-and-dirty method some older Excel guides recommend. It returns the difference in calendar years and subtracts one if the birthday has not yet occurred in the end-date's year. The formula is short, but it cannot return months or days, and it gets confused around the leap day (February 29 birthdays in non-leap reference years).
The second is YEARFRAC, which returns age as a decimal: =YEARFRAC(A2, B2, 1). The trailing argument chooses the day-count basis — 1 means actual/actual, which is the standard for most age calculations. YEARFRAC is the formula to reach for when you need fractional years for interest, vesting schedules, or any actuarial math, because it weights each year by its actual length and handles leap years correctly. For everyday "how old am I" questions, the integer output of DATEDIF is easier to read.
Neither of these alternatives replaces DATEDIF for the standard years/months/days breakdown. YEARFRAC and the YEAR-difference shortcut are best treated as specialized tools for cases where the result has to be a number rather than a calendar-style age.
Common Excel Age-Formula Mistakes and How to Fix Them
Most DATEDIF errors fall into a small set of categories that are easy to diagnose once you know what to look for:
- #VALUE! error. The start or end cell is stored as text. Re-format the cell as a Short Date and re-enter the value, or wrap it in DATEVALUE().
- Reversed dates showing as 0 or a huge number. DATEDIF returns a negative number (or 0) when the start date is after the end date. Swap the two arguments or use IF to check the order first.
- "MD" giving weird values at the end of the month. The MD unit is approximate. If you need exact day counts, use
=B2-DATE(YEAR(B2)-DATEDIF(A2,B2,"Y"), MONTH(A2), DAY(A2))-DATEDIF(A2,B2,"Y")*365.25or skip MD and compute the day portion from the other two DATEDIF units. - Age is off by one year. The most common cause is using
=YEAR(B2)-YEAR(A2)without a month/day check. Use DATEDIF with the "Y" unit instead and the off-by-one disappears. - Formulas do not update. Workbook calculation is set to Manual. Press F9 to recalculate, or switch the workbook back to Automatic recalculation under Formulas → Calculation Options.
If the spreadsheet keeps fighting back, it is usually faster to verify the answer with a browser tool than to debug the formula. Drop the same two dates into the Age Calculator and you will see the expected years/months/days breakdown within a second, which gives you a known-good value to compare against.
When to Use an Online Age Calculator Instead of Excel
Excel is the right tool when you have many dates to process or when age feeds into further spreadsheet logic. For one-off questions — "how old am I in weeks?", "how many days until my 50th birthday?", "what was my age on the day I started that job in 2017?" — a browser tool is faster and gives more detail than a formula could. A typical Age Calculator takes a birth date, optionally takes a second reference date, and returns years, months, days, total months, total weeks, total days, hours, minutes, and a live countdown to the next birthday. None of those require any formula writing.
For date math that is adjacent to age — counting business days between two dates, adding 90 days to an invoice date, or finding the day of the week a date falls on — a few related tools are worth bookmarking alongside the age one. The Date Difference Calculator counts the gap between any two dates in days, weeks, and months. The Date Add Subtract Calculator shifts a starting date forward or backward by any number of days, weeks, months, or years. The Day of Week Calculator tells you which weekday a given date lands on. Together with the Age Calculator, they cover most of the calendar arithmetic that people normally try to do in Excel with fragile formulas.
For readers who do want to keep the work in Excel but would like a quick sanity check, the workflow is simple: build the DATEDIF formulas, then verify one or two rows by hand or with the Age Calculator. Once the formula output matches the tool for the first few rows, you can trust it for the rest of the column. That is how professionals use both tools together — the spreadsheet for scale, the browser tool for verification.
Quick Reference: Age Formulas at a Glance
| Goal | Formula | Best For |
|---|---|---|
| Age in full years only | =DATEDIF(A2,B2,"Y") | HR records, age gates, eligibility checks |
| Age in years, months, days | =DATEDIF(A2,B2,"Y") & "y " & DATEDIF(A2,B2,"YM") & "m " & DATEDIF(A2,B2,"MD") & "d" | Display-friendly age strings |
| Total months lived | =DATEDIF(A2,B2,"M") | Subscription or billing tenure |
| Age in decimal years | =YEARFRAC(A2,B2,1) | Interest, vesting, fractional-age math |
| Live age as of today | =DATEDIF(A2,TODAY(),"Y") | Spreadsheets that should age automatically |
| Quick years-only approximation | =YEAR(B2)-YEAR(A2)-(IF(DATE(YEAR(B2),MONTH(A2),DAY(A2))>B2,1,0)) | Legacy reports that already use this pattern |
The DATEDIF pattern is the one to memorize. The other formulas have niche uses, and the browser-based Age Calculator handles the edge cases (exact day counts, future reference dates, next-birthday countdowns) that no single Excel formula gives you in a single cell.
More on this topic: How to Calculate Cube Volume from One Side Length.
For a deeper look, see How to Convert Angle in Degrees to Radians (Step-by-Step).
For a deeper look, see How to Calculate Annular Area with the Free Online Tool.