Browse

DATEDIF Function (LibreOffice Calc)

Date & Time Intermediate LibreOffice Calc LibreOffice 3.0

The DATEDIF function calculates the difference between two dates in years, months, days, or mixed units. It is a legacy function inherited from Lotus 1-2-3 and remains essential for precise interval calculations.

The **DATEDIF** function calculates the difference between two dates in years, months, days, or mixed units. Although it is a legacy function inherited from Lotus 1‑2‑3 and hidden from the function wizard, it remains one of the most powerful tools for interval calculations such as age, tenure, durations, and calendar offsets.

This guide provides syntax, examples, interval types, edge cases, and best practices, making it one of the most complete DATEDIF references available for LibreOffice Calc.

What the DATEDIF Function Does

  • Calculates the difference between two dates
  • Supports years, months, days, and mixed-unit intervals
  • Handles partial intervals cleanly
  • Works even though it is hidden from the function wizard
  • Returns integer values only

It is designed to be precise, reliable, and ideal for age, tenure, and duration calculations.

Syntax

DATEDIF(start_date; end_date; unit)

Arguments

  • start_date:
    The earlier date.

  • end_date:
    The later date.
    Must be ≥ start_date.

  • unit:
    A text code specifying the interval type.

Supported Units

Unit Meaning Description
“Y” Years Full years between dates
“M” Months Full months between dates
“D” Days Total days between dates
“YM” Months Months ignoring years
“YD” Days Days ignoring years
“MD” Days Days ignoring months and years

Basic Examples

Days between two dates

=DATEDIF("2024-01-01"; "2024-01-31"; "D")

Returns 30.

Full months between dates

=DATEDIF("2024-01-01"; "2024-04-01"; "M")

Returns 3.

Full years between dates

=DATEDIF("2000-05-10"; "2024-05-10"; "Y")

Returns 24.

Calculate age in years

=DATEDIF(A1; TODAY(); "Y")

Calculate age in years + months

=DATEDIF(A1; TODAY(); "Y") & " years, " &
DATEDIF(A1; TODAY(); "YM") & " months"

Advanced Examples

Age in full years, months, and days

=DATEDIF(A1; TODAY(); "Y") & "y " &
DATEDIF(A1; TODAY(); "YM") & "m " &
DATEDIF(A1; TODAY(); "MD") & "d"

Months ignoring years

=DATEDIF("2024-01-15"; "2025-03-10"; "YM")

Returns 2.

Days ignoring years

=DATEDIF("2024-02-20"; "2025-03-10"; "YD")

Returns 19.

Days ignoring months and years

=DATEDIF("2024-01-28"; "2024-02-05"; "MD")

Returns 8.

Tenure calculation (years + months)

=DATEDIF(A1; B1; "Y") + DATEDIF(A1; B1; "YM")/12

Billing cycle calculation

=DATEDIF(A1; B1; "M") + (DATEDIF(A1; B1; "MD")>0)

Compute next anniversary

=DATE(YEAR(TODAY()); MONTH(A1); DAY(A1))

If already passed:

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

Compute number of full quarters

=INT(DATEDIF(A1; B1; "M")/3)

Edge Cases and Behavior Details

DATEDIF requires end_date ≥ start_date

If not → Err:502.

DATEDIF returns integer values only

No decimals.

DATEDIF ignores time components

Time is truncated.

Units “YM”, “YD”, “MD” have special behavior

  • “YM”: months after removing full years
  • “YD”: days after removing full years
  • “MD”: days after removing full months and years

Leap-year behavior

=DATEDIF("2024-02-29"; "2025-02-28"; "Y") → 0  
=DATEDIF("2024-02-29"; "2025-02-28"; "D") → 365

DATEVALUE recommended for text inputs

DATEDIF of an error → error propagates

Common Errors and Fixes

Err:502 — Invalid argument

Occurs when:

  • end_date < start_date
  • unit is invalid
  • dates are non-numeric

Fix:

  • Ensure correct order
  • Use valid unit codes
  • Convert text with DATEVALUE

Wrong result due to text dates

Fix:

  • Wrap with DATEVALUE
  • Clean text with TRIM or SUBSTITUTE

Unexpected “MD” results

Cause:

  • “MD” ignores months and years entirely

Fix:

  • Use “D” or “YD” depending on intent

Best Practices

  • Use DATEDIF for age, tenure, and interval calculations
  • Use DATEVALUE to normalize text dates before DATEDIF
  • Use “Y”, “M”, “D” for simple intervals
  • Use “YM”, “YD”, “MD” for mixed-unit calculations
  • Combine multiple DATEDIF calls for full Y/M/D breakdown
  • Validate date order before using DATEDIF
DATEDIF is your precision interval engine — perfect for age calculations, durations, and any workflow requiring exact calendar differences.
  • Use DAYS for simple day differences
  • Use NETWORKDAYS for business-day differences
  • Use EDATE and EOMONTH for month offsets
  • Use YEARFRAC for fractional-year differences
  • Use DATE for constructing comparison dates

By mastering DATEDIF and its companion functions, you can build powerful, accurate, and fully dynamic date‑interval workflows in LibreOffice Calc.