我試圖找出兩天“xx 年 xx 天 xx 小時 xx 分鐘”格式之間的區別。我的開始日期在 A 列中，結束日期在 B 中。計算 C 列中的差異。
我B1-A1在 C 列中使用公式并在 C 列中使用自定義格式"yy" years "dd" days "hh" hours "mm" minutes"。如果開始和結束年份不同，它作業正常。但如果相同，C 列報告錯誤的年份和日期。（顯示為 99 年零 30 天）。
如何解決？
筆記：
- 公式負責格式化。
- 由于每個月的天數不同，天數有一個非常復雜的公式。但這應該顯示更準確的答案
index(split(B2, " "), ,2) >= index(split(A2, " "), ,2)添加到原始
僅洗掉前導空值：
=INDEX(IFERROR(TRIM(REGEXREPLACE( TEXT( DATEDIF(A2:A, B2:B, "Y"), "00")&CHOOSE(MATCH( DATEDIF(A2:A, B2:B, "Y"), {0,1,2})," years ", " year ", " years ")& TEXT(IF(B2:B-A2:A<1,,DATEDIF(A2:A, B2:B, "YD")), "00")&CHOOSE(MATCH(IF(B2:B-A2:A<1,,DATEDIF(A2:A, B2:B, "YD")), {0,1,2})," days ", " day ", " days ")& TEXT( TEXT(B2:B-A2:A, "H"), "00")&CHOOSE(MATCH(TEXT(B2:B-A2:A, "H") *1, {0,1,2})," hours ", " hour ", " hours ")& TEXT(INT(TEXT(B2:B-A2:A, "M.S")), "00")&CHOOSE(MATCH(TEXT(B2:B-A2:A, "M.S")*1, {0,1,2})," minutes"," minute"," minutes"), "^\b(?:00 years )?(?:00 days )?(?:00 hours )?(?:00 minutes)?", ))))
要
NOT洗掉任何空值：=INDEX(IFERROR(REGEXREPLACE( TEXT( DATEDIF(A2:A, B2:B, "Y"), "00")&CHOOSE(MATCH( DATEDIF(A2:A, B2:B, "Y"), {0,1,2})," years ", " year ", " years ")& TEXT(IF(B2:B-A2:A<1,,DATEDIF(A2:A, B2:B, "YD")), "00")&CHOOSE(MATCH(IF(B2:B-A2:A<1,,DATEDIF(A2:A, B2:B, "YD")), {0,1,2})," days ", " day ", " days ")& TEXT( TEXT(B2:B-A2:A, "H"), "00")&CHOOSE(MATCH(TEXT(B2:B-A2:A, "H") *1, {0,1,2})," hours ", " hour ", " hours ")& TEXT(INT(TEXT(B2:B-A2:A, "M.S")), "00")&CHOOSE(MATCH(TEXT(B2:B-A2:A, "M.S")*1, {0,1,2})," minutes"," minute"," minutes"), "^\b00 years 00 days 00 hours 00 minutes", )))
讓它與火星、木星和土星對齊：
=INDEX(IFERROR(REGEXREPLACE(REGEXREPLACE( TEXT( DATEDIF(A2:A, B2:B, "Y"), "00")&CHOOSE(MATCH( DATEDIF(A2:A, B2:B, "Y"), {0,1,2})," years ", " year ", " years ")& TEXT(IF(B2:B-A2:A<1,,DATEDIF(A2:A, B2:B, "YD")), "000")&CHOOSE(MATCH(IF(B2:B-A2:A<1,,DATEDIF(A2:A, B2:B, "YD")), {0,1,2})," days ", " day ", " days ")& TEXT( TEXT(B2:B-A2:A, "H"), "00")&CHOOSE(MATCH(TEXT(B2:B-A2:A, "H") *1, {0,1,2})," hours ", " hour ", " hours ")& TEXT(INT(TEXT(B2:B-A2:A, "M.S")), "00")&CHOOSE(MATCH(TEXT(B2:B-A2:A, "M.S")*1, {0,1,2})," minutes"," minute "," minutes"), "^\b00 years 000 days 00 hours 00 minutes", ), "(0)(\d{2})", " $2")))
更新：
平均使用：
=INDEX(AVERAGE(IF(B2:B A2:A, B2:B-A2:A, )))或者：
=INDEX(AVERAGE(IFERROR(1/(1/(B2:B-A2:A)))))取決于您需要的靈敏度
uj5u.com熱心網友回復：
如果兩個值相同，則放置條件，否則應該做減法
=IF(A1=B1,"0",B1-A1)uj5u.com熱心網友回復：
這是因為 GS 將零日期視為其日歷的第一天，即 1899-12-30。您可以使用以下公式獲得正確的結果：
=TEXT(DATEDIF(A2,B2,"Y"),"00 \y\ear\s ") & TEXT(TRUNC(EDATE(B2,-12*(DATEDIF(A2,B2,"Y")))-A2), "00 \da\y\s ") & TEXT(ABS((B2-TRUNC(B2,0))-(A2-TRUNC(A2,0)) 1),"hh \hour\s mm \minut\e\s")或使用陣列公式：
=ArrayFormula(IF(LEN(A2:A), TEXT(DATEDIF(A2:A,B2:B,"Y"),"00 \y\ear\s ") & TEXT(TRUNC(EDATE(B2:B,-12*(DATEDIF(A2:A,B2:B,"Y")))-A2:A), "00 \da\y\s ") & TEXT(ABS((B2:B-TRUNC(B2:B,0))-(A2:A-TRUNC(A2:A,0)) 1),"hh \hour\s mm \minut\e\s"),))該公式考慮了閏年并計算了經過的天數。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/350480.html上一篇：Google表格功能中的兩個條件