Excel Formula for Years Between Dates: Exact or Decimal

For completed years between two dates in Microsoft Excel, enter a start date in A2 and an end date in B2, then use =DATEDIF(A2,B2,"Y"). It returns whole years elapsed; for a decimal year fraction instead, use =YEARFRAC(A2,B2,1), which counts actual days on an actual/actual basis.

Choose the kind of year difference you need

“Years between dates” can mean more than one thing. The right formula depends on whether you want to know how many full anniversaries have passed, how many calendar-year boundaries the dates cross, or the elapsed time expressed as a decimal number of years. These answers can differ substantially, especially when the dates are close together or cross a year boundary.

Completed years: Use DATEDIF with the “Y” unit for ages, service duration, and anniversaries. A start date of May 20, 2020 and an end date of May 19, 2024 is three completed years, not four.
Decimal years: Use YEARFRAC when a calculation needs a fraction, such as 2.5 years. Choose its basis argument deliberately because different bases count days differently.
Calendar-year boundaries: Subtract the years shown on the dates when you specifically need the difference between their calendar years. This does not measure elapsed time.

For example, December 31, 2023 to January 1, 2024 crosses into a new calendar year, but only one day has elapsed. A formula that subtracts the year numbers reports one; a completed-years formula reports zero. That is not a contradiction: the formulas answer different questions.

How DATEDIF counts completed years

DATEDIF returns the number of complete intervals between a start date and an end date. Its “Y” unit counts whole years and ignores any remaining months and days. With a start date in A2 and an end date in B2, this formula returns the number of complete years between them: =DATEDIF(A2,B2,"Y").

Suppose A2 contains January 15, 2020 and B2 contains January 14, 2024. The result is 3 because the fourth anniversary has not yet arrived. Change the end date to January 15, 2024 and the result becomes 4. This anniversary-based meaning makes DATEDIF a natural choice for ages and completed years of employment.

DATEDIF is a long-standing Excel function, but it is not as visible as many functions: Excel may not show it in the formula autocomplete list or provide the usual argument prompts. It is still usable when typed correctly. The start date must be earlier than or equal to the end date. If the dates are reversed, DATEDIF returns the #NUM! error rather than a negative duration.

Calculate fractional years with YEARFRAC

YEARFRAC returns the portion of a year represented by the period between two dates. Its third argument, called the basis, determines the day-count convention. For an actual-days calculation using an actual/actual basis, put the start date in A2 and end date in B2, then enter =YEARFRAC(A2,B2,1).

For example, if A2 is January 1, 2020 and B2 is July 1, 2020, the dates are 182 days apart in a 366-day leap year. The actual/actual result is about 0.4973 years. The displayed value may be rounded depending on the cell’s number format, so increase the displayed decimal places if you need to inspect more digits.

The basis matters. YEARFRAC with basis 0, which is also the default when the third argument is omitted, uses a US 30/360 convention rather than simply dividing actual days by 365. Basis 1 uses actual days and actual year lengths; basis 2 uses actual days over 360; basis 3 uses actual days over 365; and basis 4 uses a European 30/360 convention. A result can therefore vary by basis, particularly over long periods or around leap years. If a business, contract, or reporting method specifies a convention, use that convention rather than assuming one is universal.

YEARFRAC is useful when a fractional value is meaningful, but a decimal year is not automatically a precise age or a count of completed anniversaries. For example, 3.99 years is not four completed years. Use DATEDIF for whole-year milestones and YEARFRAC when the downstream calculation genuinely calls for a fraction.

Worked formulas for common situations

Keep the date inputs in separate cells whenever possible. That makes the calculation easier to audit and lets you update either date without rewriting the formula. These examples assume dates are in A2 and B2 unless noted:

Full years between two dates: With the earlier date in A2 and later date in B2, use =DATEDIF(A2,B2,"Y"). January 15, 2020 through January 14, 2024 returns 3.
Calendar-year number difference: With dates in A2 and B2, use =YEAR(B2)-YEAR(A2). December 31, 2023 to January 1, 2024 returns 1, even though only one day elapsed.
Fractional actual/actual years: With dates in A2 and B2, use =YEARFRAC(A2,B2,1). January 1, 2020 to July 1, 2020 returns approximately 0.4973.
Age in completed years as of today: With a birth date in A2, use =DATEDIF(A2,TODAY(),"Y"). TODAY updates to the current date when Excel recalculates the workbook, so the displayed age can change over time.

For a birth date of June 15, 1990, the completed age is 33 on June 14, 2024 and 34 on June 15, 2024. That illustrates why subtracting birth year from current year can be wrong before the birthday has occurred. For a reusable age calculation, the anniversary-based result is usually the intended one.

