一个难倒90%人的【工时计算问题】浅析

三秋之叶

已知一员工（小员）的上下班时间和公司规定的上下班时间（见下图所示），计算员工每天的工作时长。如何计算，这个问题曾经难倒了不少人，今天我来给大家解析一下~

如果你硬算，可能有一定的难度，但你一旦发现问题的等价问题，就会非常简单，如下图所示：

※假设有一名员工名叫【小上】，上班时间是：00:00，下班时间是【小员】的上班时间

※假设另一名员工名叫【小下】，上班时间是：00:00，下班时间是【小员】的下班时间

你会发现：小员工作时长=小下上班时长-小上上班时长

接下来问题就转变成了，如何求【小上】和【小下】的上班时长问题。为了便于大家理解，继续看图吧！可能有下面的5种情况：

不用害怕，其实就是一个问题，我们逐一看一下就行

情况1：下班时间在18:00~24:00

你会很容易的发现下面的等式：

上午上班时长=①-②

下午上班时长=③-④

上班合计时长=①-②+③-④

情况2：下班时间在13:30~18:00

你会很容易的发现下面的等式：

上午上班时长=①-②

下午上班时长=③-④

上班合计时长=①-②+③-④

此时④为负数，说明不在公司，该时段无需计算，④会按0计算

情况3：下班时间在11:30~13:30

你会很容易的发现下面的等式：

上午上班时长=①-②

下午上班时长=③-④

上班合计时长=①-②+③-④

此时③④为负数，说明不在公司，该时段无需计算，③④会按0计算

情况4：下班时间在09:00~11:30

你会很容易的发现下面的等式：

上午上班时长=①-②

下午上班时长=③-④

上班合计时长=①-②+③-④

此时②③④为负数，说明不在公司，该时段无需计算，②③④会按0计算

情况5：下班时间在00:00~09:00

你会很容易的发现下面的等式：

上午上班时长=①-②

下午上班时长=③-④

上班合计时长=①-②+③-④

此时①②③④为负数，说明不在公司，该时段无需计算，①②③④会按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为每天工作时长。函数总体比较简单，就不解释了~

cinlo

楼主好勤奋~~~~收藏，学习~~~

jokulboy

*--函数总体比较简单，就不解释了~--*
Last一句尤其精华~~~

三秋之叶

不好意思，有一处笔误，抱歉哈~
小员上班时长=小下上班时长-小上下班时长
应改为：
小员上班时长=小下上班时长-小上上班时长

bright_xh

根本看不懂~~~~~~~~~~~~~~~~~~~~

丢丢表格

三秋之叶 发表于 2021-9-13 11:29
不好意思，有一处笔误，抱歉哈~
小员上班时长=小下上班时长-小上下班时长
应改为：

此计算方式 ，我发现 海鲜老师 写得最早，（在这之前有无老师写过，就木知了）

很好，你在这里再巩固一下。
向海鲜老师学习了。

三秋之叶

丢丢表格 发表于 2021-9-13 11:37
此计算方式 ，我发现 海鲜老师 写得最早，（在这之前有无老师写过，就木知了）

很好，你在这里再巩固 ...

这个解法很多人不理解，我把我的理解分享一下，没想到前天的构思在大神那里得到了印证

koovan

为什么把员工叫小上，小下，正好问题的重点是上班，下班，有点乱

三秋之叶

koovan 发表于 2021-9-13 12:46
为什么把员工叫小上，小下，正好问题的重点是上班，下班，有点乱

这个就是我的目的，小上就是上班时间，小下就是下班时间。可以下载附件看，原本问题很难，我找到了等价问题，变得简单了

cinlo

丢丢表格 发表于 2021-9-13 11:37
此计算方式 ，我发现 海鲜老师 写得最早，（在这之前有无老师写过，就木知了）

很好，你在这里再巩固 ...

看得出我学习不认真，不知道海鲜老师还写过！反省~~~~~~~~~