根据考勤计算加班时长,用 Excel “天花板”“地板”函数 |
您所在的位置:网站首页 › 自动计算考勤表 › 根据考勤计算加班时长,用 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. 下拉复制公式 这就是最终所需的结果。 |
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |
CopyRight 2018-2019 实验室设备网 版权所有 win10的实时保护怎么永久关闭 |