Summary

The Excel YEARFRAC function returns the fraction of a year between two dates. Instead of giving you only a day count or a whole number of years, it gives you a decimal such as 0.5, 1.25, or 10.8.

YEARFRAC is especially useful for interest calculations, prorated payments, lease and contract timing, and age or tenure formulas where partial years matter. The optional basis argument lets you choose how the year fraction should be measured.

Syntax

=YEARFRAC(start_date, end_date, [basis])

start_date and end_date are required. The optional basis argument tells Excel which day-count convention to use. For best reliability, Microsoft recommends entering dates with DATE(...) or using valid Excel date values rather than ambiguous text dates.

Arguments

• start_date - The first date in the period.
• end_date - The ending date in the period.
• basis - [optional] The day-count basis: 0 = US (NASD) 30/360, 1 = Actual/Actual, 2 = Actual/360, 3 = Actual/365, 4 = European 30/360.

YEARFRAC vs Other Date Methods

These tools all work with date gaps, but they answer different questions.

If you need a decimal portion of a year, YEARFRAC is the right tool. If you only need the number of days, simple subtraction is usually clearer. If you need completed whole years, DATEDIF is often a better fit.

Using YEARFRAC

The key decision in YEARFRAC is the basis. In some models, the exact rule matters because different industries count time differently. Basis 1 uses actual days and is often the easiest choice when you want a real calendar-based fraction. Basis 0 and 4 use 30/360 methods, which are common in finance because they standardize months and years.

Basis 2 and 3 are also useful when a model needs a fixed denominator of 360 or 365 days. This means the same date range can return slightly different results depending on the basis you choose.

One important Microsoft note: YEARFRAC can return an incorrect result with basis 0 when the start_date is the last day of February. If you are working with strict financial conventions, that detail is worth checking in your model.

Example 1 - Basic Actual/Actual Fraction

Use YEARFRAC with basis 1 when you want the fraction based on the actual calendar days in the period.

=YEARFRAC("2026-01-01", "2026-06-30", 1)

This returns a value just under 0.5, because the period is slightly less than half of a non-leap year. It is a good example of normal calendar-based behavior.

Example 2 - Calculate Decimal Age or Tenure

YEARFRAC is useful when you need elapsed time in years with decimals, not just whole years.

=YEARFRAC("1995-10-15", TODAY(), 1)

This can be used for decimal age, length of service, or time since a contract start date. It is especially helpful when a report or calculation depends on partial years.

Example 3 - Compare Basis Results

The same date range can produce a different answer depending on the basis. That is why the third argument matters.

=YEARFRAC("2026-01-01", "2026-06-30", 1)   // Actual/Actual
=YEARFRAC("2026-01-01", "2026-06-30", 0)   // US 30/360

For this period, the Actual/Actual result is slightly below half a year, while the 30/360 result is exactly 0.5. This is a practical way to show how convention changes the output.

Example 4 - Prorate an Annual Amount

Once you have a year fraction, you can multiply it by an annual amount to calculate the prorated share.

=YEARFRAC(A1, B1, 1) * 5000

This pattern is common for prorated salary, rent, interest, subscription value, or contract charges. YEARFRAC gives you the time portion, and multiplication converts that into money.

Conclusion Recap

• Summary: YEARFRAC returns the fraction of a year between two dates.
• Core strength: It handles partial years directly, which makes it useful for prorated calculations.
• Basis control: Different basis values can change the result, so choose the one that matches your model.
• Common use cases: Interest, rent, bonus accruals, age, tenure, and contract timing.
• Important caution: Basis 0 can be problematic when the start date is the last day of February.
• Best practice: Use valid Excel dates or DATE(...) for reliable results.