YEARFRAC
Returns the fraction of the year represented by the number of whole days between two dates. Useful for prorating annual amounts or computing accrued interest.
YEARFRAC(start_date, end_date, [basis])Arguments
start_dateThe start date.end_dateThe end date.basisOptional. The day count basis: 0=US 30/360 (default), 1=actual/actual, 2=actual/360, 3=actual/365, 4=European 30/360.(optional)
=YEARFRAC("2026-01-01","2026-07-01")Half a year (using 30/360 basis)
=YEARFRAC("2026-01-01","2027-01-01",1)One full year using actual/actual basis
=YEARFRAC(A1,A2,3)Year fraction using actual/365 basis
- •Heavily used in finance for accrued interest, bond pricing, and prorating
- •Default basis (0) is US 30/360 — set basis explicitly for accurate calendar calculations
- •Returns #NUM! if basis is not 0-4
- •Combines well with PMT, IPMT and other financial functions
- •Forgetting that the basis argument controls how days are counted (like 30/360 vs. actual/actual), and different basis values can produce noticeably different fractional-year results for the same two dates
- •Mixing up the order of start_date and end_date - putting the later date first still calculates a result, but interpreting a resulting fraction correctly requires knowing which date came first in the arguments
- •Expecting YEARFRAC to always return a clean, round fraction like 0.5 for exactly half a year - the exact result depends heavily on the chosen basis and the specific dates involved, since months have unequal lengths
DATEDIFCalculates the difference between two dates in whole years, months, or days, rather than YEARFRAC's fractional-year result.DAYSCalculates the simple difference in days between two dates, without expressing the result as a fraction of a year.DATEOften used to construct the start_date or end_date arguments before feeding them into YEARFRAC.Why do I get different results from YEARFRAC depending on the basis argument?
Different basis values use different day-counting conventions - some assume every month has 30 days, others count actual calendar days - so the same two dates can produce meaningfully different fractional results.
What happens if end_date is earlier than start_date in YEARFRAC?
It still calculates a result, but interpreting the sign and value correctly requires knowing that the dates were reversed - it's usually clearer to keep the earlier date first.
Why doesn't YEARFRAC return exactly 0.5 for a 6-month period?
Because months have unequal lengths, and the basis argument's day-counting method affects the precise fraction - a clean 0.5 isn't guaranteed even for what looks like exactly half a year.
Need to translate a formula using YEARFRAC?
Use our translator to convert your complete formula