YEARFRAC Function in Microsoft Excel: Syntax, Examples & Troubleshooting

=YEARFRAC(start_date, end_date, [basis])

Start and end dates are required; basis is optional. The accepted basis codes are 0 or omitted = US (NASD) 30/360; 1 = actual/actual; 2 = actual/360; 3 = actual/365; 4 = European 30/360.

Microsoft documents truncation of all arguments to integers. Consequently, this is a whole-day year fraction, not a precise elapsed-seconds calculation. Use real dates or DATE formulas rather than text that could be interpreted differently across locales.

1. Enter =DATE(2026,1,15) in B2 and =DATE(2026,4,15) in B3.
2. Enter =YEARFRAC(B2,B3,2) in D2. Expected result: 0.25, since 90/360 = 0.25.
3. Enter =YEARFRAC(B2,B3,3) in D3. Expected result: approximately 0.2465753425, since 90/365 gives that fraction.
4. Enter =YEARFRAC(B2,B3,1) in D4. For this interval wholly in non-leap 2026, expected result is also 0.2465753425.

These are year fractions, not the number of complete anniversaries. Percentage formatting would display the first result as 25%, while General format shows 0.25. Neither display changes the underlying fraction.

=YEARFRAC(DATE(2024,1,1),DATE(2024,7,1),1)
=YEARFRAC(DATE(2024,1,1),DATE(2024,7,1),3)

There are 182 actual days in this interval. Expected results are 182/366 = 0.4972677596 for actual/actual and 182/365 = 0.4986301370 for actual/365. Basis 3 retains 365 even though the interval is in a leap year.

Do not generalize the single-year actual/actual denominator to every multi-year span. The convention has interval-dependent rules, and these simple controls do not establish every long-span or leap-day boundary.

Suppose an example annual charge is 1200, and the agreement explicitly uses actual/365 over the original 90-day interval. Enter =1200*YEARFRAC(B2,B3,3) in D6. Expected result: approximately 295.89041096.

If the agreement instead uses actual/360, the analogous result is 300. The difference is a declared convention, not evidence that one formula is broken. Label the basis in a separate cell and include it in review, rather than omitting the argument or changing it to match a desired amount.

An age-in-complete-years calculation is a different problem. A fractional year is not automatically the same as counting completed anniversaries, particularly when rounding is introduced. Likewise, adding one to the elapsed day count for inclusive attendance dates is a separate policy, not an implied YEARFRAC setting.

Microsoft explicitly warns that YEARFRAC can return an incorrect result with US (NASD) 30/360 when start_date is the last day of February. This includes the omitted-basis form. Do not present that combination as reliable simply because an ordinary mid-month example works; select a contractually valid alternative or independently validate the boundary.

Invalid dates produce the documented #VALUE! error; basis less than 0 or greater than 4 produces #NUM!. Review date storage, omitted defaults and basis selection before masking an error. A time component is truncated, and the result should normally be formatted as a number rather than a date.

These examples illustrate calculation choices. They do not establish entitlement rules, employment policy or a legally required financial day-count method.

Microsoft’s reference checked on October 17, 2026 lists: Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016. This current support list does not establish the first release. The introduction version and last functioning version have not been established from the reviewed documentation. No retirement is asserted.

Example verification: Expected numerical results are independently calculated; text and shape expectations follow the documented contract. These examples have not been tested in Microsoft Excel. No screenshot or native execution is claimed.

DATE in Excel | DAYS in Excel | DATEDIF in Excel | YEAR in Excel

• Microsoft YEARFRAC reference, retrieved October 17, 2026.