YEARFRAC Function (LibreOffice Calc)
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.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
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.
Related Patterns and Alternatives ▾
- 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.