Forum Discussion
DATEDIF FUNCTION
Hi,
I'm wondering how does the DATEDIF FUNCTION calculates when using "Y" unit and "M" unit. Sometimes this function may result in a wrong number.
For example, I used this function to calculate month between 2000/01/31 and 2000/02/29 and the result was 0. But in fact the result should be 1 month, because the start date is the end of January and the end date is the end of February.
I want to figure out why this error happened.
Koi16 From the MS support pages on DATEDIF:
"M" calculates whole months based on day numbers. If the day number of the end date is less then the day number of the start date you get unexpected results. In your case, 29 is less then 31 thus you get zero, even though we all know that it's exactly one calendar month that has passed. But what's "one calendar month"? That may be 28, 29, 30 or 31 days.
Better to stay away from DATEDIF altogether and just count the number of days (end - start), divide by 30.42 (average number of days per month) and round if desired.
- Thanks a lot for your answer!
1) DATEDIF is a weird function. For many years, it wasn't officially documented by Microsoft. It is now, but with a warning that the result is not always accurate: DATEDIF function
2) Date calculations involving the last days of a month are always problematic:
It's reasonable to say that from February 20, 2023 to March 20, 2023 is exactly 1 month, since both dates are on the same day of the month.
And from February 20, 2023 to March 21, 2023 is 1 month and 1 day.
Following the same reasoning, from February 28, 2023 to March 28, 2023 is 1 month.
From February 28, 2023 to March 29, 2023 is one day more, so 1 month and 1 day.
From February 28, 2023 to March 30, 2023 is 1 month and 2 days.
From February 28, 2023 to March 31, 2023 is 1 month and 3 days.
But February 28 and March 31 are both the last day of their month, so it is also reasonable to say that it is exactly 1 month and 0 days.
There is no one single solution that will satisfy everybody...
- Thanks for your reply!
