Spreadsheets

Bill due date calendar in Google Sheets with colour alerts

Build a bill due date calendar in Google Sheets with a days-to-go column from TODAY(), red overdue rows and a paid status that resets itself each month.

Editorial illustration for Bill due date calendar in Google Sheets with colour alerts

A bill due date calendar in Google Sheets lists each bill with its due day of the month and shows how many days are left using TODAY(). Conditional formatting turns overdue rows red, and a paid status that is based on a date, rather than a checkbox, resets itself every month with nothing to untick.

What columns does the bill calendar need?

  • A: Bill (electricity, broadband, credit card, rent, EMI)
  • B: Due day of month, a number from 1 to 31
  • C: Usual amount in rupees
  • D: Last paid for - the month you last cleared it, entered as any date in that month
  • E: This month's due date, calculated
  • F: Days to go, calculated
  • G: Status, calculated

Credit card dues come from your statement and can differ month to month, so check the statement before trusting column B for that row. The same goes for any bill whose date moves.

Which formulas give the due date and days to go?

In E2, this month's due date. The MIN part stops a 31 from spilling into the next month when the month is shorter:

=DATE(YEAR(TODAY()), MONTH(TODAY()), MIN(B2, DAY(EOMONTH(TODAY(),0))))

In G2, the status:

=IF(AND(D2<>"", EOMONTH(D2,0)=EOMONTH(TODAY(),0)), "Paid", "Due")

In F2, days to go, blank once paid:

=IF(G2="Paid", "", E2-TODAY())

A negative number means the due date has passed. Fill all three formulas down the list.

How do I get the colour alerts?

  1. Select A2:G30.
  2. Choose Format, then Conditional formatting, then Custom formula is.
  3. For red, enter =AND($G2="Due", $F2<0).
  4. Add an amber rule: =AND($G2="Due", $F2>=0, $F2<=3).
  5. Add a green rule for paid rows: =$G2="Paid".

Because the colour rules read the status column, paying a bill changes the whole row to green once you enter the month in column D.

How does the monthly reset work?

The usual approach is a Paid checkbox that you must untick on the first of every month, and it is easy to forget. Here, after you pay a bill, enter any date from that month in column D, say 8-Nov. The status formula compares the month in D with the current month. On 1 December, column D still says November, so every bill goes back to Due on its own.

A quick example with made-up dates

Say today is 12 November and column D is blank for everything except rent.

BillDue dayStatusDays to go
Rent5Paid(blank)
Electricity10Due-2 (red)
Broadband15Due3 (amber)
Credit card20Due8
Car loan EMI7Due-5 (red)

The 10th minus the 12th gives -2 and the 7th minus the 12th gives -5, so two rows are red and need action first. Pay those, enter the month in column D, and they turn green.

How do I handle bills that are not on a fixed day?

Some bills, such as a water bill or a quarterly society charge, do not follow a single day each month. For those, type the real due date into column E instead of the formula, and put a note in column A saying the date is manual. Change it when the new bill arrives. For bills paid every two months, add a column for the months it applies to and only keep it Due in those months.

What are common mistakes?

  • Typing the due day as text such as 10th; use the plain number 10.
  • Auto-pay bills still need a month entered in column D, otherwise they sit red even though they were debited.
  • Forgetting that a payment made through UPI or NEFT can take a day to show; leave a buffer before the real due date.

Once the sheet runs, a statement import into SpendixAI can show you each month's actual debits next to what you planned, so you can notice when a bill amount has crept up.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports