Can I Spreadsheet?
← All functions

YEARFRAC

Quirk found

Category: Date and time · Last tested 2026-07-04

Support matrix

EngineDocumentedLive-testedVerdict
Excel Yes Not yet n/a
Google Sheets Yes Not yet n/a
LibreOffice Calc Yes Yes (24.2.7.2, 2026-07-04) Quirk found

Discovered quirks

Executed test cases

LibreOffice Calc 24.2.7.2 (tested 2026-07-04)

FormulaDescriptionResultExpectedVerdict
=YEARFRAC(DATE(2012,1,1),DATE(2012,7,30)) Default basis 0 (US/NASD 30/360): treats every month as 30 days. Days = 6*30+29 = 209, over a 360-day year -> 209/360. Source: https://support.microsoft.com/en-us/office/yearfrac-function-3844141e-c76d-4143-82b6-208454ddc6a8 (worked example rounds to 0.58055556) 0.580555555555556 0.5805555555555556 Matched
=YEARFRAC(DATE(2012,1,1),DATE(2012,7,30),1) basis 1 (Actual/actual): actual 211 calendar days between the two dates, divided by 366 since the enclosing 1-year span (Jan 2012-Jan 2013) is a leap year. Source: Microsoft YEARFRAC docs worked example rounds to 0.57650273. 0.576502732240437 0.5765027322404372
211/366 = 0.5765027322404372, matching Microsoft's documented rounded example (0.57650273) to 8 decimal places. Actual/actual leap-year-boundary handling is a documented high-divergence area across spreadsheet engines.		 Matched
=YEARFRAC(DATE(2012,1,1),DATE(2012,7,30),2) basis 2 (Actual/360): actual 211 calendar days divided by a fixed 360-day year. Source: Microsoft YEARFRAC docs basis table. 0.586111111111111 0.5861111111111111
211/360 = 0.5861111111111111.		 Matched
=YEARFRAC(DATE(2012,1,1),DATE(2012,7,30),3) basis 3 (Actual/365): actual 211 calendar days divided by a fixed 365-day year. Source: Microsoft YEARFRAC docs worked example rounds to 0.57808219. 0.578082191780822 0.5780821917808219
211/365 = 0.5780821917808219, matches Microsoft's documented rounded example exactly.		 Matched
=YEARFRAC(DATE(2012,1,1),DATE(2012,7,30),4) basis 4 (European 30/360). For this particular date pair neither day-of-month is the 31st, so the European day-count adjustment never triggers and the result equals basis 0's 209/360. 0.580555555555556 0.5805555555555556
European 30/360 only differs from US 30/360 when a start/end day-of-month is 31; with day 1 and day 30 as here, both conventions compute 209/360 identically.		 Matched
=YEARFRAC(DATE(2012,1,1),DATE(2012,7,30),9) basis values outside 0-4 are documented as invalid. #VALUE! #NUM!
Microsoft docs: 'invalid basis values (< 0 or > 4) return a #NUM! error.'		 Mismatch

Docs & syntax