I have a problem with the DATEDIF function
The help file states the following
“This function returns the number of whole days, months or years between Start date and End date”.
The optimum word being “between”
If asked the question “how many number are there between 1 and 5”? I would say three I.e. the numbers 2,3 and 4.
However the function below returns the number 4.
This does not make sense to me or am I missing something?
Any thoughts?
Thanks
Colin
=DATEDIF("2022-04-1","2022-04-5","d")
Okay, let’s count together.
So, 2022-04-1 is actually 2022-04-01 00:00, midnight, the moment when March 31 became April. Once 24 hours and we get April 2, again (second) 24 hours - April 3, repeat (third time) 24 hours - April 4. And finally add 24 hours for the last, fourth time and get midnight when 2022-04-05 starts.
What have I done wrong?
the function returns the duration in whole days between 2022-04-01 00:00 and 2022-04-05 00:00 … but NOT the count of days (exclusively between)
The difference between 2 “date values” is always the count of days.
=B1-A1 returns the difference in days between the values in A1 and B1.
=“2022-04-5”-“2022-04-1” does the same with implicit conversion of ISO date strings. This does not work with other date strings because they would be ambiguous.
=DAYS(B1;A1) is another (IMHO obsolete) function returning the same number of days.
In fact there are no date values in spreadsheets. Dates are serial day numbers displayed as dates.
=“2022-04-5”-“2022-04-1” calculates 44656-44652 (day numbers since day zero 1899-12-30).
=MONTH(44656) returns 4 (April), =DAY(44656) returns 5 and =YEAR(44656) returns 2022 because 44656 is the exact same value as the formatted number 2022-04-05.
That’s simply “as is”. Terms like “between” and “from … through” never were bindingly defined concerning the question whether lower and/or upper limit should be included. Beyond that fact they are generally used in a sloppy way not even checking if the context would allow for an unambiguous understanding. Since ordinary words hardly can express related facts sufficiently we should resort to mathematical inequations.
Doing so for a different example:
RANDBETWEEN(Z1; Z2), Z1 and Z2 assumed integer, will return one of the integer numbers N fulfilling Z1<=N<=Z2 (including both limits) which allows for a range of Z2-Z1+1 different values (if Z2>=Z1). DATEDIF() is including one of the limits without needing to tell which one, and the name is consistent with this behaviour - except for the absurd abbreviation “DIF” for “difference”.
I don’t know a case in our context where both limits are excluded - but there may well be one.
My advice: Decide based on a sufficient set of examples in every case. Help texts try to use “ordinary words” and will always fail to be precise.
To many words?
Non. Je ne regrette rien. That’s languages. I can’t help it.