說明 Excel 如何轉換文字格式的日期時間格式，例如將下圖文字格式的 A 欄「年-月-日 時:分:秒」，轉換成 B欄「年/月/日 時:分」格式。
如果原始資料是 VALUE 函數無法辨識的格式，例如「20150318 1345」會回傳「#VALUE!」，則請參考 Excel-轉換文字字串為標準日期數值格式(DATE,TIME,MID) 作法。
B欄儲存格的格式目前是「通用格式」
選取要修改的儲存格，從「通用格式」切換到「自訂」
因為要轉換成「年/月/日 時:分」，請在「類型」處，自行輸入「YYYY/MM/DD HH:MM」。再按確定，即可完成轉換。(關於 YYYY、MM、DD 格式代碼詳細說明，請參考 TEXT 函數 - Office 支援 。)
如果不想用上面方法，可以直接修改 B2 儲存格的函數
無效的嘗試
選取文字格式的 A 欄儲存格後，想要直接自訂格式，但是並無法成功轉換格式。因為必須先轉換成日期時間數值後，才能轉換格式。
參考資料
補充：如果要將數字轉換成時間，請點選：解決 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 欄儲存格後，想要直接自訂格式，但是並無法成功轉換格式。因為必須先轉換成日期時間數值後，才能轉換格式。
參考資料
- VALUE 函數 - Office 支援
- Excel-轉換文字字串為標準日期數值格式(DATE,TIME,MID) @ 學不完．教不停．用不盡 :: 痞客邦 PIXNET :: 將不標準的日期時間格式「年月日 時分」改成「年/月/日 時:分」
- TEXT 函數 - Office 支援
- Excel-改變日期格式(TEXT) @ 學不完．教不停．用不盡 :: 痞客邦 PIXNET :: 將日期格式「年/月/日」改成「年-月-日」
- DATEVALUE 函數 - Office 支援
- TIMEVALUE 函數 - Office 支援
- 日期及時間函數 (參照) - Office 支援
- Excel: convert text to date and number to date
- [EXCEL]我的日期排序出錯誤了 請問是什麼原因呢 | Yahoo奇摩知識+
留言
張貼留言