Excel中日期時(shí)間函數(shù)使用辨析

摘要：日期時(shí)間函數(shù)在Excel中廣泛使用，在使用過(guò)程中，對(duì)應(yīng)于一些函數(shù)的基本知識(shí)要充分了解，函數(shù)的參數(shù)，取值要注意辨析，才能保證結(jié)果正確。

關(guān)鍵詞：Excel;函數(shù);辨析

中圖分類號(hào)：TP3 文獻(xiàn)標(biāo)識(shí)碼：A

文章編號(hào)：1009-3044（2020）27-0196-02

開放科學(xué)（資源服務(wù)）標(biāo)識(shí)碼（OSID）：

1 Excel的日期、時(shí)間函數(shù)的基本知識(shí)

1.1 Excel日期系統(tǒng)

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

1.2日期、時(shí)間與序列號(hào)

一般來(lái)說(shuō)，Excel中有3種數(shù)據(jù)類型，分別為文本、數(shù)字與日期和時(shí)間類型。實(shí)際上，日期和時(shí)間是特殊的數(shù)字。從直觀上，我們可以看到在默認(rèn)狀態(tài)下，數(shù)字與日期和時(shí)間自動(dòng)右對(duì)齊，而文本自動(dòng)左對(duì)齊;從本質(zhì)上看，日期和時(shí)間是與數(shù)字有著一一對(duì)應(yīng)關(guān)系的，即系統(tǒng)的起始日期1900年1月1日對(duì)應(yīng)于數(shù)字“1”，從這個(gè)日期開始，以“天”為單位，24小時(shí)為1天，因而得到如下的對(duì)應(yīng)關(guān)系：1天=“1”整天;1小時(shí)= 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精確到的最小單位就是毫秒。如果把實(shí)際中不存在的1900年1月0日，記為坐標(biāo)原點(diǎn)，1900年1月1日的序列號(hào)即為1，因而，如圖1所示，我們也可以看到2020年4月8日對(duì)應(yīng)的就是43929。

1.3常用日期和時(shí)間函數(shù)

（1）NOWO返回當(dāng)前Windows系統(tǒng)設(shè)定的日期和時(shí)間，顯示方式為正常的日期格式，如當(dāng)前日期為4月18日，當(dāng)使用NOW（）函數(shù)后，則顯示為當(dāng)前日期和當(dāng)前時(shí)間，并且也不會(huì)實(shí)時(shí)更新，除非重新計(jì)算工作表。如果要顯示當(dāng)前日期對(duì)應(yīng)的序列號(hào)，則需將單元格格式設(shè)置為“常規(guī)”，或使用“=text （NOWO，”0”）”，則顯示函數(shù)在Excel中的常規(guī)格式，為整型數(shù)。

（2）TODAYO返回當(dāng)前Windows系統(tǒng)設(shè)定的日期，顯示方式為正常的日期格式，并且也不會(huì)實(shí)時(shí)更新，除非重新計(jì)算工作表。如果要顯示當(dāng)前日期對(duì)應(yīng)的序列號(hào)，則需將單元格格式設(shè)置為“常規(guī)”，或使用“=text（TODAYO，”0”）”。因?yàn)門ODAYO函數(shù)自動(dòng)獲取系統(tǒng)時(shí)間，因而在某些場(chǎng)合可以參與倒計(jì)時(shí)公式=截止日期-TODAYO。

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

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

（5）Year（提取年份的日期），返回值在1900-9999之間。注意，待提取年份的日期為標(biāo)準(zhǔn)格式日期，或通過(guò)其他日期時(shí)間函數(shù)計(jì)算得到的日期值。Month（提取月份的日期），返回值為1-12。Day（提取天數(shù)的日期），返回值為1-31。

2 Excel日期函數(shù)應(yīng)用中的問(wèn)題

