Contact
12 programs
Explore the site

YEARFRAC Function (LibreOffice Calc)

Date & Time Advanced LibreOffice Calc Introduction not established

The YEARFRAC function returns the fractional number of years between two dates, using selectable day-count conventions. It is essential for finance, prorating, interest calculations, HR, insurance, and any workflow requiring precise year-based fractions.

Verification status: Consult the documentation, examples and any stated test scope below. How formula examples are checked

Availability and version history

  • Introduced: Documented by LibreOffice 26.8.1.1; original introduction not established.
  • Availability: Supported in LibreOffice 26.8.1.1, verified against its function registry. This identifies a supported release, not a final working version.
  • Scope: Desktop Calc, using the English function name. Availability in other spreadsheet applications is separate.
  • Checked: 2026-10-14 — function reference; versioned support. Documentation/source check; not a new native test. Existing example-specific test claims below retain their stated scope.
The **YEARFRAC** function returns the **fractional number of years** between two dates. It supports multiple day-count conventions used in finance, accounting, insurance, HR, and prorating workflows. YEARFRAC is essential when whole years (YEARS) or whole months (MONTHS) are not precise enough.

What the YEARFRAC Function Does

  • Returns a decimal number representing years between two dates
  • Supports multiple day-count basis systems
  • Works with dates, datetimes, and serial numbers
  • Ideal for interest calculations, prorating, and actuarial logic

It is designed to be precise, flexible, and finance‑grade.

Syntax

YEARFRAC(start_date; end_date; [basis])

Arguments

  • start_date:
    The beginning date.

  • end_date:
    The ending date.

  • basis (optional):
    Determines the day‑count convention.

Day‑Count Basis Options

Basis Convention Description
0 US 30/360 Months have 30 days; year has 360 days (US/NASD)
1 Actual/Actual Uses actual days in months and year
2 Actual/360 Actual days, 360‑day year
3 Actual/365 Actual days, 365‑day year
4 European 30/360 30‑day months, 360‑day year (European method)

Basic Examples

Fractional years between two dates (Actual/Actual)

=YEARFRAC("2024-01-01"; "2024-07-01"; 1)

Fractional years using default basis (US 30/360)

=YEARFRAC("2024-01-01"; "2024-07-01")

Fractional years between cell values

=YEARFRAC(A1; B1)

Fractional years from text dates

=YEARFRAC(DATEVALUE(A1); DATEVALUE(B1))

Advanced Examples

Age in fractional years

=YEARFRAC(A1; TODAY(); 1)

Prorate an annual cost

=AnnualCost * YEARFRAC(StartDate; EndDate; 1)

Interest calculation (Actual/360)

=Principal * Rate * YEARFRAC(A1; B1; 2)

Interest calculation (Actual/365)

=Principal * Rate * YEARFRAC(A1; B1; 3)

Bond/loan calculations (30/360 European)

=YEARFRAC(A1; B1; 4)

Fractional service duration

=YEARFRAC(HireDate; TODAY(); 1)

Fractional years from imported CSV timestamps

=YEARFRAC(DATEVALUE(LEFT(A1;10)); DATEVALUE(LEFT(B1;10)); 1)

Fractional years from Excel serial dates stored as text

=YEARFRAC(DATE(1899;12;30)+VALUE(A1); DATE(1899;12;30)+VALUE(B1); 1)

Edge Cases and Behavior Details

YEARFRAC returns a decimal number

Accepts:

  • Real dates
  • Serial numbers
  • DATEVALUE outputs
  • ISO date strings

Invalid text → Err:502

Behavior details

  • Negative results allowed
  • Time components are ignored
  • Basis dramatically affects results
  • Actual/Actual uses leap‑year logic

YEARFRAC of an error → error propagates

Common Errors and Fixes

Err:502 — Invalid argument

Cause:

  • Text not recognized as a date
  • Non-numeric values
  • Wrong separators

Fix:

  • Wrap with DATEVALUE
  • Clean text with TRIM or SUBSTITUTE

Unexpected fractional value

Cause:

  • Wrong basis selected

Fix:

  • Choose correct basis for your industry

Incorrect results from serial numbers

Fix:

  • Convert with DATE(1899;12;30)+VALUE(A1)

Best Practices

  • Use basis = 1 (Actual/Actual) for most general workflows
  • Use basis = 2 or 3 for interest calculations
  • Use basis = 0 or 4 for bonds and accounting systems
  • Normalize text dates with DATEVALUE
  • Use YEARFRAC for prorating and actuarial logic
  • Use YEARS or MONTHS when whole units are required
YEARFRAC is your precision fractional‑year engine — perfect for finance, prorating, actuarial calculations, and any workflow where exact year fractions matter.
  • Use YEARS for whole years
  • Use MONTHS for whole months
  • Use DATEDIF for flexible interval logic
  • Use DAYS for day differences
  • Use DAYSINYEAR for custom prorating
  • Use DATEVALUE for text conversion

By mastering YEARFRAC and its companion functions, you can build powerful, finance‑grade, and fully precise date‑interval workflows in LibreOffice Calc.