如何驗證 Excel 欄位值是日期時間

日期：

當資料來源來自外部資料，如何驗證欄位值是日期時間？西元紀年的時間可以透過 YEAR 函數檢查，正確回傳年份數字，錯誤則回傳 #VALUE! 錯誤。民國紀年的時間，則需要事先轉換到西元紀年，再用 YEAR 函數處理。

Photo by Redd Angelo on StockSnap


問題描述

正確的日期時間欄位值會有很多可能

2021/9/26 上午 10:43:29 

2021/9/26 10:32:29

2021/9/26

2021-09-26

9/26/2021

2021年9月26日

逐一撰寫不同規則的檢查方式，會造成維護困難

解決方式

使用 Excel 原生的 YEAR 函數，可以達到驗證日期欄位值的效果。微軟網頁提到 YEAR 函數「傳回對應於日期的年份。 年份會傳回成 1900-9999 範圍內的整數。」而參照儲存格的 #VALUE! 錯誤，則作為判斷不符合格式的檢查條件。

函數說明：當儲存格 B2 欄位值不是日期時間則回傳 1，正確回傳 0

=IF(ISERROR(YEAR(B2)), 1, 0)

附帶說明：Excel 的 MONTH 與 DAY 函數都可以達到 YEAR 函數同樣檢查效果，再請視專案需求選擇合適的函數。

其他資料狀況

真實世界的資料總是比較複雜

  • 民國紀年的日期時間：需要先將民國日期轉成西元日期，才能套用上述公式。
  • 相對時間，例如「3 小時前」、「5 天前」、「7 週前」則無法透過 YEAR 函數處理，需要另外撰寫規則排除。

參考資料



留言

張貼留言