Subtitle: Due dates that skip weekends and holidays, working days left, and a "Remind now" flag with WORKDAY and NETWORKDAYS
Written with AI assistance. I edited it. The formulas below were checked in LibreOffice Calc on sample rows (not in Excel or Google Sheets).
Chasing an invoice after it's late is awkward. A polite nudge two or three days before the due date is easy, and it often means you never have to chase at all. The problem is remembering which invoices are close.
Here's a small sheet that does the remembering. It works out each due date from the payment terms, moves it off weekends and holidays, counts the working days left, and flags the ones to remind today. WORKDAY and NETWORKDAYS exist in every current version of Excel and in Google Sheets.
1. The invoice list
Invoices from row 5: A Invoice date, B Customer, C Amount, D Terms (days, e.g. 30), E Paid (type Y when the money arrives). Put an As of date in B2. =TODAY() works; I used a fixed date so the results below don't change.
List your holidays in L5:L10 (one date per cell). Leave spare blank cells: the formulas ignore them.
2. The due date
F5, the plain due date:
=IF(OR(A5="",D5=""),"",A5+D5)
G5, moved to the next working day if it lands on a weekend or holiday:
=IF(F5="","",WORKDAY(F5-1,1,$L$5:$L$10))
The F5-1 then +1 working day trick is the useful bit: a weekday due date stays the same, but a Saturday becomes Monday, and a holiday becomes the next working day. Format F and G as dates.
3. Days left, in calendar and working days
H5, calendar days left (blank once paid):
=IF(OR(G5="",E5="Y"),"",G5-$B$2)
I5, working days left (negative when overdue):
=IF(H5="","",IF(H5>=0,NETWORKDAYS($B$2,G5,$L$5:$L$10)-1,-(NETWORKDAYS(G5,$B$2,$L$5:$L$10)-1)))
NETWORKDAYS counts both end dates, so the -1 makes "due today" read 0. The two branches keep the sign right on each side of the due date. Format H and I as Number, not Date, or a 5 shows up as a date in 1900.
4. The reminder column
J5:
=IF(E5="Y","Paid",IF(G5="","No terms",IF(H5<0,"Overdue",IF(I5<=3,"Remind now","OK"))))
Change the 3 to suit your customers. Then add a conditional formatting rule on the row (Format → Conditional formatting, custom formula =$J5="Remind now") to colour it, so the list reads at a glance.
5. A summary for Monday morning
| Cell | Formula |
|---|---|
| N5: Remind now | =COUNTIF(J5:J104,"Remind now") |
| N6: Overdue | =COUNTIF(J5:J104,"Overdue") |
| N7: Amount overdue | =SUMIFS(C5:C104,J5:J104,"Overdue") |
| N8: Amount to remind about | =SUMIFS(C5:C104,J5:J104,"Remind now") |
| N9: Invoices with no terms | =COUNTIF(J5:J104,"No terms") |
N9 is the check. An invoice with no terms has no due date, so it would never be flagged. If N9 isn't 0, fill in the terms.
6. What the sample showed
Seven made-up invoices, as of Wednesday 21 Oct 2026, with Friday 23 Oct listed as a holiday:
| Customer | Due | Due (working day) | Days left | Working days left | Reminder |
|---|---|---|---|---|---|
| Asha Traders | 1 Oct | 1 Oct | (blank) | (blank) | Paid |
| Bose & Co | Sat 10 Oct | Mon 12 Oct | -9 | -7 | Overdue |
| Dutta Mart | Sat 24 Oct | Mon 26 Oct | 5 | 2 | Remind now |
| Hari Mills | 22 Oct | 22 Oct | 1 | 1 | Remind now |
| Ezra Textiles | 21 Oct | 21 Oct | 0 | 0 | Remind now |
| Chandra Stores | 9 Nov | 9 Nov | 19 | 12 | OK |
| Faiz Stores | (blank) | (blank) | (blank) | (blank) | No terms |
Summary: 3 to remind (500 + 650 + 300 = 1,450), 1 overdue (800), 1 with no terms.
Dutta Mart is the interesting row: five calendar days left looks comfortable, but with a weekend and a holiday in between, it's only two working days.
7. Test it before you trust it
Add rows you know the answer to, then confirm:
- a due date on a Saturday (should move to Monday)
- a due date on one of your holidays (should move to the next working day)
- an invoice due today (should show 0 and "Remind now")
- an invoice with blank terms (should show "No terms" and count in N9)
Then delete them.
Prefer a ready-made file? There's a free Order & Payment Tracker (.xlsx workbook, no macros). You log each order once, and it works out what's still owed and whether it's overdue, by customer and in a one-page summary: https://ashishsrivastavajay.gumroad.com/l/free-order-payment-tracker
About me, and a disclosure: I'm Ashish, a Kolkata-based Excel and Google Sheets builder with 18+ years of work experience, and I write these tutorials and build my workbooks with AI assistance under my direction. The free tracker is mine.
Top comments (0)