Enter dates as dates, not text

Excel stores valid dates as serial numbers and displays them using a date format. A cell can look like a date while actually containing text, especially when values were pasted from a website, imported from a system with different regional settings, or entered with an unrecognized month-and-day order. Text dates can cause errors or unexpected results.

To check a date, select its cell and look at the formula bar and number format. A quick practical test is to change the cell’s number format to General: a genuine Excel date displays as a serial number, while text often remains recognizable text. You can also try sorting the column; dates stored as text may not sort in chronological order. If imported dates are text, convert them using Excel’s data-import or conversion tools rather than relying on a formula to guess the intended order.

Regional settings affect how Excel interprets entries such as 03/04/2024: it might mean March 4 or April 3. When sharing a workbook, unambiguous dates such as 4-Mar-2024 can reduce confusion, though the stored value still depends on Excel correctly recognizing the entry. In Microsoft 365 for Windows, date formats are available from Home > Number Format. On Mac and Excel for the web, the formatting controls are located in their respective Home tabs, but the interface may look different.

Leap years and anniversary edge cases

Leap years affect the number of days in a year, which is one reason a decimal-year calculation needs a stated day-count basis. YEARFRAC with basis 1 accounts for actual year lengths; a simple division by 365 would not. That shortcut can be acceptable for rough estimates, but it is not equivalent to the actual/actual convention and may drift over multiple years.

February 29 birthdays require an explicit policy when calculating an age in a non-leap year. Some organizations treat the anniversary as February 28; others use March 1. DATEDIF’s completed-year behavior follows date comparison rather than choosing a legal or organizational policy for you. If the difference matters, decide which anniversary rule applies and verify the result for leap-day records instead of assuming every context uses the same convention.

Another edge case is an end date earlier than the start date. DATEDIF does not return a negative number; it produces #NUM!. If dates can arrive in either order, decide whether to reject reversed input, swap the dates, or calculate a signed duration with a different method. Automatically taking the absolute value may hide a data-entry mistake, so it is better to make the intended rule clear.

Formatting and troubleshooting results

DATEDIF with the “Y” unit returns a whole number, so ordinary Number or General formatting is appropriate. YEARFRAC returns a decimal; format the result as Number and set the decimal places you need. If Excel displays a date-like value rather than a number, change the result cell’s number format. Formatting changes how a result appears, not the underlying calculation.

When a formula produces an unexpected answer, check these points:

Confirm which date is the start and which is the end, particularly for DATEDIF.
Check that both inputs are valid Excel dates and not text values.
Confirm that you asked for completed years, fractional years, or calendar-year difference as intended.
For YEARFRAC, inspect the basis argument instead of relying on the default.
Look for hidden time values. Date-time cells can include hours and minutes even when the display shows only the date; those times may affect a fractional calculation.
Check whether the result cell is formatted as a date, which can make a valid numeric result look confusing.

If you need a result that updates daily, TODAY is convenient but volatile: Excel recalculates it as the workbook recalculates. If you need a fixed “as of” date for a report or audit, enter that date in a separate cell and reference it as the end date instead. That keeps the result reproducible later.

Frequently Asked Questions

What is the Excel formula for years between two dates?
For complete years, put the start date in A2 and the end date in B2, then use =DATEDIF(A2,B2,"Y"). Use YEARFRAC when you need a decimal-year result rather than whole anniversaries.

Why does DATEDIF return one less year than subtracting the date years?
DATEDIF counts completed anniversaries. If the next anniversary has not yet arrived, it does not count that year as complete. Subtracting YEAR values counts calendar-year boundaries instead and can overstate elapsed years.

Can Excel calculate years as a decimal?
Yes. With the start date in A2 and end date in B2, =YEARFRAC(A2,B2,1) returns a fraction based on actual days and actual year lengths. Choose a different basis only when that day-count convention is what your calculation requires.

Why does DATEDIF show #NUM!?
The end date is probably earlier than the start date, or one of the entries is not a usable date. Check their order and confirm both cells contain real Excel dates.

Does subtracting years calculate someone’s age correctly?
Not by itself. Subtracting birth year from the current year can count a birthday that has not happened yet. Use DATEDIF with the birth date and the date you want to calculate age as of, then check any February 29 policy that applies.

Which formula should you use?

For ages, tenure, and other questions about completed anniversaries, DATEDIF with “Y” is the clearest answer. For elapsed time expressed in decimal years, use YEARFRAC and specify the appropriate basis. Use a simple year-number subtraction only when the question is explicitly about calendar years. Matching the formula to the meaning of “years” prevents the most common error: a correct calculation answering the wrong question.

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.