2019年2月13日 星期三

在 Excel & Google Drive 中將日期轉為星期

很久以前我曾試著在 Excel 中將年月日三欄資料串成日期,但當時的方法祇能用在書面列印的資料,如果還要將日期用來計算,比方說算出當天是星期幾,那麼之前的方法就行不通了。所以後來我用 date & text 函數來轉換日期,轉換出來的結果還可以有不同的呈現方式,在實際應用上比較方便。

各種不同的日期呈現方式
圖、各種不同的日期呈現方式

比方說這學期我跟學生玩一些遊戲,並把每日最高得分放在網頁讓學生隨時可以查看。網頁的最左邊 2/11 對應到星期一,2/12 對應到星期二,這難道是我自己慢慢輸入的嗎?當然不是啊,那會累死人,我是用公式自動轉換的。

我原本是把每日最高分記錄在 Excel,後來想要讓學生可以隨時查看,所以轉放到 Google Drive 裡。當我把 Excel 檔的公式貼到 Google Drive 去,哎,出錯了,日期 & 星期顯示出了問題。

看了說明書才發現原來 Excel 跟 Google Drive 在一些函數格式的細節是不同的,所以要同時在 Excel & Google Drive 上使用函數時要記得調整一下。我怕日後我要使用時又忘了這些細節差異,所以寫一篇文章記錄一下。 :)

Google Drive 的日期計算函數

因為 Google Drive 的公式有彩色,比較容易分辨參數內容,所以我先從 Google Drive 講起。同樣的,一開始都是使用三個欄位來記錄年月日。

用三個欄位分別記錄年月日
圖、用三個欄位分別記錄年月日

利用 date 函數將前三欄年月日資料轉換成為日期。使用 date 函數時要把年、月、日所在的位置依序放入括號中,中間記得用逗號分隔開來。

使用 date 函數將數字轉為日期
圖、使用 date 函數將數字轉為日期

好的,現在 D2 這個格子呈現的資料已經是把 A2、B2、C2 這三格的資料轉成日期的結果了,那我們怎麼知道 D2 的這個日期是星期幾呢?在 Google Drive & Excel 都是用 text 這個函數來做轉換,但細節有點不同,以下是 Google Drive 的轉換方式。

首先,將 D2 放入 text 中,表示我們要把 D2 這欄資料進行轉換,轉換成什麼呢? "dddd" 這個參數告訴 text 函數要去計算剛剛傳日的資料是星期幾。記得引號也要加上去才行,而且 D2 與 "dddd" 之間要記得加逗號。

用 text 函數計算出特定日期是星期幾
圖、用 text 函數計算出特定日期是星期幾

我們有日期,又有星期,那就可以用 & 這個符號將資料串接起來了。不過,日期最前面的年份我不想呈現出來,那怎麼辦呢?同樣的,利用 text 函數可以達成我們的目的。

將 D2 的資料放進 text,然後用 "mm/dd" 參數告訴 text 函數:『我要你把 D2 的資料轉變成為文字,而且我祇要月 (mm) 日 (dd) 的資料就好。』

轉成月、日格式後,再用 & 符號把剛剛的轉換出來的星期幾資料串接起來。因為我們習慣的顯示方式是日期之後要加個小括弧,星期放在小括弧內,所以用 & "(" 及 & ")" 把 text(D2, "dddd") 包起來,完整的公式變成 & "(" & text(D2, "dddd") & ")"

利用 text 函數將日期轉為祇有月日格式
圖、利用 text 函數將日期轉為祇有月日格式

可是 2019/02/11 計算出來的是『星期一』,能夠簡短一點,變成『週一』就好了嗎?

沒問題,祇要稍微修改一下剛剛的參數就好了。我們前面不是用 "dddd" 參數要求 text 函數計算出特定日期是星期幾嗎?要改成週一、週二這種短格式的話,祇要改成 "ddd" 就好了,就這麼簡單。

用 text 函數計算出特定日期是星期幾 (簡短格式)
圖、用 text 函數計算出特定日期是星期幾 (簡短格式)

前面轉換出來的是 2/11(週一) 這樣的格式,如果我希望呈現更簡短一點,變成 2/11(一) 這樣該怎麼辦呢?

