How to Set Up Payment Due Date Reminders in a Spreadsheet
Subtitle: Due dates that skip weekends and holidays, working days left, and a "Remind now" flag with WORKDAY and NETWORKDAYS
Written with AI assistance.
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.
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
Practical spreadsheet templates for small businesses, made with AI assistance.
Comments
Join the discussionThe WORKDAY(F5-1,1,...) step is a neat way to push a weekend or holiday due date forward, and the Dutta Mart row shows clearly why working days matter. Two things would trip it up in daily use. First, with the threshold at 3, an invoice reads "Remind now" on four working mornings in a row (3, 2, 1 and 0 days left), and the sheet cannot tell whether the nudge already went out on the first one. A "Reminded on" date in column K, tested in J5 before the Remind now check, for example IF(K5<>"","Reminded",...), turns the flag into a to-do that clears once it is done. Second, the holiday range. Six cells in L5:L10 is tight for a year of public and bank holidays, and the formulas only know the dates actually listed. An invoice raised in December on 30 or 45 day terms falls due in January, so next year's holidays need to be in the range before then. A taller range like L5:L40 costs nothing, since blank cells are ignored.