英文:
Excel Formula required for Date Calculation
问题
在MS Excel中,你可以使用以下公式进行日期计算,以获得类似于 "31年7个月6天" 的答案:
=DATEDIF(A2, B2, "Y") & " years " & DATEDIF(A2, B2, "YM") & " months " & DATEDIF(A2, B2, "MD") & " days"
请确保将A2和B2替换为你实际的日期单元格引用。这个公式将计算两个日期之间的年、月和日的差距,并将它们以指定的格式串联起来。
英文:
Required MS Excel formula for date calculation as below stated. Need answer like 31years 7months 6 days.
Required MS Excel formula for date calculation as below stated. Need answer like 31years 7months 6 days.
答案1
得分: 3
=TEXTJOIN(" ",,DATEDIF(A1,B1,{"y";"ym";"md"})&" "&{"years";"months";"days"})
请注意,需要将"
替换为双引号"
,以在公式中正确使用引号。
英文:
Perhaps
=TEXTJOIN(" ",,DATEDIF(A1,B1,{"y";"ym";"md"})&" "&{"years";"months";"days"})
though with the caveat that the official MS documentation for the DATEDIF
function states that use of the "md" parameter can lead to inaccurate results.
答案2
得分: 1
不是最干净的方法,也许使用Excel 365的高级功能可以在单个公式中完成,但在旧版本上无法做到。您需要使用辅助列来计算一切(但您可以隐藏它们,只显示最终结果:
我使用了3个辅助列(年、月、日):
单元格 C7:=DATEDIF(A7;B7;"Y")
单元格 D7:=DATEDIF(EDATE(A7;C7*12);B7;"M")
单元格 E7:=B7-EDATE(EDATE(A7;C7*12);D7)
单元格 F7:=C7&" years "&D7&" months " & E7 & " days"
只需向下拖动。
然后在第2行中计算总计:
单元格 C2:=SUM(C7:C8)+INT(SUM(D7:D8)/12)
单元格 D2:=MOD(SUM(D7:D8);12)
单元格 E2:=SUM(E7:E8)
单元格 F2:=C2&" years "&D2&" months " & E2 & " days"
正如我所说,这不是最干净的方法,但可以完成工作。
请注意,我的函数中的参数分隔符是分号 ;
。根据您的区域设置,您可能需要使用逗号 ,
而不是 ;
。
英文:
Not the cleanest way, maybe with advanced functions of Excel 365 you can get this on a single formula, but not on older versions. You need helper columns to calculate everything (but you coould hide them and just show the final results:
I'm using 3 helper columns (years, months, days):
Cell C7: =DATEDIF(A7;B7;"Y")
Cell D7: =DATEDIF(EDATE(A7;C7*12);B7;"M")
Cell E7: =B7-EDATE(EDATE(A7;C7*12);D7)
Cell F7: =C7&" years "&D7&" months " & E7 & " days"
Just drag down.
Then for grand total in row 2:
Cell C2: =SUM(C7:C8)+INT(SUM(D7:D8)/12)
Cell D2: =MOD(SUM(D7:D8);12)
Cell E2: =SUM(E7:E8)
Cell F2: =C2&" years "&D2&" months " & E2 & " days"
As I said, not the cleanest but if gets the work done.
Please, note my argument separator in functions is the semicolon ;
. Depending on your regional settings you may need comma ,
instead of ;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论