学习Excel技术,关注微信公众号:
excelperfect
Q:给定开始日期和结束日期,想要知道这两个日期之间有多少个星期一?例如下图1所示,2021年7月1日至2021年8月8日之间有多少个星期一,如何使用公式得到答案。
图1
A:可以使用公式:
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B1&":"&B2)),"aaaa")="星期一"))
结果如下图2所示。
图2
众所周知,Excel是使用数字序列来存储日期的,显示的是日期形式,实际上就是一个数字。默认情况下1900年1月1日是数字序列1,而2021年7月1日是数字序列44378,因为它在1900年1月1日之后的第44378天。同理,2021年8月8日是数字序列44416。
公式中:
INDIRECT(B1&":"&B2)
生成:
INDIRECT(44378, 44416)
这样:
ROW(INDIRECT(44378, 44416))
即为:
ROW(44378:44416)
返回数组:
{44378;44379;44380;44381;…;44415;44416}
传给指定格式参数的TEXT函数,得到:
{“星期四”;”星期五”;”星期六”;…;”星期日”}
与“星期一”进行比较,生成包含TRUE/FALSE值的数组:
{FALSE;FALSE;FALSE;FALSE;TRUE;…;FALSE}
前面的双减号将其转化为1/0值:
{0;0;0;0;1;…;0}
传递给SUMPRODUCT函数进行求和,结果即为该日期区间包含“星期一”的数量。
下面是另外一些公式:
公式1:
=B2-B1-NETWORKDAYS.INTL(B1,B2,12)+1
公式2:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B1&":"&B2)))=2))
公式3:
=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(B1&":"&B2)))=2))
公式4:
=(WEEKDAY(B1,2)=1)+QUOTIENT(B2-B1,7)+(WEEKDAY(B2,2)<WEEKDAY(B1,2))
公式5:
=IF(WEEKDAY(B1,11)=1,0)+INT(DAYS(B2,B1)/7)+IF(MOD(DAYS(B2,B1),7)+WEEKDAY(B1,11)>7,1,0)
公式6:
=ABS(INT((N(B2)-2)/7)-INT((N(B1)-2)/7))
公式7:
=MAX(ROUNDUP(((B2-B1+1)+(IF(WEEKDAY(B1)>2,WEEKDAY(B1)-9,WEEKDAY(B1)-2)))/7,0),0)
公式8:
=ROUNDDOWN((((B2-B1+1)-CHOOSE(WEEKDAY(B1),1,0,6,5,4,3,2))/7)+1,0)
有兴趣的朋友可以详细研究。
undefined
欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。