To get the next due date for a recurring bill, keep the first due date in one cell and a payment log in another sheet, then use EDATE to add the right number of months for the payments made so far. A second formula compares that date with TODAY() and flags bills due within seven days. EDATE steps by calendar months, so you never count days by hand.
The sheet layout
Make a Bills sheet with these columns, starting in row 2 under a header row:
- A: bill name (Broadband, Society maintenance, Electricity).
- B: first due date, typed once as a real date.
- C: frequency in months: 1 for monthly, 3 for quarterly, 12 for yearly.
- D: next due date, the formula.
- E: status, the flag.
Make a Paid sheet where you add one row each time you pay, with the bill name in column A and the payment date in column B. The Paid sheet is a log; you only ever append to it.
What is the formula that rolls the date forward?
In D2 on the Bills sheet:
=EDATE($B2,COUNTIF(Paid!$A$2:$A$500,$A2)*$C2)
COUNTIF counts how many payments you have logged for this bill. Multiply by the frequency and EDATE moves the first due date that many months forward. After zero payments the next due date is the first one; after two monthly payments it is two months later. Because every result is calculated from the original first date, nothing drifts. This matters at month ends: EDATE from 31 January gives 28 February, but if you then added another month to that result you would get 28 March, not 31 March. Always add to the original date.
Is there a version using MAX?
If you would rather log the due date each payment covered, put it in column C of the Paid sheet and use:
=IF(COUNTIF(Paid!$A$2:$A$500,$A2)=0,$B2,EDATE(MAXIFS(Paid!$C$2:$C$500,Paid!$A$2:$A$500,$A2),$C2))
MAXIFS finds the latest covered due date for that bill, and EDATE steps it on. MAXIFS needs Excel 2019 or Microsoft 365, or Google Sheets. In older Excel, use the COUNTIF version. The month-end drift appears here, so it suits bills due on days 1 to 28.
How do I flag bills due soon?
In E2:
=IF(D2<TODAY(),"Overdue",IF(D2-TODAY()<=7,"Due soon",""))
Overdue is any date before today; Due soon is within the next seven days. Add a conditional format on column E so these two words turn red and amber. Hypothetical example:
| Bill | First due | Every (months) | Payments logged | Next due |
|---|---|---|---|---|
| Broadband | 05 Oct 2025 | 1 | 2 | 05 Dec 2025 |
| Society maintenance | 10 Oct 2025 | 3 | 1 | 10 Jan 2026 |
| Electricity | 28 Oct 2025 | 1 | 0 | 28 Oct 2025 |
If today is 3 December 2025, broadband is two days away and shows Due soon. Society maintenance is over a month away and the flag stays blank. Electricity, with no payment logged and a due date of 28 October, shows Overdue, which is your prompt to check whether you paid it and forgot to log the payment.
What about bills that move?
Variable bills such as electricity do not change their due date pattern, only their amount. Keep the amount out of this sheet, or add an Expected amount column you update each month. Bills tied to a date printed on a bill, such as a bank statement, can shift by a day or two; if the real due date differs, retype the first due date and the log resets cleanly.
Quick checklist
- First due dates are real dates.
- Frequency is in months, not days.
- Bill names match between sheets, with no trailing spaces.
- You append to the Paid sheet every time you pay.
SpendixAI also has bills on Pro, if you would rather not maintain a sheet like this yourself.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


