Excel 如何轉換文字格式的日期時間格式 (使用 VALUE, TEXT 函數)

說明 Excel 如何轉換文字格式的日期時間格式，例如將下圖文字格式的 A 欄「年-月-日時:分:秒」，轉換成 B欄「年/月/日時:分」格式。

補充：如果要將數字轉換成時間，請點選：解決 Excel 時間變數字的問題

步驟1: 將文字轉換成日期時間數值

多數日期時間欄位值是 Excel VALUE 函數可以辨識的格式。可以直接轉換成日期時間數值。

B2 儲存格 = VALUE(A2)

上圖 B 欄整數部分是 1900/1/1 到指定日期的天數 (資料來源: DATEVALUE)，小數點數字部分則是代表「0:00:00 (12:00:00 A.M.) 到 23:59:59 (11:59:59 P.M.) 的時間」(資料來源: TIMEVALUE)。

如果原始資料是 VALUE 函數無法辨識的格式，例如「20150318 1345」會回傳「#VALUE!」，則請參考 Excel-轉換文字字串為標準日期數值格式(DATE,TIME,MID) 作法。

步驟2: 自訂時間格式

B欄儲存格的格式目前是「通用格式」

選取要修改的儲存格，從「通用格式」切換到「自訂」

因為要轉換成「年/月/日時:分」，請在「類型」處，自行輸入「YYYY/MM/DD HH:MM」。再按確定，即可完成轉換。(關於 YYYY、MM、DD 格式代碼詳細說明，請參考 TEXT 函數 - Office 支援。)

如果不想用上面方法，可以直接修改 B2 儲存格的函數

B2儲存格 = TEXT(VALUE(A2), "YYYY/MM/DD HH:MM")

如果目的是讓 A 欄可以依照時間遞增遞減排序，而時間格式並不重要，則可以採取以下步驟

步驟 (3) 將 - 符號，取代為 - 符號

選好要轉換的 A 欄，尋找 - 符號，再取代成為 - 符號 (沒看錯，是相同符號)。可讓 A 欄可以依照時間遞增遞減排序。

原因解釋：因為文字格式的排序選項是依照文字 A-Z 或 Z-A 排序，而無法依照時間遞增遞減排序。但是使用尋找取代就可以解決問題。但是如果逐一點選儲存格 (快速鍵 F2)，內容不作任何修改，再按 Enter ，會發現該欄位可以依照時間遞增遞減排序。原本的 F2 再按 Enter 的步驟，改成尋找取代可以達到批次修改與按 Enter。

無效的嘗試

選取文字格式的 A 欄儲存格後，想要直接自訂格式，但是並無法成功轉換格式。因為必須先轉換成日期時間數值後，才能轉換格式。

參考資料