Google Sheets Overdue Alert: Track Past-Due Items Automatically
Tracking items with due dates in Google Sheets is straightforward. Knowing when those items become overdue without manually checking every day is the hard part.
Here is how to set up a complete overdue alert system in Google Sheets: visual formatting for when you are looking at the sheet, formulas for quick status checks, and automated email alerts for when you are not looking.
Step 1: The Spreadsheet Layout
You need at minimum these columns:
A: Item Name. B: Assigned To. C: Due Date. D: Date Completed. E: Days Overdue. F: Status.
This works for rent payments, maintenance requests, project tasks, invoice tracking, or anything with a deadline. Fill in your data with real dates in columns C and D.
Step 2: Days Overdue Formula
In column E, row 2:
=IF(D2<>"", 0, IF(C2<>"", IF(TODAY()>C2, TODAY()-C2, 0), ""))
In plain English: if the item is completed (D2 has a date), days overdue is 0. If it is not completed and today is past the due date, calculate the difference. Otherwise show 0 or blank.
Copy this formula down for every row.
Step 3: Status Formula
In column F, row 2:
=IF(D2<>"","Complete",IF(C2="","No Date",IF(TODAY()>C2,"OVERDUE",IF(TODAY()>=C2-3,"Due Soon","On Track"))))
This gives you five statuses: Complete, No Date, OVERDUE, Due Soon (within 3 days), and On Track. You can adjust the 3-day window to whatever makes sense for your workflow.
Step 4: Conditional Formatting
Select your entire data range. Go to Format > Conditional formatting.
OVERDUE rows: Custom formula =$F2="OVERDUE". Red background, white text.
Due Soon rows: Custom formula =$F2="Due Soon". Yellow background.
Complete rows: Custom formula =$F2="Complete". Light green background.
Your sheet now looks like a traffic light. Open it, scan for red, take action. Green means done. Yellow means get ready.
Step 5: Summary Section
Below your data, add quick counts:
Total items: =COUNTA(A2:A100)
Overdue: =COUNTIF(F2:F100,"OVERDUE")
Due Soon: =COUNTIF(F2:F100,"Due Soon")
Complete: =COUNTIF(F2:F100,"Complete")
Most overdue (days): =MAX(E2:E100)
These five numbers tell you everything at a glance. If "Overdue" is greater than 0, you have work to do.
Step 6: Automated Email Alerts
The visual setup works great when you are looking at the sheet. For alerts that come to you, add this Apps Script:
function sendOverdueAlerts() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tracker");
var data = sheet.getDataRange().getValues();
var today = new Date();
today.setHours(0, 0, 0, 0);
var overdue = [];
var dueSoon = [];
for (var i = 1; i < data.length; i++) {
var item = data[i][0];
var assignee = data[i][1];
var dueDate = new Date(data[i][2]);
var completed = data[i][3];
dueDate.setHours(0, 0, 0, 0);
if (completed) continue;
if (!dueDate || isNaN(dueDate)) continue;
var daysOver = Math.floor((today - dueDate) / 86400000);
if (daysOver > 0) {
overdue.push(item + " (" + assignee + ") - "
+ daysOver + " days overdue");
} else if (daysOver >= -3) {
dueSoon.push(item + " (" + assignee + ") - due in "
+ (-daysOver) + " days");
}
}
if (overdue.length === 0 && dueSoon.length === 0) return;
var body = "";
if (overdue.length > 0) {
body += "OVERDUE ITEMS:
" + overdue.join("
") + "
";
}
if (dueSoon.length > 0) {
body += "DUE SOON:
" + dueSoon.join("
");
}
MailApp.sendEmail(
"[email protected]",
"Overdue Alert: " + overdue.length + " overdue, "
+ dueSoon.length + " due soon",
body
);
}
Set a daily trigger (Extensions > Apps Script > Triggers > Add Trigger > Time-driven > Day timer). Pick a morning window like 7am-8am.
Now every morning you get an email listing overdue and upcoming items. No need to open the sheet. The alert comes to you.
Use Cases
Rent payments: Due date is the first of the month. Completed date is when payment was received. Overdue alert fires on day 1 after the grace period. This is exactly how I track rent - see how to track overdue rent in Google Sheets.
Maintenance requests: Due date is the target completion date based on priority (2 days for High, 7 for Medium, 30 for Low). Alert fires when the target passes without completion. My maintenance tracker template uses this pattern.
Invoice tracking: Due date is the payment deadline. Alert fires when invoices are past due. Useful for accounts receivable.
Project management: Due date is the task deadline. Alert fires for missed milestones.
Known Limitations
Apps Script triggers can fail silently. Google occasionally revokes permissions or skips trigger executions. You will not know unless you check.
Email-only alerts mean you might miss them in a busy inbox. No native text/SMS support in Apps Script.
Column positions are hardcoded in the script. Move a column and the script reads wrong data without any error.
For critical tracking like rent collection where a missed alert costs real money, consider a purpose-built monitoring tool. RentGuard handles this exact use case with text + email alerts and zero code maintenance. $15/month.
For more on building automated alert systems, see email alerts in Google Sheets, due date reminders, and conditional notifications.
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 →