辦公室秘技 - 如何用Excel處理日期

相信在辦公室工作的朋友都有用Excel，而日期是其中一個經常要處理的資料，其實Excel對日期的支援比大家想像中多，在此就跟大家分享一下，最後部份會教大家把看似日期的字串還原成Excel可讀的格式吧。

  
在Excel裡日期是一組數字，就如42069代表2015年3月3日，亦因如此兩個有效的日期是可以互相加減的。 


Year（日期）可以得到該日期中的年份，如Year（42069）= 2015
 
Month（日期）可以得到該日期中的月份，如Month（42069）= 3

Day（日期）可以得到該日期中的月份，如Day（42069）= 3


接下來的比較實用，因為你Excel是可以自動計算出該日期是在那個星期以及是星期幾
如果想知到該日是在一年中的那個禮拜（1-52）的話可以用weeknum這個公式

Weeknum（日期, n）

 
如想知道任何一天是星期幾則用weekday這公式

Weekday（日期, n）


公式中的參數n是指你希望星期中日子的順序，例如如果n是1的話公式結果1就代表星期日，2代表是星期一等等，如果n是2的話，公式結果的1就代表星期一，如此類推。


上面的都是Excel裡內置的公式，操作簡單，沒甚麼了不起，接下來的才是今篇的精華所在，如何把不同格式的日期變換成Excel可讀的格式。最重要是抓住重點，那就可以靈活運用。

例子1：Excel能接受YYYY-MM-DD／DD-MMM-YYYY的日期格式但就不能識別YYYYMMDD的格式，所以20150615在Excel眼中只是一個數字。要令其可以被Excel當成日期運算的話就需要把它轉換成日期的格式，而秘密就是TEXT這個公式。

在B1儲存格中輸入上面的公式就可以把20150615變成Excel能識別的日期格式

=TEXT(LEFT(A1,4)&"-"&MID(A1,5,2)&"-"&RIGHT(A1,2),"YYYY-MM-DD")

＊大家可以自行把A1換成其他儲存格


TEXT這個公式分開兩個部份，前半部是輸入的字串，正如之前所述，Excel能接受YYYY-MM-DD的日期格式，所以第一步就要把20150615變成2015-06-15，竅門就在於如何靈活運動字串處理公式LEFT、MID和RIGHT。

簡單介紹一下這三條公式，LEFT和RIGHT的用法很簡單，分別是從左或右讀取多少個字元，例如LEFT(A1,4)，就是在A1儲存格從左面開始讀取4個字元，如上面的例子結果就會是2015。而MID就略為複雜一點，簡單一點說就是從字串中間由第幾個字元從左至右讀多少個字元，例如MID(A1,5,2)，就是在A1儲存格由第五個字元（0）讀取兩個字元，因此公式的結果是06。

所以LEFT(A1,4)、MID(A1,5,2)、RIGHT(A1,2)的結果其實就分別是2015、06、和15，接下來就要用"-"把三者串起來變成2015-06-15。


而TEXT的另一個部份就是要告訴EXCEL如何讀取這段文字，"YYYY-MM-DD"正正是代表4個數字的年份，兩個數字的月和日。這樣就大功告成了。

可以用上述的Year、Month、Day公式來驗證一下是否轉換成功

A2和B2都是用Month這公式但只有B2能成功傳回月份=6，證明轉換成功了。



例子2︰如果日期的格式是有英文月份的簡寫，轉換時次序就要用DD-MMM-YYYY。

如果你看得明例子1中的LEFT、MID、RIGHT的用法的話這個轉換就應該毫無難度。

再用Month公式驗證一下，同樣轉換成功。


除了"-"之外，"/"同樣可以作為年月日的分隔符號。只要掌握到箇中方法，無論多刁鑽的日期（例如2015.06.15、JUN152015等等）格式都可以轉換成Excel能識別日期格式以作運算，希望大家能在日常工作上用得著吧。




我的Facebook專頁︰https://www.facebook.com/dennisckkblog