A paid and unpaid formula in Excel shows you late invoice payments and will help you with the cash-flow which is crucial to all businesses. If you have an Excel spreadsheet of your invoices, wouldn’t it be great to have a column which automatically showed you which ones were late!
The Status column in the screenshot below is a ‘paid and unpaid formula‘ showing which invoices are paid, which are outstanding and which are late!
Our complementary spreadsheet shows you how to do this, along with some other nice touches:
- Total of all unpaid invoices.
- Total of late invoices.
- Calculate invoice due dates.
- Highlight late invoice payments in red.
You can download it here –
Want to add a Paid and Unpaid Formula for Late Invoice Payments to your own Excel spreadsheet?
In your new Status column, enter a formula using the IF operator and TODAY() function. The IF operator looks at a cell value condition and then lets us perform a different action depending on whether the condition is true or false. Here we want to know if the Invoice Due Date is before today’s date. If that is true, then show the word ‘Late’ and if not, show the word ‘Outstanding’. We can compare the Invoice Due Date with today’s date using the TODAY() function. TODAY() automatically updates each day, giving today’s date!
If ‘Status’ is in cell D1 and ‘Invoice Due Date’ is in cell C1 then in cell D2 enter:
=IF(C2<TODAY(),”Late”,””)
In English this reads as: ‘If the date in cell C2 is before today, then the invoice is late, otherwise, leave the cell blank’.
If you want to recreate the Status column and Totals from the screenshot above, please feel free to download the complementary spreadsheet.
Sign up for Excel Help, Data Tips and Business Admin Advice
Custom Software Development
Save time and money, while boosting productivity
Upgrade From Spreadsheets
Reduce errors and improve sharing of information.
Add a Comment