Excel如何計算應收帳款逾期天數(月份)

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

一、既然涉及到日期天數,首先瞭解Excel裡有哪些相關函數。在上方功能區裡,選取「公式」頁籤,拉出「日期及時間」清單,這些,就是Excel相關函數了。

選取「公式」頁籤,拉出「日期及時間」清單

二、我自己看了看,發現最有用的函數就一個:「=TODAY()」,點出「函數說明(H)」,Excel官方說明為:「傳回目前日期序列值。此序列值是 Microsoft Excel 用以從事日期及時間計算的代碼。如果儲存格格式在輸入函數之前是 [通用],則結果的格式會是日期格式。」簡單講,這個函數會傳回今天的日期。由於應收帳款逾期都是以現在來計算,因此能抓出今天的函數,特別重要。

=TODAY()

三、如圖所示,應收帳款收款日明細表,有了收款日,有了今天「=TODAY()」,相減「=$E$1-D2」,便得到了逾期天數。

有了今天「=TODAY()」,相減「=$E$1-D2」

四、今天減掉應收款日,正的表示已經逾期,負的表示尚未逾期,但其實並不需要負數,尚未逾期顯示零即可,並且將TODAY這個函數直接帶入公式,不再另外設置儲存格,綜合起來,可以依照思惟邏輯寫:「=IF((TODAY()-D2)<0,0,(TODAY()-D2))」,也可以技巧一點:「=MAX((TODAY()-D2),0)」。

=IF((TODAY()-D2)<0,0,(TODAY()-D2))

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

=ROUNDDOWN(E2/30,0)

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

=MAX((DATE(2016,3,31)-D2),0)

關於應收帳款,除了計算出逾期天數和月份之外,就管理報表而言,有時候並不需要數字,只要把異常帳款標示出來即可,這個用格式化條件可以做到,另外專篇文章作說明。