YEARFRAC Function in Google Sheets: Formula, Examples & Troubleshooting
Calculate fractional years with YEARFRAC in Google Sheets. Compare all five day-count bases, avoid date-division mistakes, and audit a simple accrual.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: YEARFRAC measures the interval between two dates in years under a chosen day-count convention. Always make the basis explicit when it matters: the default is US 30/360, not actual elapsed days divided by the actual year’s length.
Verification: NOT_TESTED_IN_GOOGLE_SHEETS. First-party documentation was reviewed October 17, 2026. The calendar arithmetic below was independently checked, with no native Sheets execution.
Syntax and the five bases
=YEARFRAC(start_date, end_date, [day_count_convention])
Both dates are required and can be date cells, date-producing functions, or numeric serials. The optional convention defaults to 0:
| Basis | Convention |
|---|---|
| 0 | US (NASD) 30/360; adjusted 30-day months and 360-day years |
| 1 | Actual/Actual; actual days and the applicable actual year lengths |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360; different end-of-month adjustments from basis 0 |
Google recommends considering basis 1 for non-financial use. Basis 0 and basis 4 are distinct conventions, even when an ordinary mid-month example gives the same result. Google YEARFRAC reference.
Example: compare conventions for the same interval
Create this input area:
| Cell | Label or formula |
|---|---|
| A1 | Start |
| B1 | End |
| A2 | =DATE(2025,1,1) |
| B2 | =DATE(2025,7,1) |
| D1 | Basis |
| E1 | Fractional years |
- Enter 0, 1, 2, 3, and 4 in D2:D6.
- In E2 enter
=YEARFRAC($A$2,$B$2,D2). - Copy E2 through E6 and display enough decimal places to see the differences.
Expected results, independently calculated:
| Cell | Basis | Expected result |
|---|---|---|
| E2 | 0 | 0.5 |
| E3 | 1 | 0.495890410958904 |
| E4 | 2 | 0.502777777777778 |
| E5 | 3 | 0.495890410958904 |
| E6 | 4 | 0.5 |
There are 181 actual days between these dates. Basis 2 gives 181/360, and basis 3 gives 181/365. The interval lies in the non-leap year 2025, so basis 1 also uses 365 here. For the first-of-month 30/360 examples, six months represent 180 convention-days, giving 180/360 = 0.5.
The difference is intentional: each basis asks a different question. Increasing display precision cannot make them agree.
Advanced application: a transparent simple-accrual model
Suppose a hypothetical agreement explicitly uses Actual/360, a principal of 10,000, and a simple annual rate of 6%. Put these in G2, H2, and I2 respectively as 10000, 6%, and 2. In J2 enter:
=G2*H2*YEARFRAC(A2,B2,I2)
Expected result: 301.6666666666667, displayed as 301.67 with two decimal places. The calculation is 10000*0.06*181/360. Using basis 0 instead gives 300; using basis 3 gives approximately 297.5342465753425.
This is an arithmetic illustration, not a determination of the correct terms for an actual contract. YEARFRAC supplies a year fraction; it does not choose contractual inclusions, compounding, coupon schedules, tax treatment, or rounding rules. Store the agreed basis in a labeled input and retain the unrounded calculation where an audit needs it.
Leap-year check
In a separate output cell enter =YEARFRAC(DATE(2024,1,1),DATE(2024,7,1),1). Expected result: 182/366, approximately 0.497267759562842. The corresponding basis-3 formula uses 182/365, approximately 0.498630136986301.
Both use the same 182 elapsed days. The denominator is what changes. This within-one-year example avoids extrapolating a detailed multi-year Actual/Actual implementation that the help page does not fully specify.
Date input pitfalls and troubleshooting
Use DATE or existing date cells. An unquoted expression such as 1/1/2025 inside a formula is arithmetic division, not a date literal. Google’s YEARFRAC notes explicitly warn about this distinction. For the practice data, DATE(2025,1,1) makes the intended year, month, and day unambiguous.
Do not use a naked difference between year numbers as a substitute. The two main dates are both in 2025, yet about half a year separates them. Conversely, dividing every day count by 365 ignores a chosen 360-day basis and leap-year behavior.
Check that the result is formatted as a number. A year fraction of 0.5 is neither six date serial days nor the text “six months.” Percentage formatting would show 50%, which may be suitable only if the report explicitly means a fraction of a year.
End-of-month dates, reversed dates, time-of-day fractions, and invalid basis values deserve focused target-workbook tests. The reviewed source does not provide a complete endpoint/error matrix. This guide deliberately does not assert undocumented exact error tokens or copy another engine’s month-end algorithm. For contractual calculations at those boundaries, verify the rule before relying on a result.
Availability and related guides
YEARFRAC is documented in current Google Sheets. Its introduction date and last functioning release are not established from these sources; no retirement is established. This documentation review does not certify native behavior in imported workbooks.
Related published guides: DATE, DAYS360, and DATEDIF. Keep the unit you need clear: fractional years, convention-days, and completed calendar intervals are different outputs.