Build a Case Deadline Tracker in Google Sheets with AI Formulas

What This Does

Google Sheets' AI assistant helps you build a case management tracker with automatic deadline calculations, color-coded alerts for overdue contacts, and a weekly priority list — without needing to know spreadsheet formulas.

Before You Start

• Google Sheets open (sheets.google.com)
• Logged into a Google account (free)
• List of your current active cases (case numbers or identifiers, not real names)

Steps

1. Create your case tracker structure

In a new Google Sheet, click cell A1 and type these column headers across row 1:

• A: Case ID | B: Family (code/initials) | C: Date Last Contact | D: Contact Frequency Required | E: Next Contact Due | F: Court Date | G: Report Due | H: Priority | I: Notes

2. Ask Gemini to write your formulas

Click the Gemini icon in the top-right of Google Sheets (sparkle icon), or go to Extensions → Gemini in Sheets. Type:

"In column E, write a formula that calculates the next required contact date by adding the number of days in column D to the date in column C."

Click "Insert" when Gemini shows you the formula. Then ask: "In column H, write a formula that shows 'OVERDUE' in red if today's date is past the date in column E, 'DUE SOON' in yellow if within 3 days, and 'OK' in green otherwise."

What you should see: Gemini writes the formula and offers to insert it directly.

3. Add conditional formatting for visual alerts

With your Priority column formula working, select the entire column H. Go to Format → Conditional formatting. If Gemini hasn't already set up colors, ask: "Help me add red/yellow/green conditional formatting to column H based on whether it says OVERDUE, DUE SOON, or OK."

4. Build your weekly view

On a second sheet tab, ask Gemini: "Write a formula that filters all rows from the Case Tracker sheet where the Next Contact Due date is within the next 7 days, and show them sorted by date."

Real Example

Scenario: You have 22 active cases with different required contact frequencies (high-risk = weekly, standard = monthly) and you keep losing track of who you haven't visited recently.

What you set up: Enter all 22 cases with their last contact dates and required frequencies. Gemini writes the formulas. Every morning you open the sheet and see your 3 cases marked OVERDUE in red and your 5 cases marked DUE SOON in yellow — your day's priority list, automatically calculated.

What you get: A 5-minute daily check instead of mentally reviewing 22 cases; no more missed required contacts.

Tips

• Use case codes/initials instead of real names in your spreadsheet — this is a personal productivity tool, not an official system, so keep it general
• Ask Gemini "What other formulas would make this case tracker more useful?" for ideas you haven't thought of
• Sort by "Court Date" column to see what court prep is due in the coming weeks

Tool interfaces change — if a button has moved, look for similar AI/magic/smart options in the same menu area.