由于在日常計(jì)算中，涉及小數(shù)位數(shù)取舍問(wèn)題，因而對(duì)于日期及時(shí)間計(jì)算很容易出錯(cuò)，需多加注意。

2.1 使用常規(guī)方式解決周年、周歲問(wèn)題

例如：某年NCRE考題中，就曾出現(xiàn)“年齡需要按周歲計(jì)算，滿1年才計(jì)1歲”類似題目，就需要注意公式使用情況，如果僅使用公式=（今天日期一出生日期）/365，就可能出錯(cuò)，因?yàn)槊恳荒瓴灰欢ǘ际?65天，即使使用了INT（）函數(shù)，F(xiàn)2=INT《TO-DAYO-[@出生日期]）/365），也是一個(gè)大致的數(shù)值，一般情況下比較接近。

2.2 使用隱含函數(shù)，精確計(jì)算工齡、周歲問(wèn)題

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

2.3 停車場(chǎng)收費(fèi)問(wèn)題

某停車場(chǎng)根據(jù)記錄數(shù)據(jù)，利用出場(chǎng)日期、時(shí)間與進(jìn)場(chǎng)日期、時(shí)間的關(guān)系，計(jì)算“停放時(shí)間”列，單元格格式為時(shí)間類型的“XX時(shí)XX分”，而后再計(jì)算費(fèi)用更改前后效益差計(jì)算費(fèi)用更改前后的標(biāo)準(zhǔn)如下：固定的是超過(guò)15分鐘的按每15分鐘計(jì)費(fèi)，其中小型車1.5元，中型車2.0元，大型車2.5元;更改的是以前“不足15分鐘按15分鐘收費(fèi)”，現(xiàn)在是“不足15分鐘的部分不收費(fèi)”標(biāo)準(zhǔn)。

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

在計(jì)算現(xiàn)行收費(fèi)標(biāo)準(zhǔn)中“不足15分鐘按15分鐘收費(fèi)”，直接使用J2值，轉(zhuǎn)化為分鐘數(shù)，然后再除15，利用ROUNDUP函數(shù)向上舍人，K2=ROUNDUP（J2*24*60/15，0）*E2得到結(jié)果。

同理在擬實(shí)施標(biāo)準(zhǔn)中“不足15分鐘不收費(fèi)”，直接使用J2值，轉(zhuǎn)化為分鐘數(shù)，然后再除15，利用ROUNDDOWN函數(shù)向下舍人，得到結(jié)果。

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

但繼續(xù)瀏覽到下面發(fā)現(xiàn)問(wèn)題了在第544和545行，利用該公式計(jì)算得到的結(jié)果：

而依據(jù)原來(lái)的公式K2=ROUNDUP（J2*24*60/15，O）*E2，得到結(jié)果如下：

問(wèn)題出現(xiàn)的原因在哪里？原來(lái)我們計(jì)算停車時(shí)間雖然按照時(shí)間格式得到是**時(shí)**分，但還是存在**秒的，如果單單應(yīng)用HOURO和MINUTEO函數(shù)提取了小時(shí)和分鐘數(shù)，相當(dāng)于舍棄了“秒”值，因而會(huì)收費(fèi)減少。

日期、時(shí)間函數(shù)在Excel函數(shù)中應(yīng)用非常靈活，但由于在Excel中應(yīng)用非常廣泛，因而對(duì)于日期、時(shí)間函數(shù)的使用必須注意辨析，防止出現(xiàn)各式錯(cuò)誤答案。

參考文獻(xiàn)：

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

[2]教育部考試中心.全國(guó)計(jì)算機(jī)等級(jí)考試二級(jí)教程MS Office高級(jí)應(yīng)用[M].北京：高等教育出版社，2018.

【通聯(lián)編輯：代影】

作者簡(jiǎn)介：胡杰華（1972-），女，安徽績(jī)溪人，副教授，碩士，主要研究方向?yàn)橛?jì)算機(jī)技術(shù)與保密安全。