【Excel Date】一般日期函數：DATE() 基本應用

  • Post author:
Home » 莫非工具 » Excel » 【Excel Date】一般日期函數：DATE() 基本應用

工作中常常會使用 Excel 去記錄與時間相關的資料。而 Excel 中提供了相當多與日期、時間相關的函數可以使用，本篇文章要介紹與日期最直接相關的基本應用：DATE、YEAR、MONTH、DAY、WEEKDAY、TODAY、DATEVALUE。

延伸閱讀：【Excel Date】第一天、最後一天：簡單的方法取得當年、月、週的起始、結束日
文章總覽
  1. 認識 Excel 日期格式
  2. 日期系統
  3. DATE（日期）
  4. YEAR（日期➝年份）
  5. MONTH（日期➝月份）
  6. DAY（日期➝日）
  7. WEEKDAY（日期➝星期）
  8. TODAY（今日日期）（current day）
  9. DATEVALUE（日期➝序號值）

認識 Excel 日期格式

如果直接在儲存格(Cell)中，輸入類似日期的值，Excel 會將該其轉成日期格式：

Excel 會將該儲存格的值自動變成當年度對應日期「2023/1/2」，同時還會自作主張將儲存格格式更改，這點其實困擾我很久。

儲存格格式

最簡單的方法就是去我們主動更改儲存格格式，改成想要呈現的格式：

  • 選取儲存格 ＞ 滑鼠右鍵 ＞ 儲存格格式(F)
  • 選取儲存格 ＞ 常用 ＞ 儲存格 ＞ 格式 ＞ 儲存格格式(E)

民國年

如果不想顯示西元年，想要轉換成民國年， Excel 也有提供對應的格式：

儲存格格式 ＞ 日期 ＞ 行事曆類型(A) ＞ 中華民國曆

當然除了這種直接輸入日期的方式，還有其他可以呈現日期的函數，就是本篇的重點：DATE。

自定義日期格式（Custom Date format）

當然也可以使用自定義日期格式，選擇我們想要的日期格式

當然也可以自行去設定想要呈現的時間格式

延伸閱讀：【Excel Date】自訂日期格式（Custom Date format）

日期系統

不知道大家有沒有發現，在 Excel 中，儲存格(Cell)儲存時間的方式，是以序號來表示。

若將日期的儲存格格式換為通用格式，該日期會變成一組數字。

以 2023/10/2 為例，會得到數字為「45201」，那這代表甚麼意思？

這代表從 1899 年 12 月 31 日到 2023 年 10 月 2 日，共經過了 45201 天

1900 日期系統（The 1900 Date System）（預設）

根據預設，Microsoft Excel for Windows 會使用 1900 日期系統。 

在該系統中，Excel 支援的第一天為 1900 年 1 月 1 日，當你輸入日期值時， Excel 會將其以序號儲存，以日期顯示。

1900 年 1 月 1 日，為序號 1，每過一天，序號加 1 。

1904 日期系統（The 1904 Date System）

同理可知 ，該系統支援的第一天為 1904 年 1 月 1 日，為序號 1，每過一天，序號加 1 。

因此以同一天來說，兩個系統的值會相差 1462（四年，包含閏年）

為什麼 Excel 要有兩個時間系統？在 1900 系統中，是允許「1900/2/29」這天，但是依照閏年的規範， 1900 年並非閏年，應為平年。為了防止相關的問題，才有了 1904 日期系統。

延伸閱讀：Excel 不正確地假設 1900 年是閏年

DATE（日期）

=DATE(a, b, c)

a：年（year）

  • 1900~9999：1900~9999 年
  • 0~1899：1900 + (0~1899) 年

b：月（month）

  • 1~12：1~12 月
  • > 12：依序遞加（13:隔年 1 月）
  • 0：去年 12 月
  • < 0：依序遞減（-1:去年 11 月）

c：日（day）

  • 1~31：1~31 日（注意大小月，以10月為例）
  • > 31：依序遞加（32:隔月 1 日）
  • 0：前月最後一天
  • < 0：依序遞減（-1:前月倒數第二天）
=DATE(a,b,c)
=DATE(2023,10,2)

一般情況使用 DATE ，是非常容易懂的。假設要顯示「2023/10/2」，三個參數依序放入想要呈現的年、月、日即可：「2023」、「10」、「2」。

