※假设有一名员工名叫【小上】,上班时间是:00:00,下班时间是【小员】的上班时间
※假设另一名员工名叫【小下】,上班时间是:00:00,下班时间是【小员】的下班时间
你会发现:小员工作时长=小下上班时长-小上上班时长
接下来问题就转变成了,如何求【小上】和【小下】的上班时长问题。为了便于大家理解,继续看图吧!可能有下面的5种情况:
不用害怕,其实就是一个问题,我们逐一看一下就行
你会很容易的发现下面的等式:
上午上班时长=①-②
下午上班时长=③-④
上班合计时长=①-②+③-④
此时①②③④为负数,说明不在公司,该时段无需计算,①②③④会按0计算
以上5种情况用公式表示就是:
=TEXT(下班时间-{"09:00";"11:30";"13:30";"18:00"},"[<]!0")*{1;-1;1;-1}
因为,小员上班时长=小下上班时长-小上上班时长
所以,小员上班时长
=SUM(TEXT(下班时间-{"09:00";"11:30";"13:30";"18:00"},"[<]!0")*{1;-1;1;-1})
-SUM(TEXT(上班时间-{"09:00";"11:30";"13:30";"18:00"},"[<]!0")*{1;-1;1;-1})
化简一下:
=SUM(TEXT({上班时间,下班时间}-{"09:00";"11:30";"13:30";"18:00"},"[<]!0")*{1;-1;1;-1}*{-1,1})
即:
=SUM(TEXT(A2:B2-F$2:F$5,"[<]!0")*{1;-1;1;-1}*{-1,1})
如果需要具体的小时数字,则:
=SUM(TEXT(A2:B2-F$2:F$5,"[<]!0")*{1;-1;1;-1}*{-1,1})*24
思考:如果涉及到跨天上班时长如何计算?
=SUM(TEXT(MOD(A2:B2,1)-F$2:F$5,"[<]!0")*{1;-1;1;-1}*{-1,1}*24,INT(A2:B2)*{-1,1}*7)
注:7为每天工作时长。函数总体比较简单,就不解释了~