根据考勤计算加班时长，用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. 下拉复制公式

这就是最终所需的结果设置每天九点。

很多同学会觉得 Excel 单个案例讲解有些碎片化，初学者未必能完全理解和掌握设置每天九点。不少同学都希望有一套完整的图文教学，从最基础的概念开始，一步步由简入繁、从入门到精通，系统化地讲解 Excel 的各个知识点。

现在终于有了，文中专栏，从最基础的操作和概念讲起，用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本，你也能成为 Excel 高手设置每天九点。