Excel逾期天數計算:Today日期相減與月結天數帳齡

Excel管理報表經常要和日期打交道,本文以應收帳款為例,介紹TODAY和DATE兩個最常用到的日期函數,再搭配IF、MAX、ROUNDDOWN等函數組合,輕鬆算出逾期天數或月份。

會計人員除了結帳和切傳票,經常要以各科目為出發點,追踪管理異常項目。其中屬於應收帳款部份,最重要莫過於逾期帳款。如今ERP這麼普遍,通常建制完整的系統,都可以跑出應收帳款帳齡表或逾期表。然而,系統報表雖然方便,很多情況還是需要自己整理、或者是想檢查系統報表是否無誤,凡此種種,都必須善用Excel功能,在此分紹:

一、Excel日期計算

既然涉及到日期天數,首先瞭解Excel裡有哪些相關函數。在上方功能區裡,選取「公式」頁籤,拉出「日期及時間」清單,這些就是Excel跟日期相關函數,以這篇文章範例而言,最有用函數是「TODAY」。

Excel逾期天數計算:Today日期相減與月結天數帳齡 29

二、Excel Today函數

「按F1取得更多說明」,Excel官方對於TODAY函數的說明為:「傳回目前日期序列值。此序列值是 Microsoft Excel 用以從事日期及時間計算的代碼。如果儲存格格式在輸入函數之前是 [通用],則結果的格式會是日期格式。」簡言之,此函數將傳回今天的日期,由於應收帳款逾期多以現在為基礎計算,因此能抓出當天日期的函數,特別有用。

Excel逾期天數計算:Today日期相減與月結天數帳齡 31

三、Today當天日期

在應收帳款明細表輸入公式:「=TODAY()」,計算結果是返回當天日期。

Excel逾期天數計算:Today日期相減與月結天數帳齡 33

四、Excel日期相減today

有收款日,有當天日期今天,相減(「=$E$1-D2」),便得到逾期天數。

Excel逾期天數計算:Today日期相減與月結天數帳齡 35

五、逾期天數計算

今天減掉應收款日,正的表示已經逾期,負的表示尚未逾期,但其實尚未逾期顯示零即可,並不需要負數,而且希望一併將TODAY這個函數寫入公式,不再另外設置儲存格,綜合起來,輸入公式:「=IF((TODAY()-D2)<0,0,(TODAY()-D2))」,也可以引進「MAX」函數:「=MAX((TODAY()-D2),0)」。

Excel逾期天數計算:Today日期相減與月結天數帳齡 37

六、月結天數計算

逾期天數是比較瑣碎,很多情況只需要逾期月份即可,看起來簡單明瞭。輸入公式:「=ROUNDDOWN(E2/30,0)」,意思是把天數除以30,無條件捨去法取到整數。除了「ROUNDDOWN」,還有「ROUNDUP」無條件進位法取位,「ROUND」是四捨五入法取位,可以視需要情況使用。

Excel逾期天數計算:Today日期相減與月結天數帳齡 39

七、帳齡天數計算

「TODAY」這個函數抓的是當天日期,這是優點、同時也是缺點。因為日期是一直在變動,幾天後再打開檔案,會發現逾期天數變了。準備月末或季度資料、或者是會計師查帳時,想要將基準日固定在某個日期(通常是期末),有兩個方法:其一是設置基準日期的儲存格:「=DATE(2016,3,31)」,其二是直接將基準日期寫入公式:「=MAX((DATE(2016,3,31)-D2),0)」。

Excel逾期天數計算:Today日期相減與月結天數帳齡 41

Excel日期相減計算

這篇文章範例為應收帳款,在會計人的管理報表中,只要是渉及到日期的,都會有計算天數的情形,都可以套用這篇文章所介紹的公式,例如應付帳款延遲付款天數、存貨周轉天數、銀行借款利息天數等。