How to Track Overdue Rent in Google Sheets

Google Sheets is honestly a great tool for tracking rent. I used it exclusively for 12 units over 3 years. Here's exactly how I set it up to catch overdue payments. (If you need a starting template, check out my best rent tracking spreadsheet template post or download the free Excel template directly.)

The Basic Setup

Create a new Google Sheet. Name it something like "Rent Roll 2025." (For a full walkthrough, see our Google Sheets rent roll setup guide.) First tab is January, second is February, etc.

Each tab has these columns: Unit (A), Tenant (B), Rent Amount (C), Due Date (D), Grace Period End (E), Date Paid (F), Days Overdue (G), Status (H).

Fill in your units. Most landlords have rent due on the 1st with a grace period through the 5th. Put the due date as the 1st and the grace period end as the 5th.

The Formula That Does the Heavy Lifting

Column G, "Days Overdue," is where the magic happens. Here's the formula:

=IF(F2<>"", 0, IF(TODAY()>E2, TODAY()-E2, 0))

In plain English: if there's a date in the "Date Paid" column, days overdue is 0. If there's no payment and today is past the grace period, show how many days late.

Column H, "Status," makes it even clearer:

=IF(F2<>"", "Paid", IF(TODAY()>E2, "OVERDUE", IF(TODAY()>D2, "Grace Period", "Not Yet Due")))

Now you can see at a glance what's going on.

Conditional Formatting

Select the entire data range. Go to Format > Conditional Formatting.

Add a rule: Custom formula is =$H2="OVERDUE". Set the background to red and text to white.

Add another rule: Custom formula is =$H2="Grace Period". Set the background to yellow.

Add another: =$H2="Paid". Background green.

Now your sheet lights up like a traffic light. Red means call that tenant.

The Summary Row

Below your last unit, add a summary. Total Expected: =SUM(C2:C13). Total Collected: =SUMIF(F2:F13,"<>",C2:C13). Total Outstanding: Expected minus Collected.

I also count overdue units: =COUNTIF(H2:H13,"OVERDUE"). When that number is greater than 0, I know I have work to do.

The Problem With This System

It works great. Seriously. For 2 years I had zero issues with it.

The problem is you have to open the sheet. Every day. And check it.

I'm a part-time landlord. I have a day job. Some mornings I'm in meetings from 8 to noon. By the time I think about rent, it's 3pm and I realize I haven't looked at my sheet in 4 days.

One time I didn't check for 9 days. A tenant in Unit 7B was 9 days late and I had no idea. That's $50 in late fees I didn't charge because I felt bad catching it so late. And 9 days of rent float I'll never get back. If this sounds familiar, I wrote about how to stop missing late rent payments.

Adding Alerts to Your Sheet

You can set up Google Apps Script to email you. It's doable but it's janky. You write a script, set a trigger, and hope Google doesn't throttle it. (We cover all three methods in our Google Sheets email alerts guide.)

I did this for about 6 months. It broke twice. Once because Google changed something in their API, once because I accidentally deleted the trigger.

Both times I didn't realize it was broken for over a week.

A Simpler Approach

I ended up building a tool that does the monitoring part so I could keep using my sheet as-is. That's RentGuard.

You connect your Google Sheet. RentGuard reads it every day. If rent is overdue past your grace period, you get an email and optional text. You can mark it paid right from the email.

No Apps Script. No triggers to maintain. No code at all.

Your sheet stays yours. RentGuard just watches it and tells you when something's off.

If you're tired of manually checking your sheet every day, descoshop.com takes about 5 minutes to set up.