MONTHS Function (LibreOffice Calc)
The MONTHS function returns the number of whole months between two dates. It is essential for billing cycles, subscription logic, amortization schedules, HR calculations, and any workflow requiring precise month-based differences.
Compatibility
▾| Excel | ✖ |
| Gnumeric | ✔ |
| Google_sheets | ✖ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✖ |
| Wps | ✔ |
| Zoho | ✔ |
What the MONTHS Function Does ▾
- Returns the whole number of months between two dates
- Accepts dates, datetimes, and serial numbers
- Can return positive or negative values
- Ignores partial months (no rounding)
It is designed to be precise, predictable, and ideal for month‑interval calculations.
Syntax ▾
MONTHS(start_date; end_date; [mode])
Arguments
-
start_date:
The beginning date. -
end_date:
The ending date. -
mode (optional):
0(default): Count whole months only1: Count partial months as whole months (ceiling behavior)
Basic Examples ▾
Whole months between two dates
=MONTHS("2024-01-01"; "2024-04-01")
Returns 3.
Months between dates with partial month ignored
=MONTHS("2024-01-15"; "2024-04-10")
Returns 2.
Months between dates counting partial months
=MONTHS("2024-01-15"; "2024-04-10"; 1)
Returns 3.
Using cell references
=MONTHS(A1; B1)
Advanced Examples ▾
Months between today and a future date
=MONTHS(TODAY(); A1)
Months between two text dates
=MONTHS(DATEVALUE(A1); DATEVALUE(B1))
Months between imported CSV timestamps
=MONTHS(DATEVALUE(LEFT(A1;10)); DATEVALUE(LEFT(B1;10)))
Months between Excel serial dates stored as text
=MONTHS(DATE(1899;12;30)+VALUE(A1); DATE(1899;12;30)+VALUE(B1))
Determine if a subscription is older than 6 months
=MONTHS(A1; TODAY()) >= 6
Determine if a date is within the same month
=MONTHS(A1; B1) = 0
Calculate monthly billing cycles
=BaseRate * MONTHS(StartDate; EndDate)
Calculate months remaining in the year
=MONTHS(TODAY(); DATE(YEAR(TODAY());12;31))
Calculate age in months
=MONTHS(A1; TODAY())
Edge Cases and Behavior Details ▾
MONTHS returns an integer
Accepts:
- Real dates
- Serial numbers
- DATEVALUE outputs
- ISO date strings
Invalid text → Err:502
Behavior details
- Partial months are ignored unless mode = 1
- Negative results allowed
- Time components are ignored
- Date order matters
MONTHS 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
Wrong month count due to partial month
Fix:
- Use mode = 1 to count partial months
Unexpected result from serial numbers
Cause:
- Imported values represent days since epoch
Fix:
- Convert with DATE(1899;12;30)+VALUE(A1)
Best Practices ▾
- Use MONTHS for billing, subscriptions, and amortization
- Normalize text dates with DATEVALUE
- Use mode = 1 when partial months should count
- Use MONTHS with EDATE for rolling schedules
- Use MONTHS for age‑in‑months calculations
- Use MONTHS for monthly reporting periods
MONTHS is your month‑interval engine — perfect for billing cycles, subscription logic, amortization schedules, and any workflow where month‑based differences matter.
Related Patterns and Alternatives ▾
- Use DATEDIF for more granular intervals
- Use EDATE for month offsets
- Use EOMONTH for month boundaries
- Use YEAR and MONTH for component extraction
- Use DATEVALUE for text conversion
By mastering MONTHS and its companion functions, you can build powerful, reliable, and fully structured month‑interval workflows in LibreOffice Calc.