当前位置: > 热议

超实用的5个Excel日期处理公式,懒人的福音!

时间:2022-04-23 08:24:52 热议 我要投稿

关键字:日期公式

栏目:函数

小伙伴们,你们好呀~

Excel中与日期有关的公式很常见,涉及到在账户应付款处理、项目日程安排、时间管理等很多方面。

今天给大家分享几个与星期、工作日相关的公式,例如计算两个日期间的工作日数、判断一个日期是否为周末等。

小伙伴们,赶紧来看一看吧!

5个案例,都很实用,财务必备,新手可以多看看,增加对函数的理解。

案例1:如何判断某个日期是否为周末?

人事部需要对1月份有加班记录的日期判断是周末还是工作日,效果如图所示。

为了方便大家验证结果,用=TEXT(B2,"AAAA")公式备注了一下。

示例中判断周末的公式为:=IF(WEEKDAY(B2,2)>5,"周末","工作日")

公式解析: 这个公式中,WEEKDAY函数的第2参数为2,得到的就是B列日期的星期数字, 6代表星期六、7代表星期日,因此只需判断 WEEKDAY 函数的返回值是否大于 5,即可判断出对应日期是否为周末。

案例2:如何计算离职员工最后一个月的实际工作天数?

=MAX(NETWORKDAYS(EOMONTH(B2,-1)+1,B2)-IFERROR(VLOOKUP(C2,$F$2:$H$8,3,0),),)

公式解析:

①EOMONTH(B2,-1)+1得到的是离职月的1号;

②NETWORKDAYS函数得到离职月1号至离职日期之间的工作日天数(不含周六和周日);

③VLOOKUP(C2,$F$2:$H$8,3,0)的作用是匹配出离职月的法定节假日天数;

④使用IFERROR函数将没有法定节假日的月份返回0,否则会得到错误值无法计算;

⑤工作日天数-法定节假日是最终的上班天数,加上一个MAX防止出现负数。

这个公式比较复杂,有需要的话套用即可。

案例3:如何计算两个日期间星期日的天数?

某公司要给员工在一段时间内周日上班的补发津贴,因此需要统计出周日的天数,结果如图所示。

NETWORKDAYS.INTL函数是Excel 2010版才有的函数,特点是第三参数可以使用代码来指定需要统计周几的天数。

例如“1111110”这种格式中,1表示休息,0表示上班,0在第七位,表示周日上班。函数统计两个日期之间的上班天数,也就是周日的天数。

如果要统计两个日期之间周二的天数,只需要将第二位用0表示即可,代码为“1011111”。

案例4:根据订货日期推算交货日期?

已知订货日期和交货期限(工作日),可以使用=WORKDAY(B2,C2)来推算出具体的交货日期。

注意,这个示例中的交货期限是指不含周六周日的天数,所以不能直接用订货日期+交货天数来计算。

WORKDAY函数就是根据指定日期来推算出若干个工作日之后的日期,格式为:WORKDAY(开始日期,天数)。

案例5:推算母亲节的具体日期?

有些特殊的节日,比如母亲节、父亲节、感恩节等,其节日是根据特定月中的第几个星期几来定义的,如果要确定其日期就变得比较棘手。

例如母亲节是每年5月的第2个星期日,知道年份的话就可以用公式=CEILING(DATE(A2,4,30),7)+8来推算出该年的母亲节的具体日期。

公式解析:

①DATE(A2,4,30) 是指定年份4月30日的日期;

②利用CEILING函数将日期向上舍入到最接近的7的倍数(在Excel中,7的倍数所代表的日期序列正好都是星期六);

③得出5月份第一个星期六的日期,再加8,就是5月份第2个星期日即“母亲节”的日期。

这个公式利用了1900日期系统的特性,如果不明白的话套用公式即可。

今天分享的这五个例子,在日期类的问题中算是比较有难度的,涉及的函数大家平时也用的不多,建议收藏起来以备不时之需。

最新推荐