如何获取两个日期之间的差异以及结果之间的差异

如何获取两个日期之间的差异以及结果之间的差异

我有一张 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")

相关内容