Forum Discussion

Copper Contributor

Jan 13, 2021

The parameter "yd" of the datedif function

start_date end_date datedif=>yd 1997/9/1 2000/7/1 =DATEDIF(a2, b2, "yd") => 303 2003/9/1 2021/7/1 =DATEDIF(a3, b3, "yd") => 304 first: 1997/9/1 =》1999/9/1， 1999/9/1 =〉 2000/7/...

excel

JMB17

Bronze Contributor

Jan 13, 2021

tomorrow_star

That's interesting, I've never noticed it before. I don't know for sure, but just an observation - perhaps the algorithm is counting the days from 1997/9/1 > 1998/7/1 (and excluding the whole years from 1998/7/1 > 2000/7/1). And counting days from 2003/9/1 > 2004/7/1 (excluding whole years from 2004/7/1 > 2021/7/1).

You're assuming they are counting the days closest to the end date and excluding whole years starting from the beginning date, but perhaps it is the other way around (in an effort to make the algorithm as fast/efficient as possible)? The difference, of course, is leap years that occur at the beginning date versus the ending date.

But, I don't know for sure, it's just a guess.

tomorrow_star
Copper Contributor
Jan 13, 2021
JMB17
Thanks your guess.

According to your thinking, the above results will be inaccurate。
it should be 124、124、125。
- JMB17
  Bronze Contributor
  Jan 13, 2021
  After doing a little reading, it appears the "md" and "yd" options are known to have bugs, and this is one suggested alternative:
  
  =EndDate-EDATE(StartDate,ROUNDDOWN(YEARFRAC(StartDate,EndDate),0)*12)
  - tomorrow_star
    Copper Contributor
    Jan 13, 2021
    JMB17
    Oh, Ok. This is also a good way as it stands. Thanks.