今天世杰老师给大家讲三个关于日期格式与计算的案例。这三个案例在日常的工作中出现的频率很广。建议先收藏后学习。
TEXT函数是一个十分有用全经常运用于调整各种格式的情形,下面的几个最常见于时间格式的调整。
A.将日期转换成“00000000”的格式
在C2单元格中输入公式:=TEXT(A2,"yyyymmdd")
B.将日期转换成“0000-00-00”的格式
C2单元格中输入公式:=TEXT(A2,"yyyy-mm-dd")
C.将日期转换成“0000年00月00日”的格式
C2单元格中输入公式:=TEXT(A2,"yyyy年mm月dd日")
D.将日期转换成"一"或者“星期一”的格式
C2单元格中输入公式:=TEXT(A2,"aaa")
如果要生成“星期一”的格式的话在C2单元格中输入公式:=TEXT(A2,"aaaa")
EDATE函数是返回指定日期几个月后的日期是那一天。该函数的语法为:
=EDATE(日期,要经过几个月),该函数的第二个参数只能以月来计算,比如3个月,那么第二个参数就是3.该函数主要用于计算合同的到期日,或者员工的试用期。
如下图所示,计算借款的还款日。
在C2单元格中输入公式:=EDATE(A2,B2),确定后向下填充。
该函数返回 start_date 和 end_date 之间的天数占全年天数的百分比。
函数语法:YEARFRAC(start_date, end_date, [basis])
参数说明:Start_date 必需。一个代表开始日期的日期。End_date 必需。一个代表终止日期的日期。Basis 可选。要使用的日计数基准类型。
对于上面的Basis的参数最常用的是1,2,3这三个参数。
工龄分布的规则为:1年以下;1年(含)-3年;3(含)-5年;5年以上(含)。
在D2单元格中输入公式:
=VLOOKUP(YEARFRAC(C2,TODAY(),1),{0,"不足1年";1,"1-3年";3,"3-5年";5,"5年以上"},2,1)
或可以输入公式:
=VLOOKUP((TODAY()-C2)/365,{0,"不足1年";1,"1-3年";3,"3-5年";5,"5年以上"},2,1)
或可以输入公式:
=VLOOKUP(DATEDIF(C2,TODAY(),"d")/365,{0,"不足1年";1,"1-3年";3,"3-5年";5,"5年以上"},2,1)
当然大家也可以使用前期给大家讲过的LOOKUP函数:
=LOOKUP(YEARFRAC(C2,TODAY(),1),{0,1,3,5},{"不足1年","1-3年","3-5年","5年以上"})
或可以输入公式:
=LOOKUP((TODAY()-C2)/365,{0,1,3,5},{"不足1年","1-3年","3-5年","5年以上"})
或可以输入公式:
=LOOKUP(DATEDIF(C2,TODAY(),"d")/365,{0,1,3,5},{"不足1年","1-3年","3-5年","5年以上"})
每一个人的小有成就,源于对自己的不断要求和努力,希望和你一起加油。
我是世杰,我们下期见。
作者:我是世杰,财务excel深度玩家,坚持每天分享财务excel干货,微信公众号:24财务excel
推荐阅读