求工作时间公式优化

mmlzh

求开始到结束时间的工作时间的公式优化，要排除非工作日。详见附件。



补充内容 (2019-2-13 16:02):
附件中工作日时间：8:00~12:00、12:00~17:00的后面12有误
应为8:00~12:00、13:00~17:00


补充内容 (2019-2-13 17:03):
附件中的公式太啰嗦复杂，请参考置顶的7楼/9楼公式

mmlzh

回贴时，翻回来想抄一下，看看还是可以简化的

1. =IF(A2<B2,SUMPRODUCT(ISNA(MATCH(INT(A2:B2),E$2:E$9,))*(TEXT({12;17}-MOD(A2:B2,1)*24,"[>4]4;!0;G/通用格式"))*{1,-1})/8+SUMPRODUCT(INT(A2:B2)*{-1,1})-SUMPRODUCT((A2<E$2:E$9)*(B2>E$2:E$9)),)

复制代码

mmlzh

默认排除周末非工作日，每日工作时间段9:00~12:00  13:00~18:00 ，结果以天为单位，7.5是7天4小时
数组公式=IF(A2<B2,SUM((MOD(INT(A2:B2),7)>1)*(TEXT({12;18}-MOD(A2:B2,1)*24,{"[>3]3";"[>5]5"}&";!0;G/通用格式"))*{1,-1})/8)+IF(INT(B2)>INT(A2),SUM(N(MOD(ROW(INDIRECT(CEILING(A2,1)&":"&INT(B2))),7)>1)))
工作时间段不同、周末单双休可视具体要求修改公式标红部分

逆光萤火

=IF(A2="",IFERROR(--B1,)+1,"")

mmlzh

2楼回复的是什么意思？

mmlzh

修改了一下，但还是觉得太长了。
=(ISNA(MATCH(INT(A2),$E$2:$E$6,0))*SUMPRODUCT((--TEXT(IF({1,0},12,17)-MOD(A2,1)*24,"[>4]4;!0;0.000")))/8
+SUMPRODUCT(INT(A2:B2)*{-1,1})
-ISNA(MATCH(INT(B2),$E$2:$E$6,0))*SUMPRODUCT((--TEXT(IF({1,0},12,17)-MOD(B2,1)*24,"[>4]4;!0;0.000")))/8
-SUMPRODUCT((A2<$E$2:$E$6)*(B2>=$E$2:$E$6)))
*(A2<B2)

mmlzh

又或者：
=(SUMPRODUCT(ISNA(MATCH(INT(A2:B2),$F$2:$F$6,0))*(TEXT(12-MOD(A2:B2,1)*24,"[>4]4;!0;0.000")+TEXT(17-MOD(A2:B2,1)*24,"[>4]4;!0;0.000"))/8,{1,-1})
+SUMPRODUCT(INT(A2:B2)*{-1,1})
-SUMPRODUCT((A2<$F$2:$F$6)*(B2>$F$2:$F$6)))
*(A2<B2)

丢丢表格

mmlzh 发表于 2017-9-28 00:18
又或者：
=(SUMPRODUCT(ISNA(MATCH(INT(A2:B2),$F$2:$F$6,0))*(TEXT(12-MOD(A2:B2,1)*24,"[>4]4;!0;0.000" ...

收到信息
=SUM((COUNTIF(E:E,INT(A2:B2))=0)*TEXT({1,-1}*({17,8}-MOD(A2:B2,1)*24),"[>9]9;!0;G/通用格式"))/9+MAX(NETWORKDAYS.INTL(A2+1,B2-1,,E$2:E$10),)-(INT(A2)=INT(B2))

zzq1989250

学习了，谢谢楼主

nini711

标记一下，万一哪天用上了呢，时间这玩意真复杂