DATEDIF: LibreOffice copies Excel's bug, then changes the error code

DATEDIF is the function Excel half-admits to having. It survives for Lotus 1-2-3 compatibility, and it does not surface in Excel's autocomplete or Insert Function browser the way an ordinary function does — but Microsoft does publish a support page for it, including an explicit warning that one of its arguments is unreliable. Google Sheets and LibreOffice Calc both document it in their function references. Our executed LibreOffice runs agree with Excel's documented values on the ordinary units, reproduce Excel's acknowledged "MD" bug exactly, and disagree on one thing only: which error code comes back when the end date precedes the start date.

Executed results

The Excel column is the documented-expected result recorded in our test corpus from Microsoft's DATEDIF support page; we did not run Excel. The LibreOffice column is what our harness computed by recalculating the workbook in LibreOffice Calc 25.8.7.3. Google Sheets has not yet been run through the harness, so that column stays empty rather than guessed. Four of the five rows match; only the last one forks.

Consistent across LibreOffice versions

All five cases were run in four builds — 24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3 — and every build produced the same output: 3, 41, 9, -1, and #VALUE!. Neither the reproduced "MD" result nor the error-code difference is a regression in one release; both are stable behaviour across every version we have executed.

Why the “MD” result is negative in both engines

"MD" is supposed to give the days between two dates ignoring months and years. Microsoft's own documentation warns against relying on it, because the day-of-month subtraction it performs can borrow from the wrong month. Reading the arithmetic off our case: the start day is 31, the end day is 1, and the borrow comes from February 2024's 29 days, giving 29 + 1 - 31 = -1. That is consistent with the -1 we measured. A naive reading of “days ignoring months” would have suggested a small positive number, which is exactly why the argument is a trap: the answer is not merely off, it is negative, and it can flow into a duration total without ever looking like an error.

The practical point for a migration project is the one people get backwards. Teams sometimes assume a move off Excel will clean up Excel's legacy quirks. Here it does not. LibreOffice implements the same day-borrowing behaviour and returns the same -1, so a workbook that was already silently wrong stays silently wrong — and stays wrong in a way that matches, which means a side-by-side comparison of the two files will not flag it either.

Why the error code differs

When the end date precedes the start date, Microsoft documents DATEDIF as raising #NUM!. Our LibreOffice runs returned #VALUE! instead, in all four builds. This is the same pattern we measured across a whole family of functions: LibreOffice funnels invalid-argument and out-of-domain conditions into a single #VALUE!, where Excel reserves #NUM! for numeric domain problems. See #NUM! vs #VALUE! domain errors for the broader set — this exact DATEDIF case appears there too.

The consequence lands on error handling, not on the happy path. A guard written as =IF(ERROR.TYPE(x)=6,"bad date range",x) keys on Excel's #NUM! code and simply never fires in LibreOffice, because the error arriving is #VALUE!. Worse, ERROR.TYPE itself is not portable: see ERROR.TYPE codes in Excel vs LibreOffice, where two of the seven documented codes come back as #N/A rather than a number.

How to migrate safely

Do not use "MD" at all. Both engines reproduce the same defect, so there is no target app in which the formula is correct. If you need days-within-the-month, compute it explicitly from the date parts you actually mean rather than asking DATEDIF to guess.

Prefer documented functions for the units they cover. A plain day count is =DAYS(end,start), which our function dataset records as documented in Excel, Google Sheets and LibreOffice, and which — unlike DATEDIF — you can actually find in Excel's function browser. Fractional years for finance and tenure calculations are =YEARFRAC(start,end,basis). Working-day counts are =NETWORKDAYS(start,end,holidays). Reserve DATEDIF for the whole-year and whole-month cases ("Y", "M") where our runs show the engines agreeing.

Guard the date order yourself, and guard it portably. Rather than catching an error whose identity changes, prevent it: =IF(end<start,"",DATEDIF(start,end,"D")) sidesteps the divergence entirely, because no error is raised for either engine to label. If you must catch, use the broad wrapper — =IFERROR(DATEDIF(start,end,"D"),"") catches #NUM! and #VALUE! alike — and avoid branching on ERROR.TYPE.

Honest limits

The Excel column is Microsoft's documented behaviour as recorded in our test corpus, not values we executed in Excel. Google Sheets is not yet run through our harness, so we say “not yet executed” rather than assert a result; all we record about Sheets here is that its function reference documents DATEDIF. The LibreOffice column is executed output from LibreOffice Calc 25.8.7.3, reproduced identically in our 24.2.0.3, 24.8.7.2 and 25.2.0.3 runs. The arithmetic offered for the "MD" result is an explanation consistent with the measured output; we have not inspected either engine's source to confirm the mechanism.

Check before you migrate