YEARS in Collabora Office Calc: Count Complete Intervals or Calendar Boundaries

=YEARS(StartDate; EndDate; Type)

• StartDate: the first date, preferably a cell containing a real spreadsheet date value or a DATE expression.
• EndDate: the second date. Reversing the dates produces a signed difference.
• Type: required. Use 0 for completed interval years, or 1 for calendar years. Do not omit this argument.

The formulas use English function names and semicolon argument separators. Adapt these to your formula settings; do not type square brackets used to mark optional arguments. Put input data in a source column and formulas in a separate result column so you can inspect both.

Use DATE(year;month;day) rather than ambiguous text such as 3/4/26. Format the result cell as Number or General so an integer count is not displayed as a date.

Mode 0 first computes the signed whole-month interval using MONTHS with mode 0, then divides by 12 using integer truncation toward zero. Thus 23 whole months gives 1 year, and −23 whole months gives −1 year, not −2. Mode 1 simply subtracts the start year number from the end year number. Pinned Collabora date implementation.

Neither mode returns a fractional period. Calendar mode measures changes in calendar labels, not the number of named periods touched inclusively. Equal dates give 0 in both modes. The difference is not computed by dividing elapsed days by 365.

Enter the dates in columns A and B using DATE expressions. In C2 enter =YEARS(A2;B2;0), and in D2 enter =YEARS(A2;B2;1). Fill both formulas down.

For the first row, the completed month count is 11, and truncating 11/12 gives zero. Being the last day of February does not automatically make the anniversary complete.

=YEARS(DATE(2026;6;10);DATE(2024;7;10);0) returns −1. The completed-month difference is −23, and integer division truncates toward zero.

A negative answer can be useful for an intentionally reversed comparison. If your workflow requires chronological dates, validate that EndDate is at least StartDate instead of hiding the sign with ABS.

A service record starts July 10, 2024, with a reporting date of June 10, 2026. Put =DATE(2024;7;10) in A2 and =DATE(2026;6;10) in B2. Enter =YEARS(A2;B2;0) in C2: the expected result is 1. In D2, =IF(C2>=2;"Two-year milestone reached";"Not yet") returns Not yet.

Changing B2 to =DATE(2026;7;10) gives 2 and Two-year milestone reached. Mode 1 would give 2 even in June because it only subtracts year numbers. Choose the mode that matches your actual policy; this example is a reporting calculation, not a legal definition of age, seniority or benefits eligibility.

• Type values other than 0 or 1 are rejected by the implementation. Use those integer values explicitly rather than relying on coercion of text or decimal modes.
• All three arguments are required. A missing mode is not a documented default.
• Date-looking text may not be a valid date value under the active locale. Check inputs with DATE-based examples before debugging the interval rule.
• For day-based reporting, use date-only inputs; these add-in functions take integer date arguments, not elapsed-hour durations. Do not use them to promise time-of-day precision.
• Month-end and leap-day policies require special attention. February 29, 2024 to February 28, 2025 gives zero complete years under this rule; March 1 gives one. If your business policy differs, implement that policy explicitly.
• YEAR extracts a year number from one date; YEARS compares two dates. These functions are not interchangeable.
• The Collabora Help identifies this as ORG.OPENOFFICE.YEARS, outside the ODF 1.3 OpenFormula standard. Do not assume an exported workbook has equivalent recalculation in every spreadsheet product.

For related date construction see DATE; for extracting a date component see YEAR.

Documented in a Collabora Office 26.04 Help snapshot retrieved October 17, 2026, from the linked latest Help pages, and reviewed October 19, 2026, against the Collabora co-26.04 source commit linked below. Fresh retrieval attempts for the versioned 26.04 Help pages returned HTTP 503, so the documentation evidence is the saved snapshot rather than a successful fresh retrieval. The first release that introduced the function is not established here. Online, mobile and later desktop deployments may differ; confirm function availability and settings in the actual client.

Checked October 19, 2026. The expected answers were independently calculated from documented rules and the pinned implementation. These are not results captured from a running Collabora application; native execution and screenshots were unavailable. No screenshot is presented as evidence.

• Collabora Office YEARS Help (26.04 snapshot, retrieved October 17, 2026)
• Pinned Collabora implementation