上班时间是8：30-17：30，求两个打卡时间之间的时长。

coee · 发表于 2024-12-9 17:01

上班时间是8：30-17：30，求两个打卡时间之间的时长，精确到秒。需剔除周六日、法定节假日，但要包括调休上班时间

coee · 发表于 2024-12-9 21:44

没有答案吗，自己顶一下

dambcer · 发表于 2024-12-9 22:11

VBA自定义公式可以写

coee · 发表于 2024-12-9 23:48

=SUM(IF(OR(ISNUMBER(MATCH(INT(A2),$J$2:$J$31,0)),AND(OR(WEEKDAY(INT(A2))=7, WEEKDAY(INT(A2))=1),NOT(ISNUMBER(MATCH(INT(A2),$K$2:$K$9,0))))),0,$M$3-MAX(MOD(A2,1),$M$2)),IF(OR(ISNUMBER(MATCH(INT(D2),$J$2:$J$31,0)),AND(OR(WEEKDAY(INT(D2))=7, WEEKDAY(INT(D2))=1),NOT(ISNUMBER(MATCH(INT(D2),$K$2:$K$9,0))))),0,MIN(MOD(D2,1),$M$3)-$M$2))*24*60*60+($M$3-$M$2)*SUM(NETWORKDAYS(INT(A2)+1,INT(D2)-1,$J$2:$J$31),INT(D2)-1-(INT(A2)+1)+1-NETWORKDAYS.INTL(INT(A2)+1,INT(D2)-1,"0000000",$K$2:$K$9))*24*60*60

coee · 发表于 2024-12-9 23:50

做了个很笨的公式：=SUM(IF(OR(ISNUMBER(MATCH(INT(A2),$J$2:$J$31,0)),AND(OR(WEEKDAY(INT(A2))=7, WEEKDAY(INT(A2))=1),NOT(ISNUMBER(MATCH(INT(A2),$K$2:$K$9,0))))),0,$M$3-MAX(MOD(A2,1),$M$2)),IF(OR(ISNUMBER(MATCH(INT(D2),$J$2:$J$31,0)),AND(OR(WEEKDAY(INT(D2))=7, WEEKDAY(INT(D2))=1),NOT(ISNUMBER(MATCH(INT(D2),$K$2:$K$9,0))))),0,MIN(MOD(D2,1),$M$3)-$M$2))*24*60*60+($M$3-$M$2)*SUM(NETWORKDAYS(INT(A2)+1,INT(D2)-1,$J$2:$J$31),INT(D2)-1-(INT(A2)+1)+1-NETWORKDAYS.INTL(INT(A2)+1,INT(D2)-1,"0000000",$K$2:$K$9))*24*60*60

coee · 发表于 2024-12-10 00:21

=SUM(IF(OR(ISNUMBER(MATCH(INT(A2),$J$2:$J$31,0)),AND(OR(WEEKDAY(INT(A2))=7, WEEKDAY(INT(A2))=1),NOT(ISNUMBER(MATCH(INT(A2),$K$2:$K$9,0))))),0,$M$3-MAX(MOD(A2,1),$M$2)),IF(OR(ISNUMBER(MATCH(INT(D2),$J$2:$J$31,0)),AND(OR(WEEKDAY(INT(D2))=7, WEEKDAY(INT(D2))=1),NOT(ISNUMBER(MATCH(INT(D2),$K$2:$K$9,0))))),0,MIN(MOD(D2,1),$M$3)-$M$2))*24*60*60+($M$3-$M$2)*SUM(NETWORKDAYS(INT(A2)+1,INT(D2)-1,$J$2:$J$31),INT(D2)-1-(INT(A2)+1)+1-NETWORKDAYS.INTL(INT(A2)+1,INT(D2)-1,"0000000",$K$2:$K$9))*24*60*60

coee · 发表于 2024-12-10 00:25

#在这里快速回复#=SUM(IF(OR(ISNUMBER(MATCH(INT(A2),$J$2:$J$31,0)),AND(OR(WEEKDAY(INT(A2))=7, WEEKDAY(INT(A2))=1),NOT(ISNUMBER(MATCH(INT(A2),$K$2:$K$9,0))))),0,$M$3-MAX(MOD(A2,1),$M$2)),IF(OR(ISNUMBER(MATCH(INT(D2),$J$2:$J$31,0)),AND(OR(WEEKDAY(INT(D2))=7, WEEKDAY(INT(D2))=1),NOT(ISNUMBER(MATCH(INT(D2),$K$2:$K$9,0))))),0,MIN(MOD(D2,1),$M$3)-$M$2))*24*60*60+($M$3-$M$2)*SUM(NETWORKDAYS(INT(A2)+1,INT(D2)-1,$J$2:$J$31),INT(D2)-1-(INT(A2)+1)+1-NETWORKDAYS.INTL(INT(A2)+1,INT(D2)-1,"0000000",$K$2:$K$9))*24*60*60

coee · 发表于 2024-12-10 08:59

没有提示，以为没回复成功，重复回复了几次，没找到删除的地方

coee · 发表于 2024-12-10 09:01

期待大师给个简洁高效的公式

		自动登录	找回密码
密码			免费注册

[求助] 上班时间是8：30-17：30，求两个打卡时间之间的时长。