Excel中日期時間函數使用辨析

摘要：日期時間函數在Excel中廣泛使用，在使用過程中，對應于一些函數的基本知識要充分了解，函數的參數，取值要注意辨析，才能保證結果正確。

關鍵詞：Excel;函數;辨析

中圖分類號：TP3 文獻標識碼：A

文章編號：1009-3044（2020）27-0196-02

開放科學（資源服務）標識碼（OSID）：

1 Excel的日期、時間函數的基本知識

1.1 Excel日期系統

Excel提供了兩種日期系統，一種是1900日期系統，系統的起始日期是1900年1月1日，應用在Windows操作系統中;另一種是1904日期系統，起始日期為1904年1月1日，使用在Ma-cintosh中，本文主要以1900日期系統為例。

1.2日期、時間與序列號

一般來說，Excel中有3種數據類型，分別為文本、數字與日期和時間類型。實際上，日期和時間是特殊的數字。從直觀上，我們可以看到在默認狀態下，數字與日期和時間自動右對齊，而文本自動左對齊;從本質上看，日期和時間是與數字有著一一對應關系的，即系統的起始日期1900年1月1日對應于數字“1”，從這個日期開始，以“天”為單位，24小時為1天，因而得到如下的對應關系：1天=“1”整天;1小時= 1/24=0.0416667;1分鐘=1/24*60 - 1/1440=0.000694;1秒=1/24*60*60=1/86400

0.00001157;1毫秒=1/24*60*60*1000=1/86400000，Excel精確到的最小單位就是毫秒。如果把實際中不存在的1900年1月0日，記為坐標原點，1900年1月1日的序列號即為1，因而，如圖1所示，我們也可以看到2020年4月8日對應的就是43929。

1.3常用日期和時間函數

（1）NOWO返回當前Windows系統設定的日期和時間，顯示方式為正常的日期格式，如當前日期為4月18日，當使用NOW（）函數后，則顯示為當前日期和當前時間，并且也不會實時更新，除非重新計算工作表。如果要顯示當前日期對應的序列號，則需將單元格格式設置為“常規”，或使用“=text （NOWO，”0”）”，則顯示函數在Excel中的常規格式，為整型數。

（2）TODAYO返回當前Windows系統設定的日期，顯示方式為正常的日期格式，并且也不會實時更新，除非重新計算工作表。如果要顯示當前日期對應的序列號，則需將單元格格式設置為“常規”，或使用“=text（TODAYO，”0”）”。因為TODAYO函數自動獲取系統時間，因而在某些場合可以參與倒計時公式=截止日期-TODAYO。

（3） DATE（year，month，day）返回指定日期的序列號。在DATE函數中所有參數必須為數值型數據，year值為1900-9999之間，month正常值為1-12，day正常值為1-31，但date有自動更正功能，當月>12或日>31時，自動更正為下一年或下一月;同理當月<1或日<1時，自動更正為上一年或上一月。如圖2所示。

（4）Time（hour，minute，second）返回指定時間的序列號，返回值為小數，值在0-0.99999999之間表示從0：00：00到23：59：59。Hour（提取小時數的時間），返回值為0-23。Minute（提取分鐘數的時間），返回值為0-59。Second（提取秒數的時間），返回值為0-59。

（5）Year（提取年份的日期），返回值在1900-9999之間。注意，待提取年份的日期為標準格式日期，或通過其他日期時間函數計算得到的日期值。Month（提取月份的日期），返回值為1-12。Day（提取天數的日期），返回值為1-31。

2 Excel日期函數應用中的問題

由于在日常計算中，涉及小數位數取舍問題，因而對于日期及時間計算很容易出錯，需多加注意。

2.1 使用常規方式解決周年、周歲問題

例如：某年NCRE考題中，就曾出現“年齡需要按周歲計算，滿1年才計1歲”類似題目，就需要注意公式使用情況，如果僅使用公式=（今天日期一出生日期）/365，就可能出錯，因為每一年不一定都是365天，即使使用了INT（）函數，F2=INT《TO-DAYO-[@出生日期]）/365），也是一個大致的數值，一般情況下比較接近。

2.2 使用隱含函數，精確計算工齡、周歲問題

DATEDIF（起始日期，截止日期，間隔類型）函數是隱含的函數，不僅沒有函數幫助，而且也不出現在公式記憶式錄入列表中，DATEDIF中間隔類型比較常用的有：“y”代表年差，“m”代表月差，“d”代表日差;還有3中不常用的間隔類型，忽略年月日中的一部分計算日期之差，分別為“md”“ym”“yd”。在計算年齡，尤其是周歲問題，這種要求精確數據時，使用DATEDIF就非常方便了。在計算單元格中直接輸入公式= DATEDIF（[@出生日期，TODAYO，”y”）就可以啦。

2.3 停車場收費問題

某停車場根據記錄數據，利用出場日期、時間與進場日期、時間的關系，計算“停放時間”列，單元格格式為時間類型的“XX時XX分”，而后再計算費用更改前后效益差計算費用更改前后的標準如下：固定的是超過15分鐘的按每15分鐘計費，其中小型車1.5元，中型車2.0元，大型車2.5元;更改的是以前“不足15分鐘按15分鐘收費”，現在是“不足15分鐘的部分不收費”標準。

題目給出之后，考慮的思路就是日期和時間都是數值型數據可以直接參與運算，給出公式：J2=H2-F2+12-G2，這樣就得到停放時間。

在計算現行收費標準中“不足15分鐘按15分鐘收費”，直接使用J2值，轉化為分鐘數，然后再除15，利用ROUNDUP函數向上舍人，K2=ROUNDUP（J2*24*60/15，0）*E2得到結果。

同理在擬實施標準中“不足15分鐘不收費”，直接使用J2值，轉化為分鐘數，然后再除15，利用ROUNDDOWN函數向下舍人，得到結果。

如果，采用另一種計算方法如下：使用公式K2=ROUNDUP《HOUR（J2）*60+MINUTE（J2）/15，0）*E2，看似結果與上面計算一樣

但繼續瀏覽到下面發現問題了在第544和545行，利用該公式計算得到的結果：

而依據原來的公式K2=ROUNDUP（J2*24*60/15，O）*E2，得到結果如下：

問題出現的原因在哪里？原來我們計算停車時間雖然按照時間格式得到是**時**分，但還是存在**秒的，如果單單應用HOURO和MINUTEO函數提取了小時和分鐘數，相當于舍棄了“秒”值，因而會收費減少。

日期、時間函數在Excel函數中應用非常靈活，但由于在Excel中應用非常廣泛，因而對于日期、時間函數的使用必須注意辨析，防止出現各式錯誤答案。

參考文獻：

[1]宋翔.Excel公式與函數大辭典[Ml.北京：人民郵電出版社，2010.

[2]教育部考試中心.全國計算機等級考試二級教程MS Office高級應用[M].北京：高等教育出版社，2018.

【通聯編輯：代影】

作者簡介：胡杰華（1972-），女，安徽績溪人，副教授，碩士，主要研究方向為計算機技術與保密安全。