求助考勤判断

43272chl

考勤时间和判断的问题

橒♂蝣

=LET(
x,--FILTER($D$2:$D$13,($B$2:$B$13=B2)*(DAY($D$2:$D$13)=DAY(D2))*(MOD($D$2:$D$13,1)>0.5)),
y,--FILTER($D$2:$D$13,($B$2:$B$13=B2)*(DAY($D$2:$D$13)=DAY(D2))*(MOD($D$2:$D$13,1)<0.5)),
迟到,(MOD(--D2,1)>0.5)*(MOD(--D2,1)>$P$2)*(--D2=MIN(x)),
签到正常,(--D2=MIN(x)),
早退,(MOD(--D2,1)<0.5)*(MOD(--D2,1)<$Q$2)*(--D2=MAX(y)),
签退正常,(--D2=MIN(--FILTER($D$2:$D$13,($B$2:$B$13=B2)*(DAY($D$2:$D$13)=DAY(D2))*(MOD($D$2:$D$13,1)<0.5)*(MOD($D$2:$D$13,1)>=$Q$2)))),
无效,(MAX(y)>=$Q$2)*(MOD(--D2,1)<$Q$2)+(MIN(x)<=$P$2)*(MOD(--D2,1)>=$P$2),
r,IFS(迟到,"迟到",签到正常,"签到正常",早退,"早退",签退正常,"签退正常",无效,"无效",TRUE,"重复"),r
)

橒♂蝣

规则比想象的多的多啊

橒♂蝣

更新到2021应该也能用

43272chl

365确实可以用