Excel日期資料函數轉換:設定格式及數值格式代碼

Excel有可能會拿到文字或數值的日期資料,本文介紹如何利用函數轉換成純粹的日期格式,進而將相同的日期值以不同格式呈現,包括進階的數值格式代碼。

一、Excel日期表

原始資料如圖所示,每個月固定兩行數字表示日期,上面一排是1到15,下面是16到30,最前面是月初完整日期,後面最後加一個31。

Excel日期資料函數轉換:設定格式及數值格式代碼 1

二、日期函數公式

想要依照上個步驟的原始資料,以Excel函數計算到當月份日期形式的儲存格值,可以設計公式:「=DATE(YEAR($A$1),MONTH($A$1),B1)」,這裡的「YEAR($A$1)、MONTH($A$1)」分別是以儲存格「A1」得到該日期所屬的年及月,加上「$」是固定引用的功能,在往右拉往下拉快速複製公式時,同樣都是引用A1儲存格,不會跟著變動。

執行結果可見得確實得到了相對應的日期。

Excel日期資料函數轉換:設定格式及數值格式代碼 3

三、文字函數合併

還可以利用文字函數將數值轉換為文字,公式為「= TEXT($A$1,”YYYY-MM-“)&B1)」,這裡的「($A$1)」和上個步驟同樣是固定參照作用,年與月所參考的固定欄位資料,「-」為年月日的連接符號,「B1」會依照所參考的數值帶出當月日數,此格式仍然為文字,因此可以用「&」將前後兩串文字連接起來。

Excel日期資料函數轉換:設定格式及數值格式代碼 5

四、文字轉換日期

由於「2022-03-1」是Excel容易辨識的日期資料型態,可以簡單設計公式「–B6」,Excel會在計算過程中同時進行資料自動轉換,於是便技巧性的將文字轉換為日期「3/1」。

Excel日期資料函數轉換:設定格式及數值格式代碼 7

五、設定儲存格格式

大費周章將數值或文字資料轉換成純粹的日期型態,是因為Excel為日期資料提供了很多配套工具可以使用。例如選取適當的資料範圍,按Ctrl+1快速鍵進入「設定儲存格格式」視窗,目前已經是日期資料型態,可以看到它在數值類型會自動跑到「日期」,因此能迅速切換擇不同格式的顯示狀態,目前為「3/14」,也就是截圖所看到的模樣。

Excel日期資料函數轉換:設定格式及數值格式代碼 9

六、日期格式切換

同樣如同截圖所示,「周二」、「星期二」、「三月三日」,其實儲存格值沒有任何變動,純粹是不同的日期格式切換。

Excel日期資料函數轉換:設定格式及數值格式代碼 11

七、數值格式代碼

除了Excel預設既定的日期格式,同樣在「設定儲存格格式」將「數值」的「類別」點選切換到「自訂」,可以輸入更加靈活的數值格式代碼,例如「mmm」、「mmmm」、「ddd」、「dddd」,分別是簡寫和全稱的英文月份及星期。

Excel日期資料函數轉換:設定格式及數值格式代碼 13

善用日期格式呈現報告

本篇文章一開始是簡單的數字資料,在實務上可能是為了方便起見如此輸入,但也有可能是系統跑出來的報表便是如此狀況,因此有必要進行Excel文字日期的轉換。而在轉換之後,便可以利用Excel工具將同樣的日期值以不同格式呈現,編製管理報告時可能因依照重點不同而有不同格式的呈現,運用得當會使得報告更加完美。

每天學習,每天充電:Excel指令文章合集

Excel日期資料函數轉換:設定格式及數值格式代碼 15
加入Line社群,口袋裡的Excel小教室!