今天教大家一个非常接地气的案例，根据员工每天考勤记录中的实际上下班时间，计算加班或缺勤绩效。
今天的案例，会用到两个全新的函数 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. 下拉复制公式
这就是最终所需的结果。
联系客服
