Google Sheets Lease Expiration Tracker with Auto-Reminders
Missing a lease renewal deadline is one of those mistakes you only make once. Or twice, if you're me. (For a broader approach to due-date tracking, see our Google Sheets due date reminders guide.)
I had a tenant go month-to-month because I forgot their lease expired. Took me 3 months to get them onto a new 12-month lease. During that time they could have left with 30 days notice and I would have eaten a $2,200 vacancy.
Here's how to build a lease expiration tracker in Google Sheets that actually reminds you before deadlines sneak up.
The Spreadsheet Layout
Create a new tab called "Leases" in your property management sheet. Here are the columns you need:
A: Unit. B: Tenant Name. C: Lease Start. D: Lease End. E: Monthly Rent. F: Days Until Expiry. G: Status. H: Notes.
Fill in your units. Lease Start and Lease End should be actual date values, not text. Format them as dates (Format > Number > Date).
For a 12-unit building, your sheet should have 12 rows of data starting in row 2.
Days Until Expiry Formula
Column F is the critical one. Here's the formula for F2:
=IF(D2="", "", DATEDIF(TODAY(), D2, "D"))
This calculates the number of days between today and the lease end date. If the lease end is blank, it shows nothing.
If the lease has already expired, this formula will show an error. To handle that, wrap it:
=IF(D2="", "", IF(D2
Now it shows "EXPIRED" for past leases and a number for upcoming ones. Copy this formula down for every row.
Status Column
Column G gives you a plain-English status. Here's the formula for G2:
=IF(D2="", "", IF(D2
This gives you five statuses. "Active" means more than 60 days out. "Upcoming" means 31 to 60 days. "Renew Soon" is 16 to 30 days. "Urgent" is 15 days or less. "Expired" means you're already late.
Conditional Formatting
Select columns F and G (or the whole data range). Add conditional formatting rules:
Custom formula =$G2="Expired": dark red background, white text.
Custom formula =$G2="Urgent": red background, white text.
Custom formula =$G2="Renew Soon": yellow background, black text.
Custom formula =$G2="Upcoming": light yellow background.
Custom formula =$G2="Active": green background.
Now your lease tracker is color-coded. You can see at a glance which leases need attention. Green is good. Yellow means start the conversation. Red means you're behind.
Adding a Summary
At the top or bottom of your sheet, add quick counts:
Expiring in 30 days: =COUNTIFS(G2:G20, "Urgent") + COUNTIFS(G2:G20, "Renew Soon")
Expiring in 60 days: =COUNTIF(G2:G20, "Upcoming") + COUNTIFS(G2:G20, "Renew Soon") + COUNTIFS(G2:G20, "Urgent")
Currently expired: =COUNTIF(G2:G20, "Expired")
These three numbers tell you the whole story at a glance.
Auto-Reminders with Apps Script
The spreadsheet is useful, but only if you check it regularly. Let's add email reminders that fire automatically at 60, 30, and 15 days before expiry.
Go to Extensions > Apps Script and paste this:
function checkLeaseExpirations() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Leases");
var data = sheet.getDataRange().getValues();
var today = new Date();
today.setHours(0, 0, 0, 0);
var alerts = [];
var milestones = [60, 30, 15];
for (var i = 1; i < data.length; i++) {
var unit = data[i][0];
var tenant = data[i][1];
var leaseEnd = new Date(data[i][3]);
leaseEnd.setHours(0, 0, 0, 0);
if (!leaseEnd || leaseEnd < today) continue;
var daysLeft = Math.floor((leaseEnd - today) / (1000 * 60 * 60 * 24));
for (var m = 0; m < milestones.length; m++) {
if (daysLeft === milestones[m]) {
alerts.push(unit + " - " + tenant + ": lease expires in " + daysLeft + " days (" + leaseEnd.toDateString() + ")");
}
}
}
if (alerts.length > 0) {
var subject = "Lease Expiration Reminder: " + alerts.length + " lease(s) approaching";
var body = "The following leases need attention:
" + alerts.join("
");
MailApp.sendEmail("[email protected]", subject, body);
}
}
Replace the email address with yours. Then set up a daily trigger (clock icon > Add Trigger > Time-driven > Day timer). The script checks every day and emails you only on the exact milestone days: 60, 30, and 15 days before expiry.
The Limitations
This works. But it has the same problems as any Apps Script solution.
The milestone approach means if you miss the 60-day email (or it lands in spam), you don't get another reminder until 30 days. That's a full month of silence. If you want reminders every week, you'd need to modify the script, and it gets complicated fast.
Tab names and column positions are hardcoded. Rearrange your sheet and the script breaks. No error message. It just silently does the wrong thing.
And there are no text alerts. Email only. I've lost count of how many important emails I've missed because they got buried under newsletters and promotional junk.
A Better Way to Track Lease Dates
RentGuard also watches for lease expirations. Connect your sheet, and it will alert you when leases are approaching their end date. Text and email.
No Apps Script to maintain. No triggers to break. No hardcoded column positions that silently fail when you move things around.
You can grab the free lease tracker template to get started with the spreadsheet layout. Use the formulas and formatting from this post. And if you want alerts that actually reach you, connect it to RentGuard.
Either way, don't be the landlord who finds out a lease expired 2 months ago. Track it, format it, and set up some kind of reminder. Your vacancy rate will thank you.
Stop missing late rent payments
RentGuard monitors your Google Sheet and alerts you when rent is overdue or maintenance is aging. No migration. 5 minute setup. 30 days free.
Start Free Monitoring →