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 desktop Excel. The LibreOffice column is what our harness computed by recalculating the workbook in LibreOffice Calc 25.8.7.3. Google Sheets was executed on 2026-08-29 via Drive import and matches Excel’s documented value on all five rows — the acknowledged “MD” result included. Four of the five LibreOffice rows match; only the last one forks, and it forks away from both other engines.

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 every engine

"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. Every engine we execute reproduces the same defect — Google Sheets, all four LibreOffice builds, and Excel for the web, which returned -1 for =DATEDIF(DATE(2024,1,31),DATE(2024,3,1),"MD") when we recalculated the corpus on OneDrive on 2026-09-01 — 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. The Google Sheets column is executed output from a Drive import on 2026-08-29; before that run all we recorded about Sheets was 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

A note on which Excel this is. The Excel column in the tables above is Microsoft’s documented behaviour for desktop Excel, as recorded in our test corpus — we do not run desktop Excel, and no value in that column is a measurement. Excel for the web is a different application with its own calculation engine, and that one we do run (recalculated on OneDrive, 2026-09-01). Its measured results are published on each function’s own page rather than in these guide tables. Because we have no desktop run to compare against, a disagreement between an Excel-web measurement and the documented column is genuinely ambiguous: it may mean the web engine diverges from the desktop one, or that the documentation is wrong about both. We do not claim to know which.