求计算在本单位工作年限

木希 · 发表于 2023-10-11 14:24

求计算在本单位工作年限

高个子 · 发表于 2023-10-11 14:45

=DATEDIF(TEXT(A3&"01","0000-00-00"),TEXT(B3&"01","0000-00-00"),"y")&"年"&IF(DATEDIF(TEXT(A3&"01","0000-00-00"),TEXT(B3&"01","0000-00-00"),"ym")+1=12,"",DATEDIF(TEXT(A3&"01","0000-00-00"),TEXT(B3&"01","0000-00-00"),"ym")+1&"月")

复制代码

小辛呀小辛 · 发表于 2023-10-11 15:12

=LET(a,DATEDIF(DATE(LEFT(A3,4),RIGHT(A3,2),1),DATE(LEFT(B3,4),RIGHT(B3,2)+1,1),"m"),b,ROUNDDOWN(a/12,0),b&"年"&a-b*12&"个月")

复制代码

看看OK不？问题是如果是整年的话会显示”n年0个月“，如果在意的话可以再加判断去除掉

quqiyuan · 发表于 2023-10-11 15:23

=DATEDIF(TEXT(A3,"0000-00"&"-1")-1,TEXT(B3,"0000-00"&"-1")+31,"y")&"年"&DATEDIF(TEXT(A3,"0000-00"&"-1")-1,TEXT(B3,"0000-00"&"-1")+31,"ym")&"个月"

quqiyuan · 发表于 2023-10-11 15:51

改进了一下

=TEXT(DATEDIF(TEXT(A3,"0000-00"&"-1")-1,TEXT(B3,"0000-00"&"-1")+31,"y"),"0年;;;")&TEXT(DATEDIF(TEXT(A3,"0000-00"&"-1")-1,TEXT(B3,"0000-00"&"-1")+31,"ym"),"0个月;;整;")

复制代码

		自动登录	找回密码
密码			免费注册

[求助] 求计算在本单位工作年限