第一個參數 a（年）

  • a 介於 1900~9999，會直接將該值作為年份：「=DATE(2023,10,2)」➝「2023/10/2」
  • 如果介於 0~1899 之間，則 Excel 會將該值加上 1900 來計算年分：
    「=DATE(157,10,2)」➝「2057/10/2」
  • 小於 0 或者 大於 9999，會回傳錯誤值：「#NUM!」

月和日的規則很相似。正常範圍之外，可以想成平移的概念：

第二個參數 b（月）

  • b 介於 1~12，會直接將該值作為月份：「=DATE(2023,10,2)」➝「2023/10/2」
  • b 等於 0，Excel 會將其視為前一年的 12 月(12-0)：「=DATE(2023,0,2)」➝「2022/12/2」
  • b 小於 0，若為 -3，為去年 12 月減去 3 月(12-3)：「=DATE(2023,-3,2)」➝「2022/9/2」
  • b 大於 12，若為 14，為隔年 (14-12) 月：「=DATE(2023,14,2)」➝「2024/2/2」

第三個參數 c（日）注意該月總日數，下方範例以 10 月共有 31 天為例

  • c 介於 1~31 ，會直接將該值作為月份：「=DATE(2023,10,2)」➝「2023/10/2」
  • c 等於 0，Excel 會將其視為前一月的最後一天：「=DATE(2023,10,0)」➝「2023/9/30」
  • b 小於 0，若為 -3，為前月最後一天減去 3 天(31-3)：「=DATE(2023,10,-3)」➝「2023/9/27」
  • b 大於 31，若為 33，為隔月 (33-31) 日：「=DATE(2023,10,33)」➝「2023/11/2」

YEAR（日期➝年份）

=YEAR(a)

a：日期

回傳：年份

YEAR 這個函數會直接回傳該日期的西元年。

但是 Excel 並沒有提供民國年的函數，如果要回傳民國年可以直接將此值減去 1911 即可。

MONTH（日期➝月份）

=MONTH(a)

a：日期

回傳：月份

DAY（日期➝日）

=DAY(a)

a：日期

回傳：幾號

WEEKDAY（日期➝星期）

=WEEKDAY(a,[b])

a：日期
b：規則（預設值為：1）

回傳：星期幾（預設週日為起始：1）

2023 年 10 月 2 日為星期一，那為什麼 WEEKDAY() 的結果是 2 而不是 1 呢？
在預設（沒有第二參數）的情況下，根據 Excel 的規則，會把週日訂為一週的起始日，因此週一的值就會是 2 。依此類推週二就會是 3 、週六是 7。

這裡 WEEKDAY 提供了第二個參數來決定回傳的起始值依據，如果不使用第二參數，則會依照預設值，也就是，星期日為「1」

這裡我把不同參數對應的回傳值放上來

TODAY（今日日期）（current day）

=TODAY()

回傳：當天日期(current day)

TODAY 會直接回傳當天的日期，不包含時間，如果要包含時間可以使用 NOW()

但這兩個函數的值都會隨時間變化，使用上要多加注意

DATEVALUE（日期➝序號值）

=DATEVALUE(a)

a：文字格式的日期

回傳：該日日期系統之值（預設為1900日期系統）

DATEVALUE 可以將日期轉換成「1900日期系統」所對應之值：以 1900/1/1 當作 1，每過一天加 1

這裡唯一要注意的就是，函數內的參數要使用文字格式，不能直接引用放有日期的儲存格，因為該儲存格已經被 Excel 轉換成 1900日期系統，實際地值並非日期。

=DATEVALUE("2023/10/2")

這裡我會將時間轉成文字「“2023/10/2”」最簡單的方法就是掛上雙引號。

或者如上方的圖片紅色箭頭，在字串前加上一個單引號「‘2023/10/2」，這樣也可以輕鬆地將單引號後面的內容認定為文字格式，就可以直接被 DATEVALUE 引用。

更多 Excel 文章請至 文章導覽 尋找，也可利用上方「搜尋(Search...)」功能
有任何莫非工具的問題，歡迎至「表單」提問

Excel 是不是很容易
和墨菲一起用 Excel 把工作變簡單

This Post Has 5 Comments

歡迎留言，與我們分享你的看法！