在财务工作和人力资源管理中,经常需要计算两个时间的间隔,比如需要计算员工的工龄等。 在Excel中,时间本来就是一个数字,是可以用来直接计算的。那么,Excel中有这样的函数吗?——还真有! DATEDIF函数就是这样的函数,可以用来计算两个时间间隔的年、月、日。 但是,在Excel的帮助和插入公式里面却没有。因此,DATEDIF函数是Excel的隐藏函数,该函数只能手工输入,而不能使用插入公式输入。 一、函数语法简介 DATEDIF(起始时间,结束时间,需要计算的时间间隔类型) 起始时间:为一个日期,它代表时间段内的第一个日期或起始日期。 结束时间:为一个日期,它代表时间段内的最后一个日期或结束日期。 需要计算的时间间隔类型:为所需信息的返回类型。 注:结束日期必须大于起始日期。 下面举个小例子:在日常工作中非常实用。 假如B1单元格写的也是一个日期,那么下面的三个公式可以计算出B1单元格的日期和今天的时间差,分别是年数差,月数差,天数差。注意下面公式中的引号和逗号括号都是在英文状态下输入的。 =DATEDIF(B1,TODAY(),"Y")计算年数差,"Y"代表要求时间段中的整年数。 =DATEDIF(B1,TODAY(),"M")计算月数差,"M"代表要求时间段中的整月数。 =DATEDIF(B1,TODAY(),"D")计算天数差,"D"代表要求时间段中的天数。 "MD"代表要计算两个日期中天数的差,但是忽略日期中的月和年。 "YM"代表要计算两个日期中月数的差,但是忽略日期中的年。 "YD"代表要求两个日期中天数的差,忽略日期中的年。 二、使用DATEDIF函数计算的间隔天数相等而间隔月份不一定相等 DATEDIF函数计算的间隔月份是指足月。大家都知道每年的12个月,并不是所有月份的天数都是相等的,因此在DATEDIF函数计算时会出现间隔天数相等而间隔月份不相等的情况,甚至还会出现间隔天数少的而间隔月份多的情况。 下面举例说明:图E6-2
图E6-2说明:通过DATEDIF函数计算的间隔天数是30天的,可能DATEDIF函数计算的间隔月份是0,反而间隔天数是28天或29天的可以间隔月份是1。 这个特点对于财务工作中的需要特别注意!我之前在设置固定资产折旧函数公式时就在这上面出现过错误。本来会计准则规定,当月入账的固定资产,次月开始折旧。当月入账的固定资产,可能是当月1号,也可能是当月最后一天,那么如果DATEDIF函数计算的间隔月份就可能少一个月的情况。最后我嵌套了一个EOMONTH函数才解决了问题。 三、应用举例 在员工表上设置函数公式,自动生成员工的工龄和生日提醒。如图E6-3 说明: 1.“年龄”的计算: 在单元格E2输入函数公式:=DATEDIF(C2,TODAY(),"Y") 2.“工龄”的计算: 在单元格F2中输入函数公式:=DATEDIF(D2,TODAY(),"Y") 3.“生日提醒”的设置 (1)输入函数公式: 在单元格G2中输入函数公式:=IF(DATEDIF(C2,TODAY(),"YD")<8,"距离生日还有"&DATEDIF(C2,TODAY(),"YD")&"天","") 当距离生日还有7天时就开始提醒,其余时间则不显示任何内容。 (2)条件格式的设置 当出现生日提醒时,自动生成不同颜色以引起注意。这个只需要通过菜单栏的“条件格式”设置即可。 步骤:用鼠标选中需要设置的区域→开始→条件格式→突出显示单元格规则(H)→文本包含(T),这时会出现下面的对话框: 只需要在对话框中输入“距离生日”或“距离”等文字就可以了。 我曾经说过,Excel的函数不用记,但是对于DATEDIF函数却不适用,因为它是隐藏函数,所以必须要记住它。还好,隐藏函数不是很多。 更多Excel财务应用,请参阅: |
|