返回目录：excel表格制作
小陆正在网上查一些关于精致生活的用品的时候，突然觉的左肩一沉，转头一看没人，正想继续时，右肩又一沉，正想发火说：谁啊，这么讨厌，打扰人家工作！可看到是谁后，瞬间笑脸相迎说：霉霉，什么事啊，你看看你有什么想要吗？我买给你啊！
霉霉：别说买东西了，哪有心情啊，领导又给我出难题啦！
小陆：开始都这样，走，去你哪吧，边走边说是什么问题吧？
霉霉：嗯，领导让我统计员工的总的加班时间？要求<30分，不算加班，>=30且<60的算30分钟，>=60且<90的算60，依次类推，这怎么计算啊？
小陆：我给你讲的日期的部分的函数，也没有系统的给你说日期的时间的知识，你不会很正常啊！打开你的发给你的表格，让我看看都是有什么内容？
霉霉打开了发来的表，内容如下：
公司员工的考勤信息表
小陆一边在E列创建的加班时间，接着输入公式：=ROUND(FLOOR(D2-(TEXT(D2,"yyyy/m/d")&" 17:29:59"),"0:30")/TEXT("1:00:00","0.000000"),3)，然后拖拽完成公式填充。
霉霉：这个公式里我们就认识3，4个函数，其他的函数都是干什么的？之前你的写的吧，我还能看明白，今天的这个公式彻底蒙圈啦！你赶紧跟我讲讲吧。说着霉霉已经拿出小本本，准备好记笔记啦！
小陆：我先简单说一下思路：咱们正常的下班为17:30，我们用下班打卡的时间减去正常下班的时间，求出时间差，然后与30分比较，是30分钟整倍数的返回整倍数时间，然后再将分钟转化成小时数（方便日后求计算加班费）。
霉霉连连点头：嗯嗯，公式中我唯一能看懂就是D2使用引用的下班打卡的时间，其他就看不懂，你仔细讲解一下公式吧。
小陆：TEXT函数的功能是将字符串按设定的格式编码显示，我使用这个功能只截取了打卡的当天日期然后拼接下班时间，组成当天的下班日期时间串，注意在拼接时间的时候，一定要在时间字符串前加个空格，否则会导致无法识别为日期时间格式，也就无法计算，然后用打卡时间与之相减，得出加班时间。
TEXT文本函数使用语法结构图
霉霉疑惑的说：哪(TEXT(D2,"yyyy/m/d")&" 17:29:59")最终会得出什么结果呢？
小陆：执行后的结果：2019/6/1 17:29:59，时间日期字符串，这个结果可以直接与日期格式计算的。
霉霉：为什么是17:29:59啊？不应该是17:30:00吗？
小陆：这个跟公式中的floor函数有关，它的功能是向下取整，它有两个必填参数，第一个处理的数字，第二个参数为倍数，只保留最大整倍数的数字，比如floor(1,0.3)=0.9、语法结构图如下：
FLOOR函数语法结构图
正是利用floor这个特性，正好符合公司定的30分钟的规则，所以公式就可以这么写：=floor(D2-TEXT(D2,"yyyy/m/d")&" 17:30:00"),"0:30:00"),会出现18:00:00打卡时，公式计算为0，为了纠正需要将公式变为=floor(D2-TEXT(D2,"yyyy/m/d")&" 17:29:59"),"0:30:00")，这个问题可以调整也可以不调整，因为打卡的时间不太可能整分整秒。
霉霉：哪是为什么呢？怎么会出现这种情况呢？
小陆：由于时间30分是个无限循环的小数，因为Excel的精度问题，不过还好精度误差在1-2秒，这也是我为什么将公式提前1秒就能解决精度导致的误差问题，最后我们需要将结果转化成小时并保留2位小数即=round(floor(D2-TEXT(D2,"yyyy/m/d")&" 17:29:59"),"0:30:00")/text("1:00","0.000000"),2)，最后加一个严谨性判断iferror就搞定啦！
霉霉：round函数也是小数保留函数吗？
小度：没有错，它是符合四舍五入的规则的，第二个参数就是保留几个小数位标志。
ROUND函数使用语法结构图
一个爱讲，一个爱听，不知什么两人背后多了两个人都不知道，不用说你也猜到了，正是小皮和小度。
小度接着说：陆儿啊，你的公式有问题，你怎么能舍近求远呢，计算时间还要带上年月日呢，者只需要计算小数部分就好啦，看我的：=ROUND(FLOOR(MOD(D4,1)-TEXT("17:30:00","0.000000"),"0:29:59")/TEXT("1:00","0.000000"),2)，怎么样，这回我的写法没有问题吧！
小陆：嗯，是没有问题，我是因为怕将太多了，霉霉记不住，所以没有讲这种方法。然后转过头去，问小皮：你是不是也有什么要说的啊？
小皮：当然，我看见你俩吧，公式写的不错，就是操作太初级了，怎么还拖拽填充公式呢？我来教你们一手，鼠标停到单元格右下角，当变成实心的＋时，双击就能完成整列有内容部分的填充！
小陆笑了笑，小声告诉霉霉：赶紧记下来吧，这个不错！
霉霉：第二个公式我不太明白呢？
小陆：你先记下来，回来我给你再讲，今天讲的东西挺多了，你回去消化消化吧！
小皮和小度都觉得很尴尬，小皮说：忘了忘了，我的文案还没有写完了！
小度说：嗯嗯，我也有点还没有弄，我得走了！
这两人就这样灰溜溜的走了，小陆说：等过这段时间，我好好给补补Excel啊！
霉霉：好啊好啊，哪是去我家，还是你家？此处省略1万字。
加班时间效果图
（我是喜欢用故事聊excel的我爱极客的小胖，欢迎你在下方留言，把你遇到问题告诉我们，我们看到后，会第一时间回复。在工作的路上你并不孤单，我们一起同行！）