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 ...
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!