The parameter "yd" of the datedif function

%3CLINGO-SUB%20id%3D%22lingo-sub-2055240%22%20slang%3D%22zh-CN%22%3EThe%20parameter%20%22yd%22%20of%20the%20datedif%20function%E3%80%82%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2055240%22%20slang%3D%22zh-CN%22%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3Estart_date%E3%80%82%3C%2FTD%3E%3CTD%3Eend_date%E3%80%82%3C%2FTD%3E%3CTD%3Edatedif%3D%26gt%3Byd%E3%80%82%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2234.41295546558704%25%22%3E1997%2F9%2F1%E3%80%82%3C%2FTD%3E%3CTD%20width%3D%2232.25371120107962%25%22%3E2000%2F7%2F1%E3%80%82%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E%3DDATEDIF(a2%2C%20b2%2C%20%22yd%22)%20%3D%26gt%3B%20303%E3%80%82%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2234.41295546558704%25%22%3E%3CP%3E2003%2F9%2F1%E3%80%82%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2232.25371120107962%25%22%3E2021%2F7%2F1%E3%80%82%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E%3DDATEDIF(a3%2C%20b3%2C%20%22yd%22)%20%3D%26gt%3B%20304%E3%80%82%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efirst%3A%201997%2F9%2F1%20%3D%E3%80%8B1999%2F9%2F1%2C%201999%2F9%2F1%20%3D%E3%80%89%202000%2F7%2F1%20has%20304%20days%E3%80%82%3C%2FP%3E%3CP%3Esecond%3A2003%2F9%2F1%20%3D%E3%80%8B2020%2F9%2F1%2C%202020%2F9%2F1%20%3D%E3%80%8B2021%2F7%2F1%20has%20303days%E3%80%82%3C%2FP%3E%3CP%3Ewhy%20the%20result%20is%20different%3F%3C%2FP%3E%3CP%3E%40Sergei%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20%E3%80%82%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2055240%22%20slang%3D%22zh-CN%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2055346%22%20slang%3D%22en-US%22%3ERe%3A%20The%20parameter%20%22yd%22%20of%20the%20datedif%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2055346%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F927004%22%20target%3D%22_blank%22%3E%40tomorrow_star%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20interesting%2C%20I've%20never%20noticed%20it%20before.%20I%20don't%20know%20for%20sure%2C%20but%20just%20an%20observation%20-%20perhaps%20the%20algorithm%20is%20counting%20the%20days%20from%201997%2F9%2F1%20%26gt%3B%201998%2F7%2F1%20(and%20excluding%20the%20whole%20years%20from%201998%2F7%2F1%20%26gt%3B%202000%2F7%2F1).%20And%20counting%20days%20from%202003%2F9%2F1%20%26gt%3B%202004%2F7%2F1%20(excluding%20whole%20years%20from%202004%2F7%2F1%20%26gt%3B%202021%2F7%2F1).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20assuming%20they%20are%20counting%20the%20days%20closest%20to%20the%20end%20date%20and%20excluding%20whole%20years%20starting%20from%20the%20beginning%20date%2C%20but%20perhaps%20it%20is%20the%20other%20way%20around%20(in%20an%20effort%20to%20make%20the%20algorithm%20as%20fast%2Fefficient%20as%20possible)%3F%20The%20difference%2C%20of%20course%2C%20is%20leap%20years%20that%20occur%20at%20the%20beginning%20date%20versus%20the%20ending%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%2C%20I%20don't%20know%20for%20sure%2C%20it's%20just%20a%20guess.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JMB17_0-1610509284188.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F246011i6F7E2C2FADC30C18%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JMB17_0-1610509284188.png%22%20alt%3D%22JMB17_0-1610509284188.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2055380%22%20slang%3D%22zh-CN%22%3ERe%3A%20The%20parameter%20%22yd%22%20of%20the%20datedif%20function%E3%80%82%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2055380%22%20slang%3D%22zh-CN%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%E3%80%82%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20your%20%3CSPAN%3Eguess.%3C%2FSPAN%3E%20%E3%80%82%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22%E6%88%AA%E5%B1%8F2021-01-13%2011.53.16.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F246013i2213598242596BCD%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22%E6%88%AA%E5%B1%8F2021-01-13%2011.53.16.png%22%20alt%3D%22Screenshot2021-01-13%2011.53.16.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAccording%20to%20your%20thinking%2C%20the%20above%20results%20will%20be%20inaccurate%E3%80%82%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eit%20should%20be%20124%E3%80%81124%E3%80%81125%E3%80%82%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2055484%22%20slang%3D%22en-US%22%3ERe%3A%20The%20parameter%20%22yd%22%20of%20the%20datedif%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2055484%22%20slang%3D%22en-US%22%3EAfter%20doing%20a%20little%20reading%2C%20it%20appears%20the%20%22md%22%20and%20%22yd%22%20options%20are%20known%20to%20have%20bugs%2C%20and%20this%20is%20one%20suggested%20alternative%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DEndDate-EDATE(StartDate%2CROUNDDOWN(YEARFRAC(StartDate%2CEndDate)%2C0)*12)%3C%2FLINGO-BODY%3E
New Contributor
start_dateend_datedatedif=>yd
1997/9/12000/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 @Sergei Baklan

4 Replies

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

JMB17_0-1610509284188.png

 

@JMB17 

Thanks your guess.

 

截屏2021-01-13 11.53.16.png

 

 

 

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)

@JMB17 

Oh, Ok. This is also a good way as it stands. Thanks.