解決 Excel 日期時間欄位值，混合多種時間格式的問題

日期：

 Excel 日期時間欄位值雖然都是西元記年，但是混合多種時間格式，例如：「年/月/日 時:分:秒」、「年/月/日 時:分」、「年-月-日 時:分:秒」、「年/月/日」、「年-月-日」以及一長串的數字，造成無法時間排序。解決方式是：透過 VALUE 函數將文字轉換成一致的日期時間序列值，再修改儲存格格式為人類可讀的日期時間。

working with chaos data
Photo by Kristin Hardwick on StockSnap


問題狀況

因為從多個資料源組合而成，最終合併的時間欄位值，混合了多種時間格式。導致無法使用 Excel 時間篩選排序功能。

混合多種時間格式的欄位值


解決方式

1. 儘管有多種時間表示方式，透過 VALUE 函數，將文字轉換成日期時間序列值。微軟文件提到：

Excel 會以連續的序列值儲存日期，以便將日期用於計算。 根據預設，1900 年 1 月 1 日是序列值 1，而 2008 年 1 月 1 日因為是 1900 年 1 月 1 日之後的第 39,447 天，所以其序列值是 39,448。

B2 儲存格公式

= VALUE(A2)

套用 VALUE 函數

2. 選取時間欄位 (上圖的 B 欄)，設定儲存格格式

以 Mac 版 Microsoft Excel 為例，自訂儲存格格式輸入「yyyy/mm/dd hh:mm:ss」(日期時間格式詳見微軟文件)

修改 Excel 日期時間格式

如果是 Google 試算表，則可線上直接點選下拉選單進行微調
修改 Google Sheet 日期時間格式

3. 修改後的欄位，可以看到適合閱讀的日期時間格式，而不是一長串數字。除了讓不同格式的欄位值一致，也可以進行篩選排序。


如果時間格式比較特別，VALUE 函數將會無法轉換成數字，而顯示 #VALUE! 錯誤，則需要額外處理。

留言

張貼留言