每月一課：如何在Excel中計算日期和天數(shù)

在Excel中可以對日期進(jìn)行基本的加減運算，也有專門的日期函數(shù)用于進(jìn)行更加復(fù)雜的運算，但必須將含有公式的單元格設(shè)置為日期格式，才能正確顯示計算結(jié)果，具體如何操作呢？

月數(shù)計算

Excel中經(jīng)常會需要在某個日期上加上指定月數(shù)，如做預(yù)算、預(yù)測或計算租金時。下面將介紹兩個可實現(xiàn)這一功能的函數(shù)。

1、月初：

專家建議：進(jìn)行月份計算時，尤其是在報告、預(yù)算或預(yù)測中使用月初的日期，這樣計算更不容易出錯。

如需在某個日期上加上指定月數(shù)，至簡單的方法是使用EDATE函數(shù)。

在圖1中，C列使用EDATE函數(shù)，返回A列日期加上B列月數(shù)后的日期。D列顯示C列的公式。EDATE函數(shù)有兩個參數(shù)，第一個是指定的起始日期，第二個是要在起始日期上加上的月數(shù)。如果第二個參數(shù)為小數(shù)，則忽略小數(shù)部分，截取整數(shù)，且不會四舍五入；如果為負(fù)數(shù)，則會從起始日期減去相應(yīng)月數(shù)。

對于28日之前的日期，非常適合使用EDATE函數(shù)進(jìn)行計算。

2、月末：

當(dāng)起始日期為月末或接近月末的日期時，可能會出現(xiàn)問題。如圖1第4行所示，當(dāng)起始日期為月末時，加上指定月數(shù)后的日期并非是該月月末。

另一方面，圖1第7行卻得到正確結(jié)果，因為至終結(jié)果的月末日期（28）小于起始的月末日期（31）。當(dāng)起始的月末日期小于至終結(jié)果的月末日期時，就會出現(xiàn)問題，如第4行所示。

如果需要返回至終結(jié)果的月末日期，則可使用EOMONTH函數(shù)。如圖1中的E列所示，公式顯示在F列中。

EOMONTH函數(shù)的兩個參數(shù)與EDATE相同，但它始終返回至終結(jié)果的月末日期。

Days Caculation

天數(shù)計算：

1、自然日：

圖2中的公式在計算時將起始日期排除在外，即從起始日期后增加相應(yīng)的天數(shù)。

如果您想要包含起始日期，即起始日期當(dāng)天也納入計算，只需在上述基礎(chǔ)上減去1（=B2+A2-1）。例如，如果完成一項任務(wù)需要兩天時間，起始日期為2022年1月1日，則應(yīng)于2022年1月2日之前完成，而非2022年1月3日。

2、工作日：

上例中沒有考慮是否為工作日。2022年1月1日為周六，次日為周日，1月3日又是節(jié)假日。如果需要從2022年1月1日開始向后加上2個工作日，則應(yīng)于1月5日（周三）前完成任務(wù)。

Excel中有可以處理此類計算的函數(shù)。WORKDAY和WORKDAY.INTL函數(shù)可返回某個日期加上指定工作日后的至終日期。本文的配套視頻將詳細(xì)介紹WORKDAY.INTL函數(shù)（您可點擊“閱讀原文”查看）。

WORKDAY函數(shù)默認(rèn)周六和周日為周末。圖3中的表格顯示了WORKDAY函數(shù)的計算結(jié)果。

WORKDAY函數(shù)有三個參數(shù)，第一個是指定的起始日期，第二個是起始日期之前或之后的工作日天數(shù)，截尾取整。第三個參數(shù)為可選項，表示一組視為節(jié)假日的日期，需要從工作日歷中排除。

C列中的結(jié)果排除了周末，但只計算平日而未考慮節(jié)假日。E列中的結(jié)果將H2:H3區(qū)域中列出的兩個當(dāng)月節(jié)假日排除在外。在計算工作日時，一般會將期間的所有節(jié)假日排除在外。

WORKDAY函數(shù)在計算時將起始日期排除在外，即便起始日期是工作日也不計入。

3、兩個日期之間的工作日天數(shù)

如果想要計算指定起始日期與結(jié)束日期之間的工作日天數(shù)，可使用NETWORKDAYS和NETWORKDAYS. INTL函數(shù)，如圖4所示。

NETWORKDAYS函數(shù)的第一個參數(shù)是指定的起始日期，第二個參數(shù)是結(jié)束日期，計算時會將起始日期排除在外。如果想要包含起始日期，可在NETWORDAYS的計算結(jié)果上加1。

圖4中C列的計算結(jié)果包含節(jié)假日，E列的計算結(jié)果將節(jié)假日排除在外。

4、某個月的倒計時一個工作日

如果想要獲取某個月的倒計時一個工作日，可以結(jié)合使用兩個函數(shù)。圖5顯示了這兩個公式及其結(jié)果。

通過將EOMONTH函數(shù)的第二個參數(shù)設(shè)為零，可以獲取A列日期所在日歷月的月末日期。

在該日期的基礎(chǔ)上加1，可得到下個月第一天的日期。

然后將這個日期作為WORKDAY函數(shù)的起始日期，在此基礎(chǔ)上減去1，即可得到上個月的倒計時一個工作日。

澳洲注冊會計師資質(zhì)受到全球雇主的廣泛認(rèn)可，在亞洲市場亦有超過60年的歷史沉淀。公會在香港、新加坡、馬來西亞等主要亞洲城市、大洋洲和歐洲均設(shè)有分會，支持會員的國際化事業(yè)發(fā)展，點擊獲取更多澳洲CPA資料>>

更多推薦：