YEARFRAC in Collabora Office: Compare Fractional Years Across Day-Count Bases
Use Collabora YEARFRAC with all five documented bases, audit a leap-year interval, and compare year fractions for the same dates under different bases.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =YEARFRAC(DATE(2024;1;1);DATE(2024;7;1);3) has independently expected result 182/365, approximately 0.498630137. Basis 3 counts actual days and divides by a 365-day year.
Syntax and the five bases
=YEARFRAC(StartDate; EndDate [; Basis])
StartDate and EndDate are required date values. Basis determines the year-fraction convention:
| Basis | Documented calculation |
|---|---|
| 0 or omitted | US NASD, twelve 30-day months |
| 1 | Actual days in the months and actual days in the year |
| 2 | Actual days, with a 360-day year |
| 3 | Actual days, with a 365-day year |
| 4 | European method, twelve 30-day months |
The default is basis zero, not actual/actual. Always supply Basis when the convention matters to a report or calculation.
Use DATE or genuine numeric date cells. A bare 2024-1-1 is an arithmetic expression. Although the help discusses quoted ISO dates, text conversion can depend on detailed calculation settings; DATE gives a clearer reproducible input.
Example: audit one leap-year interval
Put =DATE(2024;1;1) in A2 and =DATE(2024;7;1) in B2. Put basis numbers 0, 1, 2, 3, 4 in D2:D6. In E2 enter =YEARFRAC($A$2;$B$2;D2) and fill down.
| Basis | Independent calculation | Expected fraction |
|---|---|---|
| 0 | 180/360 | 0.500000000 |
| 1 | 182/366 | 0.497267760 |
| 2 | 182/360 | 0.505555556 |
| 3 | 182/365 | 0.498630137 |
| 4 | 180/360 | 0.500000000 |
There are 182 actual elapsed days in this interval, and 2024 contains 366 days. The 30-day-month conventions instead represent the six-month span as 180 days. No single row is universally the “correct” fraction without choosing the intended convention.
For an independent calendar-day check, =B2-A2 should correspond to 182. Do not add one to include both endpoints unless your own model explicitly requires that different counting convention.
Reproduce the vendor’s fixed control
The help asks for the fraction between January 1 and July 1, 2008, under basis zero. Written with explicit date constructors:
=YEARFRAC(DATE(2008;1;1);DATE(2008;7;1);0)
The documented result is 0.50. This is a basis-zero control and should not be relabeled as proof that actual/actual always gives exactly one-half for those dates in a leap year.
Advanced example: allocate a hypothetical annual amount
Suppose a planning model prorates an annual amount of 12000 using the January-to-July 2024 interval. In F2 use =12000*E2 and fill down through F6.
The independent amounts are 6000, approximately 5967.213114754, 6066.666666667, 5983.561643836, and 6000 for bases 0–4. Keep the basis label beside each result rather than displaying an unexplained amount.
This is a transparent arithmetic illustration, not a decision about an employment, loan, tax, or other agreement’s required convention. A real agreement may specify its own treatment. Apply ROUND at the reporting stage only after deciding the required precision and convention.
Advanced comparison: fraction of a year versus completed years
A value such as 0.498630137 is a fractional year under a specified basis. It is not a count of completed birthdays, completed calendar years, or completed months. DATEDIF addresses different elapsed-unit questions according to its own unit argument.
Multiplying YEARFRAC by 12 does not automatically produce a count of completed months. Likewise, treating every result as an exact fraction of 365 days is incompatible with the other documented bases.
Errors, boundaries, and troubleshooting
The dedicated section summarizes each basis but does not fully specify multi-year actual/actual denominator choices or every NASD February/month-end adjustment. This guide’s comparison stays within one known leap year and uses first-of-month endpoints. It does not infer another product’s boundary behavior.
Check that the dates are numeric, Basis is one of 0–4, and the displayed result is formatted as a number or percentage rather than a date. The help discusses #VALUE! in connection with text conversion; native invalid-basis, text-coercion, and boundary error details are not comprehensively established here.
File-format scope
The vendor identifies YEARFRAC as part of OpenDocument 1.2. Standards membership does not establish a Collabora introduction release or certify identical behavior in every other spreadsheet program.
Availability and verification
The exact function is documented in Collabora Office 26.04 desktop help, retrieved and reviewed on October 17, 2026. This establishes a documentation scope, not the first release that introduced the function or the last version in which it works; those versions and any retirement date remain unestablished. Collabora Online and mobile support are not inferred from desktop help, nor is behavior in another spreadsheet product.
The examples are documentation-based expectations with independent calculations. They have not been executed in Collabora Office. No application screenshot or native error/coercion certification is implied. Formulas use English function names and semicolon argument separators.
References
- Collabora Office 26.04: YEARFRAC (checked October 17, 2026).
- Editorial testing standards.