Who Needs DATEDIF() ?? Revisited.
Harlan Grove added some comments regarding the Excel DATEDIF() function, that I feel deserve an article of their own…
Excellent example of POOR DESIGN in OOo Calc.
YEARS(EarlierDate;LaterDate;)
MONTHS(EarlierDate;LaterDate;)
but
DAYS(LaterDate;EarlierDate) [and no argument]
Maybe this was the fault of the original StarDivision programmers, but it’s still stupid, and it makes a bad impression for OOo Calc. Maybe it looks like this because the FAR SIMPLER equivalent formula
=LaterDate-EarlierDate
puts LaterDate before EarlierDate.
The only excuse for not fixing this is backwards compatibility: extant design errors must remain so that formulas adapted to those design errors continue to work.
Anyway, the more useful DATEDIF characteristics are YM, YD and MD. Those are more difficult to manage in OOo Calc. Actually, YM is simple.
=MOD(YEARS(EarlierDate;LaterDate;0);12)
YD takes more work.
=DATE(YEAR(EarlierDate)+(TEXT(EarlierDate;”mmdd”)>TEXT(LaterDate;”mmdd”));
MONTH(LaterDate);DAY(LaterDate))-EarlierDate
MD is more difficult.
=DATE(YEAR(EarlierDate);
MONTH(EarlierDate)+(DAY(EarlierDate)>DAY(LaterDate));
DAY(LaterDate))-$A3
is arguably more accurate, but it doesn’t produce the same results as Excel. Excel, for good or ill, NEVER returns anything higher than 29 for DATEDIF(EarlierDate,LaterDate,”MD”) while it can produce screwy results for 5 or fewer. Excel’s DATEDIF(EarlierDate,LaterDate,”MD”) may produce results consistent with DAYS360, but I’ll leave that to someone else to check.
Thanks Harlan for taking the time to comment on this topic!