Forum Discussion
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/1 has 304 days
second：2003/9/1 =》2020/9/1， 2020/9/1 =》2021/7/1 has 303days
why the result is different？
@Sergei SergeiBaklan
4 Replies
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.
Thanks your guess.
According to your thinking, the above results will be inaccurate。
it should be 124、124、125。
- 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)
