如何用日期函数核算按小时计算的年假时长
作者：郅龙来源：部落窝教育发布时间：2021-06-11 11:19:08点击：2733
编按：
HR必会几个日期函数，才能轻松核查员工转正日期、核算不同员工的不同年假时长（按小时算）……明天就是端午小假了，面对想要把年假和端午假期一起放的员工来说，今天是兴奋的一天；可是对于HR来说，快速整理员工可放假的时长并及时回复员工，就是一件很考验工作效率的事！下面，一起来看看如何快速核算假期时长吧！另外还有核算转正日期和合同结束日期，2个HR必会日期函数！
总是有人问：
“最常用的函数是什么？”
“什么函数是必须学会的？”
……
类似的问题有好多。
其实常用的函数和必会的函数是一个很笼统的说法，因人而异。不同的岗位不同的工作，遇到的问题是五花八门的，解决问题所用到的函数也是不同的。
以HR为例，不同的公司必定会用到的函数都没有唯一标准，但是如果将应用场景明确的话，什么是必会的函数，似乎还是有迹可循的。
小李是某公司人资部，专门负责员工合同管理和员工年假核算的员工。对他来说，经常遇到的问题，如下图的表格：
表格中已有的内容都是基础资料，空白的三列就是他的主要工作。
那么对小李来说，什么函数是必须要会的，相信大家看完今天的教程都能知道答案。
接下来咱们一起帮小李把这三列公式补全。
一、转正日期
已知入职日期和试用期，如何计算出转正日期？
这个问题不难理解，例如入职日期为2018年4月2日，试用期3个月，转正日期就是2018年7月2日。
正确结果应该是这样的：
不难是不难，但要自己一个一个的算好再去填吗？
肯定不需要！那么要用什么公式和函数才能自动计算出转正日期呢？
答：EDATE函数。
这个函数的功能就是得到某个日期在指定月数之后的一个日期。
格式为：=EDATE（起始日期，指定月数）
放在本例中，起始日期就是入职日期，指定月数就是试用期。
因此最终的公式为：=EDATE(C2,D2)
二、合同结束日期
已知合同开始日期和合同期限，如何计算合同结束日期？
实际上这个问题和第一个问题如出一辙，区别在于实习期的单位是月，而合同期限的单位是年。
如果继续使用EDATE函数的话，指定月数能不能直接使用指定年数呢？
答案是不行！但是可以变通一下！
因为一年有十二个月这是固定的，所以可以把公式改成：EDATE（合同开始日期，合同期限*12）
套入表格中，公式就是：=EDATE(F2,H2*12)
三、最后一个问题——年假核算。
这个问题和前两个完全不一样。因为不同公司，年假的规则也是不同的。
小李他们公司的年假是这样规定的：入职满一年后，每年享有5天年假，每天按8小时核算，每次休假按4小时一次使用，因此年假需要核算到小时（h）。另外，入职满一年后年假才开始计入，比如说入职一年到一年半之间的时候，只拥有2.5天年假（年假中的另外2.5天需入职一年半到两年之间才拥有），合计增加年假20小时。
感觉这是一道数学题了。怎么设计一个公式来自动核算年假呢？
这里面有几个关键步骤：
1.要统计出入职一年的时间；
2.要计算出现在距离入职一年的这个时间有多久（几个月），这个时间也就是实际核算年假的基础；
3.根据统计出的核算年假的月数，按照每年5天的标准分摊到每个月，同时按照4小时为单位进行核算，最后按照统一的格式来显示核算结果。
最后的效果是这样的：
公式为：=IFERROR(INT(FLOOR(DATEDIF(EDATE(C2,12),TODAY(),"M")/12*5*8,4)),0)&"小时"
如何理解这个公式，又如何才能自己完成这个公式呢？
篇幅所限，就不一一详细解释这个公式了。
下面简单的帮大家拆解一下其中的原理：
①在这个公式中，最里面的一层是EDATE(C2,12)，和前面两个问题一样，最终得到的是入职一年的日期。
②公式的第二层是DATEDIF(EDATE(C2,12),TODAY(),"M")，DATEDIF也是公众号里多次讲过的函数，此处的作用是计算现在距离转正一年还有几个月。
③第三层是一堆数字计算，INT(FLOOR(DATEDIF()/12*5*8,4))，就是将DATEDIF计算出的月数除以12（一年12个月），乘以5（每年5天年假）再乘以8（每天年假按8小时计算）换算成年假的小时数，再以4为基数取整得到一个可以实际使用的年假小时数。
④最后一层是美化了，使用IFERROR将错误值显示为0，并且加了&"小时"，显示出核算结果的单位。
PS：
想要详细了解这个嵌套公式，大家可以查看下面的教程！
在这个公式中，初学者比较难理解的可能是第三层的这些数学函数，实际上这类函数还挺多，有兴趣的朋友扫描下方二维码，到公众号下留言，咱们单独来一期数学计算类的函数介绍。
最后一个问题，小李必须学会的函数到底是哪个，你看出来了吗？
本文配套的练习课件请加入QQ群：902294808下载。
做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！
扫下方二维码关注公众号，可随时随地学习Excel：
相关推荐：
HR的福音！一张能自动提醒面试的excel表格，面试信息再多也不怕看漏！
版权申明：
本文作者郅龙；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。