Case Aging Digest: A Zapier Automation That Flags Quiet and Old Cases Every Monday

What This Builds

A case with no activity does not announce itself. It sits in the queue while newer work crowds it out, and the gap shows up weeks later in a supervisor's review. This build sends you a Monday email that lists the case codes that have gone quiet, the ones that have been open a long time and the ones whose dates are missing.

The sheet counts the days and writes the list. Zapier carries one cell to your inbox, so the email arrives whether the list is long or empty. There is no AI step. The email goes to you alone, and what to do about a flagged case stays with you and your supervisor.

Prerequisites

• A Google account that holds the sheet and a Gmail address you are allowed to send from. If your employer runs Google Workspace, ask IT whether outside apps such as Zapier may connect to it.
• A Professional plan or better ($29.99/month). The Zap has a trigger plus two actions, which is three steps, and Zapier's free plan allows two-step Zaps only. Check Zapier's current plan page before you build.
• Total ongoing cost of the finished build: the Zapier plan above. Google Sheets and Gmail add nothing on a Google account you already have. Each weekly run uses two tasks (the lookup and the email), so roughly 8 to 10 tasks a month. Zapier counts only successful action steps as tasks.
• Comfort writing a spreadsheet formula and copying it down a column.
• Your employer's AI and data policy read, and an answer on whether Zapier is an approved tool for even this coded data (see "What Zapier Sees and Keeps" below).
• Two numbers that come from your own unit's practice: how many days without activity you want flagged (Quiet Days) and how many days open you want flagged (Old Case Days). This guide states no rule about either.

The Concept

Think of a wall chart in a records room with a single sticky note on it. The sheet rewrites the note whenever a case changes. Zapier is a courier who reads the note every Monday and brings you a copy.

The note is always there, and that matters because of how Zapier's search step behaves. A lookup that finds zero rows halts the Zap ("Safely halted" in Zap History), no later step runs and no email goes out. If the Zap searched the Cases tab for "quiet cases", a week with no quiet cases would return nothing and no email, and you could not tell a good week from a broken Zap. By searching for one Summary row whose Key is always "summary", the lookup always finds a row. The email arrives every week.

The Zap has no AI step, no formatting step and no loop. The sheet decides what is flagged and Zapier only moves text.

What Zapier Sees and Keeps

Zapier handles whatever the Zap reads, and Zap History stores the data of each step. The lookup step stores the whole Summary row, and the Gmail step stores the subject and body. In this build that means case codes, dates, status values and the two settings.

Keep everything else out of the sheet. A case is a code such as "2026-014" and nothing more. Never enter a subject's, witness's, customer's or colleague's name, an account, claim or policy number, an amount, an allegation, a finding or a description of what the case is about. Case details stay in your case management system. Even coded data belongs to your employer, so check the AI and data policy and whether Zapier is an approved tool before you connect it. If it is not approved, stop after Part 3 and use the Summary tab on its own as a Monday dashboard.

Build It Step by Step

Part 1: Build the Cases Tab and the Summary Tab

Create a new spreadsheet. Name one tab exactly Cases and another exactly Summary. Tab names are case-sensitive in formulas, so match them. This digest gets its own spreadsheet because its columns differ from your other digests.

On Cases, row 1 holds the headers and each case gets one row from row 2 down.

Add the Status dropdown with Data, then Data validation, then Add rule. Pick Dropdown and enter the four values for D2:D201. Format B and C as dates and E and F as plain numbers.

On Summary, row 1 holds these headers and row 2 holds the one data row.

Both numbers are your own choices from your unit's practice. Keep a number in each cell at all times.

Part 2: Add the Formulas

Paste each formula into row 2 of its column and copy it down to row 201. That leaves room for 200 cases. Every range below ends at row 201, so extend them all together if you ever need more rows.

Days Quiet, cell E2

=IF(OR(A2="",C2=""),"",TODAY()-C2)

Days Open, cell F2

=IF(OR(A2="",B2=""),"",TODAY()-B2)

Each is blank when the case code or its date is missing. Otherwise it is today minus the date, so the count grows by one each day.

Flag, cell G2

=IF(A2="","",IF(D2="Closed","Closed",IF(OR(B2="",C2=""),"Missing date",IF(E2>=Summary!$B$2,"QUIET",IF(F2>=Summary!$C$2,"OLD","Active")))))

