EDATE Function (LibreOffice Calc)
The EDATE function returns a date that is a specified number of months before or after a given date. It is the safest and most reliable way to perform month-based date arithmetic in LibreOffice Calc.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the EDATE Function Does ▾
- Adds or subtracts whole months from a date
- Automatically adjusts for month lengths
- Handles end-of-month edge cases
- Works with serial dates or text dates
- Returns a serial date number formatted as a date
It is designed to be robust, predictable, and ideal for month-based scheduling.
Syntax ▾
EDATE(start_date; months)
Arguments
-
start_date:
The date from which to offset. -
months:
Number of months to add (positive) or subtract (negative).
Basic Examples ▾
Add 3 months
=EDATE("2024-01-15"; 3)
Returns 2024‑04‑15.
Subtract 2 months
=EDATE("2024-05-10"; -2)
Returns 2024‑03‑10.
Using cell references
=EDATE(A1; B1)
Add 12 months (1 year)
=EDATE(A1; 12)
End‑of‑Month Behavior ▾
Add 1 month to January 31
=EDATE("2024-01-31"; 1)
Returns 2024‑02‑29 (leap year).
Add 1 month to February 29
=EDATE("2024-02-29"; 1)
Returns 2024‑03‑29.
Subtract 1 month from March 31
=EDATE("2024-03-31"; -1)
Returns 2024‑02‑29.
EDATE always tries to preserve the day number; if the target month is shorter, it clamps to the last valid day.
Advanced Examples ▾
Generate a monthly billing schedule
=EDATE($A$1; ROW(A1)-1)
Add months based on a dynamic value
=EDATE(A1; VALUE(B1))
Compute next quarter
=EDATE(A1; 3)
Compute previous quarter
=EDATE(A1; -3)
Compute fiscal year start (April 1)
=EDATE(DATE(YEAR(A1);4;1); IF(MONTH(A1)>=4;0;-12))
Add months to a date stored as text
=EDATE(DATEVALUE(A1); 6)
Add months to Excel serial dates imported as text
=EDATE(DATE(1899;12;30)+VALUE(A1); 1)
Create a rolling 12‑month forecast
=EDATE(TODAY(); 12)
Compute the same day next year
=EDATE(A1; 12)
Compute the same day last year
=EDATE(A1; -12)
Edge Cases and Behavior Details ▾
EDATE returns a number, not text
Accepts:
- Real dates
- Serial numbers
- DATEVALUE outputs
- ISO date strings
Invalid text → Err:502
Time components are ignored
=EDATE("2024-01-01 23:59"; 1) → 2024‑02‑01
Negative months allowed
Large offsets allowed
=EDATE("2024-01-01"; 1200) → year 2124
Leap-year aware
EDATE of an error → error propagates
Common Errors and Fixes ▾
Err:502 — Invalid argument
Cause:
- Text not recognized as a date
- Non-numeric month offset
Fix:
- Wrap with DATEVALUE
- Ensure months is numeric
Wrong result due to text dates
Fix:
- Normalize with DATEVALUE
Unexpected end-of-month behavior
Fix:
- Use EOMONTH if you want strict month-end logic
Best Practices ▾
- Use EDATE for all month-based offsets
- Use DATEVALUE to normalize text dates
- Use EOMONTH for month-end calculations
- Use EDATE(A1;12) for same-day-next-year logic
- Use EDATE with ROW() or SEQUENCE() to generate schedules
Related Patterns and Alternatives ▾
- Use EOMONTH for end-of-month offsets
- Use DATE for custom date construction
- Use DATEDIF for interval calculations
- Use DAYS for day differences
- Use YEARFRAC for fractional-year differences
By mastering EDATE and its companion functions, you can build powerful, reliable, and fully dynamic month‑based workflows in LibreOffice Calc.