How to flag late payments in an Excel Spreadsheet

Cash-flow 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!

Just like the Status column in the screenshot below.
Screenshot of Excel Spreadsheet with column showing late invoice status
Our complementary spreadsheet shows you how to do this, along with some other nice touches:

  1. Total of all unpaid invoices.
  2. Total of unpaid invoices which are late.
  3. Formulas to automatically calculate due date from Invoice Date and Terms (Days).
  4. Conditional Formatting to make the Late ones stand out.

You can download it here –


Want to add a Status column to your own 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”,””)

Status column in an Excel Spreadsheet showing late invoice payment

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.

 

 


 

Want some more tips and examples of how to use Excel to make your work easier?

Subscribe to our Newsletter for examples of Excel Spreadsheets, Business Admin Tips and Databases.