The formula reads left to right and stops at the first test that is true.

• Case empty: blank. This is why a formula filled down to row 201 stays silent on empty rows.
• Status Closed: "Closed".
• Opened Date or Last Activity Date empty: "Missing date".
• Days Quiet at least Quiet Days (Summary!$B$2): "QUIET".
• Days Open at least Old Case Days (Summary!$C$2): "OLD".
• Anything else: "Active".

Closed is tested first so a finished case never appears in the digest. A case that is both quiet and old shows QUIET, because QUIET is tested before OLD. If you would rather see OLD first, swap the two tests and the two labels in the formula. The Action Count and the digest do not change, since they count both labels.

Digest Line, cell H2

=IF(A2="","",A2&" | "&D2&" | "&IF(B2="","open date missing","open "&F2&IF(F2=1," day"," days"))&" | "&IF(C2="","last activity date missing",IF(E2=0,"last activity today","last activity "&E2&IF(E2=1," day"," days")&" ago"))&" | "&G2)

The line reads Case | Status | open N days | last activity N days ago | Flag. A blank date prints a short "date missing" phrase instead of a number, and a blank case returns blank.

Action Count, Summary!D2

=COUNTIF(Cases!$G$2:$G$201,"QUIET")+COUNTIF(Cases!$G$2:$G$201,"OLD")+COUNTIF(Cases!$G$2:$G$201,"Missing date")

The count uses the labels the Flag column writes for filled rows. Empty rows return blank, so they are never counted.

Digest Lines, Summary!E2

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Cases!$H$2:$H$201,(Cases!$G$2:$G$201="QUIET")+(Cases!$G$2:$G$201="OLD")+(Cases!$G$2:$G$201="Missing date"))),"No quiet, old or undated open cases")

The four ranges inside FILTER all run from row 2 to row 201, so they are the same height. FILTER keeps the Digest Line of every row flagged QUIET, OLD or Missing date, and TEXTJOIN stacks those lines with a line break between them. When nothing matches, FILTER returns an error and IFERROR substitutes the fallback sentence.

Re-read each formula once after pasting. Count the opening and closing parentheses, confirm every range ends at row 201 and confirm the tab names match.

Part 3: Keep Calculation on Automatic

TODAY() recalculates as the sheet is used. Google's calculation settings page lists only two choices, Automatic and Manual, and no scheduled recalculation. In the sheet, open File, then Settings, then the Calculation tab, and keep the mode on Automatic. If the counts in an email look stale, open the sheet before the run and check that Days Quiet matches today's date.

Part 4: Build the Zap

In Zapier, create a new Zap with three steps.

1. Trigger. Choose the app Schedule by Zapier and the event Every Week. Pick Monday and a morning time.
2. Action. Choose Google Sheets and the event Lookup Spreadsheet Row. Connect your Google account. Choose the spreadsheet and the Summary tab. Set the lookup column to Key and the lookup value to summary. Add no other condition. The sheet already did the work, and you want a lookup that always finds its row.
3. Action. Choose Gmail and the event Send Email. In To, enter your own address and nobody else's. In Subject, build the line in this order: the text Case aging digest: , the Action Count field from step 2 and the text to review. In Body, insert the Digest Lines field from step 2. If the action offers a Body type setting, choose plain text so the line breaks from the sheet survive.

Zapier lists the Summary columns as fields named after your row 1 headers. If a header is missing from the field list, run the lookup test again so Zapier reloads the headers.

Test each step. The lookup test should return the summary row with the count and the digest text. The email test should reach your inbox. Then turn the Zap on.

Part 5: Test and Refine

Add a dummy case with a last activity date older than your Quiet Days and Status Open. Confirm that Flag reads QUIET, that Action Count rises by one and that the line appears in Digest Lines. Then set Status to Closed and confirm that it disappears. Delete the dummy row when done.

Real Example: A Monday in February

Invented setup: today is Monday 2027-02-22, Quiet Days is 14 and Old Case Days is 90. Every code, status and date below is invented.

Working through the Flag formula:

