Cashflow is crucial to all businesses. It is important to keep on top of who owes you what and when it is due. Excel can quickly identify those customers who have not paid on time. You can follow the example below using your own data.
Although, you may find it easier to purchase our completed Flagging Late Payments Spreadsheet Example and start entering your data straight away.
Purchase the Flagging Late Payments Spreadsheet Example
Click the button below to purchase the Flagging Late Payments Spreadsheet Example
After completing your purchase on PayPal you receive the download link via email.
Using Excel’s IF function allows us to quickly identify whether a row of data matches our criteria, or not, in this case; Has the bill been paid? If you have a list of sales orders with different payment dates and terms, you could spend a lot more time looking through them manually, instead of letting Excel do the work for you. Spreadsheet Data 1 shows a list of sales orders with a mixture of payment terms. Some are thirty days, some are seven days and so on. Also, some are paid and some are not. In this spreadsheet tip, we will show you a single formula which will show you how to quickly identify those people who are late at paying you.
How To Flag Late Payments
Spreadsheet Data 1
In Spreadsheet Data 1, the orders can be one of three statuses; Paid, Outstanding or Late. To see if it is paid or not, we first check for a date in the ‘Paid Date’ column. If there is no date there, then the order invoice is still unpaid. We then check the due date against today’s date to determine if it is oustanding or late. To do this we used a ‘nested IF statement’.
To start though, a single IF statement determines whether the payment is late or not. In cell F2 we write:
=IF(E2=””,”Paid or Outstanding”,”Paid”)
In English this reads as:
IF E2 is blank (empty inverted commas) then the invoice is paid or outstanding, otherwise (i.e. there is a paid date), the invoice is paid.
Copy this down column F for the rest of the rows to get the same as Spreadsheet Data 2.
Spreadsheet Data 2
We now need to differentiate between those unpaid invoices which are Outstanding, and those which are Late. To do this, we put another IF statement inside the current IF statement. This is called ‘nesting’. We amend the formula in cell F2 to read
In English this reads as:
IF E2 is blank, check if the due date (D2) is before today (the constant TODAY()). If it is, show “LATE”, otherwise, show “Outstanding”, and if E2 is not blank show “Paid”>
We copy this down the rest of the cells in F colum to get Spreadsheet Data 3.
Spreadsheet Data 3
As a final touch, we can use some Conditional Formatting to draw the eye to LATE orders. First, highlight column F. Then select ‘Conditional Formatting’ from the FORMAT menu. This brings up the ‘Conditional Formatting’ dialog box. We enter two Conditions; Bold and Red for ‘LATE’ and Grey for ‘Paid’, as in Spreadsheet Data 4, then click OK.
Spreadsheet Data 4
We now see that the late unpaid invoices are now flagged up with a red, bold ‘LATE’ next to them. Paid invoices are grey. With a long sales order sheet this will allow you to immediately see the unpaid orders, as in Spreadsheet Data 5.
Spreadsheet Data 5
The constant TODAY() will change value according to the day on which you open the spreadsheet, so the Status of the orders will update as well. This is a useful tool which you can keep using into the future, and keep on top of late payments.