3140 |
Excel如何將純數值轉換為對應日期-TEXT函數 |
蘇學文:錦子老師您好,請問A欄計劃起始日期為:1090401,B欄計劃結束日期為:1091231,需要在金C欄求得計劃期間的"天數"。
1.請問要如何將1090404轉換為109年4月1日的民國日期格式?
2.日期格式設好後,要如何將起訖日相減,得到這段期間的日數?
錦子老師:針對第1個問題的解決方法如下:
步驟1:點取D2儲存格輸入公式:
=TEXT(DATE(LEFT(A2,3)+11,MID(A2,4,2),RIGHT(A2,2)),"ggyy年mm月dd日")
再將公式複製到D2:E6儲存格。
【公式說明】
=TEXT(DATE(LEFT(A2,3)+11,MID(A2,4,2),RIGHT(A2,2)),"ggyy年mm月dd日")
公式1:LEFT(A2,3)
傳回A2儲存格內容左邊三個字元。
公式2:VALUE(MID(A2,4,2))
傳回A2儲存格內容第4個字元開始抓取2個字元,這是月份值。
公式3:VALUE(RIGHT(A2,2))
傳回A2儲存格內容右邊2個字元,這是日值。
公式4:DATE(LEFT(A2,3)+11,MID(A2,4,2),RIGHT(A2,2))
將公式1運算結果加11與公式2運算結果與公式3運算結果結合而成民國日期字串。
為何年份要加11,是因為=DATE(109,4,1)是西元2009/4/1,而民國109是2020,故需要加11。
公式5:TEXT(公式4,"ggyy年mm月dd日")
將公式4運算結果變成民國日期顯示。
gg:是加上民國字串。
ggg是加上中華民國字串。
不論1個y、2個yy、4個yyyy都是一樣顯示3位數的民國年份。
針對第2個問題的解決方法如下:
步驟1:點取C2儲存格輸入公式:
=DATE(LEFT(B2,3)+11,MID(B2,4,2),RIGHT(B2,2))-DATE(LEFT(A2,3)+11,MID(A2,4,2), RIGHT(A2,2))
再將公式複製到C3:C6儲存格。
【公式說明】
=DATE(LEFT(B2,3)+11,MID(B2,4,2),RIGHT(B2,2))-DATE(LEFT(A2,3)+11,MID(A2,4,2), RIGHT(A2,2))
公式1:LEFT(B2,3)+1911
傳回B2儲存格內容左邊三個字元數值再加上11轉換為西元年份,。
公式2:MID(B2,4,2)
傳回B2儲存格內容第4個字元開始抓取2個字元,這是月份值。
公式3: RIGHT(B2,2)
傳回A2儲存格內容右邊2個字元,這是日值。
公式4:DATE(公式1,公式2,公式3)
用DATE函數傳回指定日期(年公式1,月公式2,日公式3)。
B2
A2
公式5:DATE(B2西元年份,月,日)- DATE(A2西元年份,月,日)
B2儲存格日期減掉A2儲存格日期,計算出差距天數。
以上就是今天主要學習的知識點,希望對大家有所幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~
更多相關影片教學:請點我
更多相關文章:請點我
留言列表