『我知道,我知道,剛剛 "dddd" 參數指示 text 函數將星期呈現為完整格式,"ddd" 變為簡短格式,所以要更簡短的話,祇要寫 "dd" 就好了對不對?』

啊,很遺憾,你猜錯了。前面有提到 "dd" 是要求 text 函數顯示該日期的『日』("mm" 則顯示月份),所以如果你下了 text(d2, "dd") 這樣的指令,顯示出來的結果會是 2/11(11),一整個讓人槌心肝。

那怎麼最簡短的顯示星期呢?其實也很簡單,你有沒有發現,不論是『星期一』或是『週一』,那個『一』都是在最右邊,所以我們祇要用 right 函數抓取 text(D2, "ddd") 計算結果的最右邊字元就好了。寫法是 right(text(D2, "ddd"))

利用 right 函數讓星期的呈現方式更簡短
圖、利用 right 函數讓星期的呈現方式更簡短

利用 date、text & right 三個函數,我們就能讓日期的顯示方式變得很多變,因應各種不同的需求。不過上面說的是 Google Drive 的使用方式,Excel 參數有點不太一樣,所以我們底下來說說 Excel 如何做到這樣的功能。

在 Excel 中計算日期與星期

其實在 Excel 與 Google Drive 的參數祇有一點點不同,所以底下會用很快的速度帶過。首先,我們同樣使用三個欄位分別記錄年、月、日。

在 Excel 中用三個欄位記錄年月日資料
圖、在 Excel 中用三個欄位記錄年月日資料

接著,用 date 函數將年、月、日這三欄資料轉變成為可以計算的日期資料,到這裡都沒有任何差別。

利用 date 函數將文字轉變成為可計算的日期
圖、利用 date 函數將文字轉變成為可計算的日期

重點來了,我們要計算特定日期是星期幾時,Excel 與 Google Drive 的指令是不同的。在 Google Drive 我們用 "dddd" 來要求 text 函數計算特定日期是星期幾,但在 Excel 中,我們要用 "[$-404]aaaa;@" 這樣的參數來指示 text 函數進行計算。

我們要用 D2 這一格的資料來計算當天是星期幾,完整的指令是 text(D2, "[$-404]aaaa;@") ,你可以想像,我在說明檔案中查到這個參數時,一整個覺得:『What?』的感覺! XDDD

利用 text 函數計算特定日期是星期幾
圖、利用 text 函數計算特定日期是星期幾

參數的這一關過了,後面就很順暢了,與 Google Drive 的做法沒什麼不同。一樣用 Text(D2, "mm/dd") 就能祇顯示月日資料,再用 & 將資料串接起來。

Excel 與 Google Drive 祇有日期轉星期的參數格式不同
圖、Excel 與 Google Drive 祇有日期轉星期的參數格式不同

那麼要用『週一』取代『星期一』這樣的格式呢?在 Google Drive 是用 "ddd" 取代 "dddd",在 Excel 我們用 text(D2, "[$-404]aaa;@") 三個 a 取代四個 a 的 "[$-404]aaaa;@" 一樣也可以得到『週一』這樣簡短格式的星期表示。

Excel 用三個 a 對應 Google Drive 的三個 d
圖、Excel 用三個 a 對應 Google Drive 的三個 d

最後,同樣使用 right 函數取得最右方的字元,就可以用 2/11(一) 這樣的方式來呈現資料了。

用 right 函數取得最右邊的字元
圖、用 right 函數取得最右邊的字元

所以 Excel 與 Google Drive 其實祇有在一些細節上不一樣而已,所以也不用學太多新的東西就可以在那邊自由移動了。祇不過那個 "[$-404]aaaa;@" 實在太奇怪,所以我要寫下來才不會日後又不知道該怎麼處理。希望我的個人記錄也有幫到你。

做個小小的測試

前面有提到,在 Google Drive 中 "dd" 是要呈現『日期』的資料,所以在 Google Drive 中一定要用 right 去抓最右邊的字元,才能用 2/11(一) 這樣的格式呈現日期與星期。可是 Excel 是用 "[$-404]aaaa;@" 做參數,那麼我可以用兩個 a 的 "[$-404]aa;@" 做出 2/11(一) 這樣的格式嗎?

留給你試試看嘍! ;)

沒有留言:

張貼留言