Excel儲存格式日期轉文字變成數字序號的解法

其實這篇文章就是Excel假敖（台語）的系列文，在程式設計師的眼中，Excel是一個非常討厭的東西，原因是因為他對使用者太過於友善，太過友善的後果就是會自動幫使用者「背景」處理很多的「值」的轉換，而這些轉換動作就是程式設計師最不想要的動作，因為他將會破壞掉原來預期的文字格式。當文字格式被破壞掉後，使用者上傳到你的程式中就會被解析成錯誤格式。演變到最後，變成是你要下海幫使用者轉資料，那當初何須要寫一個匯入轉檔的程式碼呢？

Excel日期轉文字的錯誤範例

這個例子來說，當使用者在Excel裡面輸入的日期值，而Excel也正確的辨識到這是一個正確的日期值，當使用者按下轉換成文字格式的時候，Excel卻幫你轉換成一組序號式的數字，這個假敖的行為，讓使用者跟程式設計師都崩潰了。以下是範例：

Step 1. 使用者在Excel裡面輸入四組日期

2016/07/20
2016-07-20
'2016/07/20
20160720

Step 2. 對欄位按下文字轉換後，我們發現前兩組都跳成了數字序號，如圖所見，2016/07/20被轉換成數字42571。但是我們也發現到，最後面兩組日期值並沒有被影響到，一個是加入單引號「'」，一個是把分隔符號去除掉讓Excel無法啟動假敖模式來自動辨識與轉換。

Excel在日期轉文字的背後，幫忙做了什麼？

42571這組神秘的數字絕對不是明牌，而是Excel啟動了DATEVALUE函式幫使用者進行轉換，轉換的原理很簡單，就是幫你計算從1900/01/01這一天開始，到你給定的這個日期，總共有幾天？而1900/01/01是第一天來計算，到2016/07/20總共是42571天。

有興趣的人可以參考一下微軟官方的說明：Office Excel DATEVALUE function

所以理解了這種序號式的數字來源後，是否你想要幫Excel陳情一下，說這個數字是有原由的，只要不要破壞到原始資料就好。等等，請你再仔細想想，如果我今天要記錄的是某大百年公司的產品資料沿革，然後我輸入下列資料後並轉換回文字時，你覺得會怎樣？

1989/12/31  本公司發明了第一項產品
1900/01/01  本公司發明了第二項產品

轉換結果如下，記得不要罵髒話喔！（Excel看不懂1900年以前的日期，因此把它當文字看，WTF！）想想看如果你有一串需要分析的資料中，混雜著一百年前以前的日期（例如老公司、考古、各種資料數據研究等），光是日期你要耗費多少時間處理？

1989/12/31 本公司發明了第一項產品 1 本公司發明了第二項產品

讓Excel正確的轉換日期到文字：方法一

其實一開始的範例就已經有解答了，答案就是手動的把所有你要轉換的日期加上單引號「'」，這樣就解決了。如果你的資料筆數只有十來筆，那麼這個方法是最快的。如果你的資料是破百筆上千筆，除非你用一些文字處理工具，不然你還是放棄手動加單引號的作法。

喔對了，可能你會想要用函式來進行字串相加的方式對吧，思路對但方法錯（詳見方法二），我已經幫你證明行不通了，如下：

A           B
----------  -----------
2016/07/20  =("'" & A1)

B的答案是「'42571」，不要罵髒話。

讓Excel正確的轉換日期到文字：方法二

方法二就是使用正確的Excel函式（TEXT）來幫我們處理這個棘手的問題。指令如下：

=TEXT(A1, "yyyy/MM/dd")

成果如下圖，看著這兩個日期值靠右靠左，前後欄位值頭尾相連，有一種莫名的感動。（注釋：當Excel把值顯示成靠左，這表示他已經是文字型態了。）

同場加映

等等，那個自訂的20160720出現「#VALUE!」錯誤，要不要幫忙處理一下啊！其實自訂格式是另外一種解法，不需要處理，因為Excel看不懂，就不會假敖，也就沒有日期轉換文字的問題。這個「#VALUE!」錯誤是因為我直接下拉套用上面的TEXT指令所致，要讓他正確的顯示也很簡單，或許這個可以用來取代在那邊不斷的LEFT、RIGHT、MID來的快速吧！

=TEXT(A4, "0000-00-00")