Age Formula in Excel Without DATEDIF

Formulas & functions · Updated November 19, 2026

To calculate age in completed years in Excel without DATEDIF, put a birth date in A2 and enter =YEAR(TODAY())-YEAR(A2)-IF(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))>TODAY(),1,0) in another cell. The formula subtracts one if this year’s birthday has not happened yet, so it returns whole years rather than a rough difference between calendar years.

What the age formula is doing

The calculation has three parts. YEAR(TODAY()) gets the current year, and YEAR(A2) gets the birth year. Subtracting those values gives the difference between the two years, but that number can be one too high if the person’s birthday is still ahead on the calendar. The IF portion checks whether the birthday in the current year is later than today. If so, it subtracts one; otherwise, it subtracts zero.

For example, suppose A2 contains November 4, 2000, and the formula is evaluated on November 3, 2026. The year difference is 26, but the 2026 birthday is one day away, so the formula returns 25. If A2 is November 3, 2000, it returns 26. These are completed ages: the number of full birthdays reached, not a decimal age or a count of calendar years touched.

Excel stores valid dates as serial numbers, which lets it compare dates and extract their year, month, and day. A2 therefore needs to contain a real Excel date, not text that merely looks like one. The formula works in Microsoft 365 for Windows and uses the same functions in Excel for Mac and Excel for the web.

Enter the formula and check the result

Use this setup for a simple age list:

  • Put each person’s date of birth in column A, starting in A2.
  • Enter the formula in B2, beside the first birth date.
  • Fill or copy B2 down for the other rows. Each copied formula adjusts its reference to the birth date on that row.

With the birth date in A2 and the formula in B2, use =YEAR(TODAY())-YEAR(A2)-IF(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))>TODAY(),1,0). If A2 is November 3, 2000, and today is November 3, 2026, B2 returns 26. If A2 is November 4, 2000, it returns 25.

If the result appears as a date or an oddly formatted number, select the result cell and change its number format to General or Number. The result is a count of years, so it should usually display as an integer. The formatting of the birth-date cell itself should be a date format; changing a cell’s appearance does not, by itself, convert text into an Excel date.

Use a fixed as-of date instead of today

TODAY() is convenient for a live age list, but it updates as time passes. For a report that must reproduce an age as of a particular date, keep the reference date in a cell. This also makes the formula easier to check: you can change the as-of date and see how the answer changes without editing the calculation.

Assume birth dates are in A2:A100 and a valid as-of date is in B1. Put this formula beside the first birth date, then fill it down: =YEAR($B$1)-YEAR(A2)-IF(DATE(YEAR($B$1),MONTH(A2),DAY(A2))>$B$1,1,0). The dollar signs lock the reference to B1 as the formula is copied. If B1 is November 3, 2026, and A2 is November 4, 2000, the result is 25. Change B1 to November 4, 2026, and the result becomes 26.

A fixed as-of date is useful for dated records, eligibility checks, and archived reports because the result will not change simply because the workbook is opened next month. Enter B1 as a real date, and label it clearly—for example, “Age calculated as of”—so another person can understand what the figures represent.

Leap-day birthdays and the age convention

A February 29 birthday needs a convention in a non-leap year, when February has only 28 days. The formula above uses March 1 as the effective birthday in such a year. That behavior follows from Excel’s DATE function: asking it for February 29 in a year without that date rolls the date forward to March 1. On February 28, the comparison still treats the birthday as upcoming; on March 1, it does not.

There is no single convention that fits every administrative or personal use. Some organizations or jurisdictions may treat a leap-day birthday as occurring on February 28 in non-leap years. If that distinction matters, confirm the applicable rule rather than assuming the spreadsheet formula determines it. For an everyday age list, pick the intended convention, document it, and use it consistently. The ordinary formula is suitable when March 1 is the chosen convention.

Why not subtract birth year from current year?

A tempting shortcut is to subtract the birth year from the current year. With a birth date in A2, =YEAR(TODAY())-YEAR(A2) is valid Excel syntax, but it does not always return a person’s completed age. It counts the current calendar year in every case, even if the birthday has not arrived. On November 3, 2026, someone born on November 4, 2000, is 25, although subtracting 2000 from 2026 gives 26.

The adjustment in the full formula is what fixes that off-by-one error. It compares an actual anniversary date with today rather than relying only on year numbers. This is why a simple year subtraction can be fine for rough grouping but is not dependable when the exact age in completed years matters.

Common errors and how to fix them

  • The formula returns a strange value or an error: Check that A2 is a valid date and not text. A date that was imported from another system can look normal while being stored as text. Re-enter it as a date or convert the source data before calculating.
  • The result is one year too high: Confirm that the formula includes the IF test and refers to the correct birth-date cell. The year-only shortcut misses the birthday check.
  • The answer changes over time: That is expected with TODAY(). Use a fixed date in a cell for a report tied to a specific day.
  • Excel rejects the formula: Some regional settings use semicolons rather than commas between function arguments. If the comma version is not accepted, use the argument separator configured for that Excel installation.
  • The result displays as a date: Set the result cell’s number format to General or Number. The formula calculates a number of years, not a date.

Excel recalculates TODAY() as the workbook recalculates, so an age may update when the file is opened or recalculated. If a workbook does not seem to update, check that calculation is set to Automatic in Excel’s calculation options. A fixed as-of date avoids relying on a changing system date for historical calculations.

Choosing the right approach for a workbook

Choose the live formula when the question is “How old is this person today?” Choose the as-of-date version when the question is “How old were they on this date?” The distinction is important in any list that may be reviewed later: a live value answers a different question every day, while a value tied to a stored date can be reproduced.

Before filling a formula down a long column, test it against a few known dates: a birthday that has already passed this year, one that is today, and one still to come. Also check a leap-day birth date if your data includes one. These small checks catch wrong references, text dates, and assumptions about birthday timing before they affect a whole report.

For a larger workbook, keep the source of truth visible: retain the birth date, label the calculated-age column, and record the as-of date if using one. Avoid replacing birth dates with ages, because ages become stale and cannot reliably be used to reconstruct the original dates. If the file is shared, a brief note about the leap-day convention can prevent conflicting interpretations.

Frequently Asked Questions

Can Excel calculate age without DATEDIF?
Yes. Use YEAR to get the year values, DATE to create this year’s birthday, and IF to subtract one when the birthday has not arrived. The formula at the start of this page uses only standard Excel functions.

Does the formula update automatically on a birthday?
It uses TODAY(), so its answer is based on the current date when Excel recalculates. It does not need to be edited on each birthday. For a calculation that must stay fixed, use an as-of date entered in a cell instead.

How do I calculate age as of a date in another cell?
Put the as-of date in B1 and use the fixed-date formula shown above, with the birth date in A2. The formula compares the birthday in the year from B1 against B1 itself, so it returns completed years as of that date.

What does the formula return for someone born on February 29?
The formula treats March 1 as the birthday in a non-leap year because Excel rolls the constructed February 29 date forward. If your use requires February 28 instead, apply the relevant rule explicitly rather than assuming both conventions produce the same result.

Why is my age formula one year off?
The most common reason is using only the difference between the current year and birth year. That shortcut does not check whether the birthday has occurred yet. Also verify that the birth date is a real Excel date and that the formula points to the intended cell.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.