我有一张 Excel 表格,第一行有两个单元格,分别是入职日期和当前日期。我想要以“年、月、日”的形式给出答案。
类似地,另一行内容相同但年份不同,我希望了解两行结果之间的差异。
答案1
我确信一定有更优雅的方法来实现这一点,但这就是我解决问题的方法:
公式
B2 和 B3
=TODAY()
C2
=DATEDIF(A3,B3,"Y") & " Years, " & DATEDIF(A3,B3,"YM") & " Months, " &I7& " Days"
C3
=DATEDIF(A4,B4,"Y") & " Years, " & DATEDIF(A4,B4,"YM") & " Months, " & I8 & " Days"
C4
=E6&" Years, "&E9&" Months, "&E12&" Days"
E2
=TEXTSPLIT(C2,", ")
E3
=TEXTSPLIT(C3,", ")
E6
=TEXTBEFORE(E3," Y")-TEXTBEFORE(E2," Y")
E9
=TEXTBEFORE(F3," M")-TEXTBEFORE(F2," M")
E12
=TEXTBEFORE(G3," D")-TEXTBEFORE(G2," D")
I3
=DATEDIF($A3,$B3,"y")
I4
=DATEDIF($A4,$B4,"y")
J3
=DATEDIF($A3,$B3,"ym")
J4
=DATEDIF($A4,$B4,"ym")
I7
=TEXT((B3-EDATE(A3,(I3*12)+J3)),"d")
I8
=TEXT((B4-EDATE(A4,(I4*12)+J4)),"d")