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")

相關連結:

如何將Excel中被儲存成「文字格式的數值」,轉回「真正的數值」?

Excel Date2String DateToString Convert Converter DATEVALUEfunction TEXT