计算不同部门不同班次的打卡是否迟到和迟到多少分钟？

kangdiwu

各位大神，您们好！请教一下，用什么公式，来计算不同部门不同班次的打卡是否迟到和迟到多少分钟？

先判断最接近的部门班次？然后判断是否迟到？迟到的话迟到多少分钟？并在对应下方单元格显示

麻烦用公式，最好能用新、旧函数两种公式（有的电脑没如365版最新的函数），非常感谢！

边缘码农

张三，客房部，但没说是哪个班次。根据题意，5月1日-5月6日都是上班时间？

kangdiwu

边缘码农 发表于 2023-6-10 14:50
张三，客房部，但没说是哪个班次。根据题意，5月1日-5月6日都是上班时间？

你好。
就是各班次太多，又不知道上哪个班次，所以才要用公式来判断最接近的那个班次来看是否迟到和迟到多少分钟。
举例嘛，就是1-6日都有上班，各个单元格里的时间就是他们上班的时间

边缘码农

=MIN(IF(IF($A$2:$A$20=$G2,1,0)*H2-IF($A$2:$A$20=$G2,1,0)*$C$2:$C$20>0,IF($A$2:$A$20=$G2,1,0)*H2-IF($A$2:$A$20=$G2,1,0)*$C$2:$C$20,10000),1)

边缘码农

新的公式
=MIN(IF(IF($A$2:$A$20=$G2,H2-$C$2:$C$20,0)>0,IF($A$2:$A$20=$G2,H2-$C$2:$C$20,0),10000),1)

excel2016

kangdiwu

边缘码农 发表于 2023-6-12 09:28
新的公式
=MIN(IF(IF($A$2:$A$20=$G2,H2-$C$2:$C$20,0)>0,IF($A$2:$A$20=$G2,H2-$C$2:$C$20,0),10000),1)
...

您好。
这个公式不太对，第一个客房部（7：58）就错了，最接近上班时间是8：00（不算迟到），而不是7：30（公式取了这个，迟到28分钟）。

边缘码农

kangdiwu 发表于 2023-6-12 10:32
您好。
这个公式不太对，第一个客房部（7：58）就错了，最接近上班时间是8：00（不算迟到），而不是7：3 ...

是我的理解有误。

kangdiwu

受了启发，好像下面的公式可以解决问题了，谢谢各位。
新函数公式
=LET(A,H2-FILTER($C$2:$C$20,$A$2:$A$20=$G2),MAX(,IF(MIN(ABS(A))=ABS(A),A,)))
excel各种版本的通用公式好像太长复杂了，不太会

solextrade

=TEXT(H2-LOOKUP(,0/FREQUENCY(0,IF($A$2:$A$20=$G2,(H2-$C$2:$C$20)^2,9)),$C$2:$C$20),"H:MM;;")

kangdiwu

solextrade 发表于 2023-6-12 12:49
=TEXT(H2-LOOKUP(,0/FREQUENCY(0,IF($A$2:$A$20=$G2,(H2-$C$2:$C$20)^2,9)),$C$2:$C$20),"H:MM;;")

谢谢高手大神，麻烦能说一下公式的意思吗？谢谢