Excel can be a great tool to calculate invoice payment information for Accounts Payable registers. If you are a business that pays a lot of vendors, you can keep track of all your payments and invoices and determine which ones are overdue. Excel is a great tool to do this.
Let’s say we have this :
We have a few invoices along with Invoice date, their terms and when the invoice is due.
We want to calculate how many days these invoices are outstanding and tell if they are overdue.
To figure out Days Outstanding, We need to take today’s date and subtract out the due date. However we want to make this interactive. To do that we will use the function TODAY(). This will give you today’s date. So we can put:
This will give you days outstanding.
To figure out if the invoice is overdue, we can use an IF statement. We just want to say if the Days outstanding is greater than 0, then we would say it is overdue. Everything else would mean it is not overdue. So we can use the formula:
This would tell us that it is overdue when the days are over 0. Once we copy it down, we would get the following:
The second invoice isnt due until June 24th and since today is June 8th, this isn’t overdue. However the first one was.
***If you would like more information about Excel functions, check out “Learn Functions Fast” by Chandoo at bit.ly/LearnFunctionsFast