sxt66329的部落格

幾天前，同事拿了一張慘不忍睹的工作表來求我，因為日期欄位資料格式沒統一，無法篩選及統計，我整理日期欄位資料格式沒統一情形如下：

這圖片說明了日期格式有民國、西元、文字、空格等等，我看了都想哭，真是亂七八糟的工作表，無奈人家求助，只好逐一整理。

一、儲存格式統一

原來通用格式所造成慘不忍睹日期欄位資料格式，統一為西元年格式(yyyy/mm/dd)，如下圖

三、統一空白格式及判斷民國年的格式

在儲存格空白值有分為無資料、半形空白鍵及全形空白鍵，這些資料易使公式判斷上出現異常，仍需用IF函數來統一。另外，民國年的格式也是要先去除，才能改為西元。因此，公式為

更改民國年後，儲存格就沒有民國字樣了。

四、去除年月日文字格式

日期格式比較繁瑣是將文字日期改成西元數字日期，去除年月日步驟，使用了REPLACE、SEARCH函數，因此，公式為

五、文字形式之西元日期轉化

用DATEVALUE及數就可以將文字形式之西元日期轉化為日期格式。

六、西元年2位數之文字形式轉化

遇到西元年2位數之文字形式(mm/dd/yy)，這就不太好玩，只能逐一拆解組合。首先，將年轉化4位數，公式為DATEVALUE(TEXT(VALUE(RIGHT(A8,2)+2000),"G/通用格式")；月日則由右拆解年及/後，再補左側剩下的文字，公式為&"/"&LEFT(A8,LEN(A8)-3)，整個公式轉化日期格式為DATEVALUE(TEXT(VALUE(RIGHT(A8,2)+2000),"G/通用格式")&"/"&LEFT(A8,LEN(A8)-3))，但有時年為民國年(二位數)時，就設公式為DATEVALUE(TEXT(VALUE(LEFT(H8,3))+1911,"@")&RIGHT(H8,LEN(H8)-3)))來轉化民國年。

七、民國年有三位數之轉化

若有經年月日文字形式轉成/的文字格式時，公式為IF(H17="",C17,IFERROR(DATEVALUE(H17),DATEVALUE(TEXT(VALUE(LEFT(H17,3))+1911,"@")&RIGHT(H17,LEN(H17)-3))))，將民國年轉化西元年。

最後，各種日期格式的，逐一轉化成西元年日期格式了，所以，大家在輸入儲存格時，切記格式要統一，否則很麻煩。