根据考勤计算加班时长，用 Excel “天花板”“地板”函数

今天教大家一个非常接地气的案例，根据员工每天考勤记录中的实际上下班时间，计算加班或缺勤绩效。

今天的案例，会用到两个全新的函数 CEILING 和 FLOOR，来吧，小板凳搬好，开始上课。

案例：

某公司实行人性化的弹性上下班制度，规则如下：

正常通勤时间为 9 点上班，18 点下班，如果弹性工作，总时长与此相当即可；

上下班时间都以半小时为单位四舍五入；

上班时间往后取整半小时；

下班时间向前取整半小时

请根据下图 1 中的上下班时间，计算出各员工的加班或缺勤分钟数，效果如下图 2 所示。

解决方案：

1. 在 D2 单元格中输入以下公式：

=CEILING(B2,1/48)

此时 D 列的格式是数字，我们可以用格式刷将 B 或 C 列的时间格式复制到 D 列。

2. 下拉复制公式

按照以往惯例，此处应有公式释义，但今天我先把 E 列公式写完，再一起解释，因为 D、E 两列的公式有共性。

3. 在 E2 单元格内输入以下公式：

=FLOOR(C2,1/48)

4. 用格式刷将 E2 单元格的格式变成时间

5. 下拉拖动复制公式

D、E 两列分别使用了 ceiling 和 floor 函数，以下是这两个函数的说明。

CEILING 函数说明：

作用：

将参数 number 沿绝对值增大的方向，向上舍入为最接近的 significance 倍数。

语法：

CEILING(number, significance)

参数：

number：必需，起始日期

significance：必需，要舍入到的倍数

说明：

如果任何一个参数是非数值型，则 CEILING 返回 #VALUE! 错误值；

如果 number 为正且 significance 为负，则返回 #NUM!  错误值；

如果 number 和 significance 都为负，则对值按远离 0 的方向进行向下舍入；

如果 number 为负，significance 为正，则对值按朝向 0 的方向进行向上舍入。

FLOOR 函数说明：

作用：

将参数 number 沿绝对值减小的方向，向下舍入为最接近的 significance 倍数。

语法：

FLOOR(number, significance)

floor 函数除了舍入方向跟 ceiling 正好相反，其他的注意点都一样。

现在我们再回过头来解释刚才的公式。

D 列公式释义：

CEILING(B2,1/48)：

将 B2 单元格的内容，以 1/48 的倍数向上舍入；

为什么是 1/48 呢？因为时间每 24 小时循环一次，如果以半小时为单位的话，就是每 48 个半小时循环一次

E 列公式释义：

FLOOR(C2,1/48)：将 C2 单元格的内容，以 1/48 的倍数向下舍入

6. 在 F2 单元格中输入以下公式：

=(E2-D2)*24*60-9*60

公式释义：

(E2-D2)：通过两列的差计算出上班总时长；

(...)*24*60：将以上时长换算为分钟；

9*60：正常的通勤时间为 9 小时，*60 后换算为分钟；

将两者相减即可得出以分钟为单位的加班或缺勤时间

现在 F2 单元格显示为时间格式，需要将它转换为数值。

7. 选中 F2 单元格 --> 按 Ctrl+1 --> 在弹出的对话框中选择“数字”选项卡 --> 选择“数值”--> 按以下方式设置 --> 点击“确定”：

小数位数：设置为“0”

取消勾选“使用千位分隔符”

选择所需的负数格式

8. 下拉复制公式

这就是最终所需的结果。