DATE Function (LibreOffice Calc)

• Creates a valid date from year, month, day
• Automatically normalizes overflow values
• Supports negative and zero month/day offsets
• Enables dynamic date arithmetic
• Returns a serial date number formatted as a date

It is designed to be robust, predictable, and ideal for all calendar-based workflows.

DATE(year; month; day)

Arguments

• year:
  Integer representing the year (0–9999).
  Values 0–1899 are interpreted literally.
  Values 1900–9999 are interpreted as full years.

• month:
  Integer representing the month.
  Can overflow (e.g., 13 = January of next year).

• day:
  Integer representing the day.
  Can overflow (e.g., 32 = next month).

Construct a date

=DATE(2024; 5; 10)

Returns 2024‑05‑10.

Overflow month (13 → next year)

=DATE(2024; 13; 1)

Returns 2025‑01‑01.

Overflow day (32 → next month)

=DATE(2024; 1; 32)

Returns 2024‑02‑01.

Zero or negative month offsets

=DATE(2024; 0; 15)

Returns 2023‑12‑15.

Zero or negative day offsets

=DATE(2024; 3; 0)

Returns 2024‑02‑29 (day 0 = last day of previous month).

Add months safely

=DATE(YEAR(A1); MONTH(A1)+B1; DAY(A1))

Equivalent to EDATE but manual.

Last day of month (classic pattern)

=DATE(YEAR(A1); MONTH(A1)+1; 0)

First day of next month

=DATE(YEAR(A1); MONTH(A1)+1; 1)

First day of current month

=DATE(YEAR(A1); MONTH(A1); 1)

Add days to a date

=DATE(YEAR(A1); MONTH(A1); DAY(A1)+B1)

Build a date from text components

=DATE(VALUE(A1); VALUE(B1); VALUE(C1))

Convert week number + weekday to a date

=DATE(A1; 1; 1) + (B1-1)*7 + C1 - WEEKDAY(DATE(A1;1;1);2)

Create a date from ISO year-week-day

=DATE(A1;1;4) - WEEKDAY(DATE(A1;1;4);2) + (B1-1)*7 + C1

Generate a monthly calendar grid

=DATE($A$1; $B$1; 1) - WEEKDAY(DATE($A$1; $B$1; 1); 2) + ROW()*7 + COLUMN()

Build dynamic fiscal year boundaries

=DATE(YEAR(A1)+(MONTH(A1)>=4); 4; 1)

Convert Excel serial date to Calc date

=DATE(1899;12;30) + A1

DATE normalizes overflow automatically

• Month 0 → previous December
• Month 13 → next January
• Day 0 → last day of previous month
• Day 32 → next month

DATE returns a serial number

=TYPE(DATE(2024;1;1)) → 1 (number)

Year interpretation

• 0–1899 → literal
• 1900–9999 → literal
• No two‑digit year shorthand (unlike Excel)

Invalid results

• DATE with year < 0 → Err:502
• DATE with year > 9999 → Err:502

Leap-year handling

=DATE(2024; 2; 29) → valid  
=DATE(2023; 2; 29) → becomes 2023‑03‑01  

DATE of an error → error propagates

Locale affects display, not value

Underlying serial number is universal.

• Use DATE for all date construction
• Use overflow behavior intentionally for offsets
• Use DATE(YEAR(); MONTH()+n; DAY()) for safe month arithmetic
• Use DATE(YEAR(); MONTH()+1; 0) for end‑of‑month logic
• Use DATE with VALUE() when parsing text components
• Use DATE instead of manually adding serial numbers

• Use DATEVALUE to convert text to dates
• Use EDATE and EOMONTH for month offsets
• Use TODAY and NOW for dynamic dates
• Use YEAR, MONTH, DAY to extract components
• Use DATEDIF for interval calculations

By mastering DATE and its companion functions, you can build powerful, reliable, and fully dynamic date workflows in LibreOffice Calc.