• 2026-014: Days Quiet is 3 (2027-02-19 to 2027-02-22). Days Open is 83 (31 days to 2027-01-01, 31 more to 2027-02-01, then 21). Below 14 and below 90, so Active.
• 2026-011: Days Quiet is 21 (2027-02-01 to 2027-02-22). Days Open is 140 (31 to 2026-11-05, 30 to 2026-12-05, 31 to 2027-01-05, 31 to 2027-02-05, then 17). Quiet 21 is at least 14, so QUIET. It is also old, but QUIET is tested first.
• 2026-009: Days Quiet is 7. Days Open is 112 (30 to 2026-12-02, 31 to 2027-01-02, 31 to 2027-02-02, then 20). Not quiet, and 112 is at least 90, so OLD.
• 2026-019: Days Quiet is 14 and Days Open is 14 (both dates are 2027-02-08). 14 is at least 14, so QUIET. This is the boundary case.
• 2026-003: Status is Closed, so Closed. The blank dates never matter because Closed is tested first.
• 2026-021: no last activity date, so Missing date. Days Open is 5 (2027-02-17 to 2027-02-22), and the line still shows it.
• Row 8: no case code, so every formula returns blank and nothing is counted.

Counts: QUIET is 2 (2026-011 and 2026-019), OLD is 1 (2026-009) and Missing date is 1 (2026-021). Action Count is 2 + 1 + 1 = 4.

The Digest Lines cell holds four lines in sheet order:

2026-011 | Waiting on records | open 140 days | last activity 21 days ago | QUIET
2026-009 | Open | open 112 days | last activity 7 days ago | OLD
2026-019 | With supervisor | open 14 days | last activity 14 days ago | QUIET
2026-021 | Open | open 5 days | last activity date missing | Missing date

The email arrives with the subject "Case aging digest: 4 to review" and those four lines in the body. If every open case were Active, the count would be 0 and the body would read "No quiet, old or undated open cases". The email still arrives.

Setup: one spreadsheet, one Zap, three steps. Input: the Monday schedule and the dates you keep current. Output: one email to you with a count in the subject and the flagged case codes in the body. Time saved: an estimated minute a week, compared with paging through a case list. The digest is only as current as your last date entry, so updating Last Activity Date when you work a case is the price.

What to Do When It Breaks

• No email arrived on Monday. This is the failure you will not see, because a stopped Zap makes no noise. Check that the Zap is switched on. Then open Zap History for the run. A "Safely halted" lookup means no row had Key equal to summary, usually because someone renamed the Key cell, changed its text or moved the Summary tab. Restore the text summary in A2 exactly. A run that never appears at all usually means the Zap was turned off or the Google connection expired. Reconnect the account under your Zapier connected accounts and re-test the lookup.
• Set a standing reminder. Put a calendar reminder for mid-morning on Monday. If no digest has arrived by then, treat that as the alarm and check the Zap that day.
• Counts look wrong or stale. Open the sheet and confirm Calculation is on Automatic. Then check that Days Quiet matches today's date arithmetic.
• A quiet case never shows up. Check that its Last Activity Date is a real date and not text, that Status is not Closed and that the case has a row. A case you forgot to enter cannot be flagged.
• A day count is negative or very large. A date was typed with the wrong year or the wrong order. Fix the date and the formula recalculates.
• Line breaks collapse in the email. Switch the Gmail step to plain text if it offers a body type, and re-test.
• A formula shows #REF! or #NAME?. A tab was renamed. Rename it back, or fix the tab name inside the formulas.

Variations

• Simpler version: skip Zapier and open the Summary tab each Monday. The count and lines are the same.
• Extended version: add a Google Form for logging a new case. Form answers land on their own responses tab, and you copy each one into Cases yourself. Never point a Form at the Cases tab, because it would overwrite your formulas. The Zap still runs on its schedule.
• Separate windows: raise Old Case Days for a unit that runs long cases and lower Quiet Days for a unit that turns cases fast.

What to Do Next

• This week: build the tabs and test with dummy rows. Then switch the Zap on and compare the first real email with your own sense of the queue.
• This month: adjust Quiet Days and Old Case Days after you see which flags you act on and which you ignore.
• Advanced: build the sister digests for document requests and your own milestones, each in its own spreadsheet with its own Summary tab.

Advanced guide for fraud examiners and financial investigators. The digest flags dates you have typed. It does not decide whether a case stays open, closes or goes anywhere else. Your supervisor and your unit's procedures decide that. Zapier and Google may change their screens, so follow the labels you see.