Milestone Deadline Digest: A Zapier Automation That Emails Your Case Milestones Every Weekday

What This Builds

Your own commitments on a case, such as the records review, the interview plan to your supervisor or the draft report, are easy to lose between interviews and meetings. This build emails you each weekday morning with the milestones that are overdue, the ones inside your look-ahead window and the ones with no date.

It tracks the work you owe, not records owed to you by others. A separate digest covers document requests. The sheet counts the days and writes the list, and Zapier carries one cell to your inbox. There is no AI step. The email goes to you alone.

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 run uses two tasks (the lookup and the email). On weekdays only that is roughly 40 to 46 tasks a month, and a Zap that runs every day of the week uses roughly 56 to 62. 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).
• Milestone due dates that come from your own procedures and your supervisor. You type each one. This guide states no deadline rule, and the Look Ahead Days number is your own choice.

The Concept

Think of a desk calendar with one page that is rewritten overnight. The sheet rewrites the page whenever a milestone changes. Zapier is a courier who reads the page each weekday morning and brings you a copy.

The page 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 Milestones tab for "overdue", a day with nothing overdue would return nothing and no email, and you could not tell a clear day 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 run.

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 milestone codes, case codes, short labels, dates and status values.

Keep everything else out of the sheet. Use codes such as "MS-2" and "2026-014", and a short label in your own words such as "Draft report to supervisor". Never enter a subject's, witness's, customer's or colleague's name, an account, claim or policy number, an amount, an allegation or a finding. A label names the task and never describes 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 morning dashboard.

Build It Step by Step

Part 1: Build the Milestones Tab and the Summary Tab

Create a new spreadsheet. Name one tab exactly Milestones 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 Milestones, row 1 holds the headers and each milestone gets one row from row 2 down.

Add the Done dropdown with Data, then Data validation, then Add rule. Pick Dropdown and enter Yes and No for E2:E201. Format D as a date and F as a plain number.

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

Look Ahead Days counts calendar days, not working days. Keep a number in B2 at all times, because a blank cell is read as 0.

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 milestones. Every range below ends at row 201, so extend them all together if you ever need more rows.

Days Left, cell F2

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

Blank when there is no milestone code or no due date. Otherwise the due date minus today, so a due date in the past gives a negative number.

Flag, cell G2

=IF(A2="","",IF(E2="Yes","Done",IF(D2="","Missing date",IF(F2<0,"OVERDUE",IF(F2<=Summary!$B$2,"DUE SOON","Later")))))

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

• Milestone empty: blank. This is why a formula filled down to row 201 stays silent on empty rows.
• Done is Yes: "Done".
• Due Date empty: "Missing date".
• Days Left below 0: "OVERDUE".
• Days Left at most Look Ahead Days (Summary!$B$2): "DUE SOON".
• Anything else: "Later".

Done is tested first so a milestone you finished late never shows as OVERDUE. A row where Done is still blank is treated as not done.

Digest Line, cell H2

=IF(A2="","",A2&" | "&B2&" | "&C2&" | "&IF(D2="","no due date","due "&TEXT(D2,"yyyy-mm-dd")&", "&IF(F2<0,-F2&IF(F2=-1," day"," days")&" past",IF(F2=0,"today","in "&F2&IF(F2=1," day"," days"))))&" | "&G2)

The line reads Milestone | Case | Label | due date with a day count | Flag. The date is wrapped in TEXT so it prints as 2027-02-19 and not as a serial number. A blank due date prints "no due date", and a blank row returns blank.

Action Count, Summary!C2

=COUNTIF(Milestones!$G$2:$G$201,"OVERDUE")+COUNTIF(Milestones!$G$2:$G$201,"DUE SOON")+COUNTIF(Milestones!$G$2:$G$201,"Missing date")

Overdue Count, Summary!D2

=COUNTIF(Milestones!$G$2:$G$201,"OVERDUE")

Both count 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(Milestones!$H$2:$H$201,(Milestones!$G$2:$G$201="OVERDUE")+(Milestones!$G$2:$G$201="DUE SOON")+(Milestones!$G$2:$G$201="Missing date"))),"No milestones overdue or due in the look-ahead window")

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 OVERDUE, DUE SOON 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, so the cell is never empty.

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 Left 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 Day. Pick a morning time. Zapier's Schedule by Zapier page lists a Trigger on weekends? option on this event. Set it to No for weekday mornings only, or leave it on if you work weekends. If you do not see the option when you build, the Zap runs every day, which is fine.
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 Milestone digest: , the Action Count field from step 2, the text to act on,, the Overdue Count field and the text overdue. 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 counts and the digest text. The email test should reach your inbox. Then turn the Zap on.

Part 5: Test and Refine

Add a dummy milestone with a due date in the past and Done set to No. Confirm that Flag reads OVERDUE, that both counts rise by one and that the line appears in Digest Lines. Then set Done to Yes 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 and Look Ahead Days is 5, so a milestone is DUE SOON when it is due on or before 2027-02-27. Every code, label and date below is invented.

Working through the Flag formula:

• MS-1: Days Left is 2027-02-19 minus 2027-02-22, which is -3. Not done, date present, below 0, so OVERDUE.
• MS-2: 2027-02-27 is 5 days after 2027-02-22. Not below 0, and 5 is at most 5, so DUE SOON. This is the boundary case.
• MS-3: February 2027 has 28 days, so 2027-02-22 to 2027-02-28 is 6 days and 5 more reach 2027-03-05, which makes 11. Above 5, so Later.
• MS-4: Done is Yes, so Done. Its Days Left would be 2, which would otherwise be DUE SOON. This is why Done is tested first.
• MS-5: no due date, so Missing date. Days Left is blank, but the date test comes before the day count.
• Row 7: no milestone code, so every formula returns blank and nothing is counted.

Counts: OVERDUE is 1 (MS-1), DUE SOON is 1 (MS-2) and Missing date is 1 (MS-5). Action Count is 1 + 1 + 1 = 3. Overdue Count is 1.

The Digest Lines cell holds three lines in sheet order:

MS-1 | 2026-014 | Records review done | due 2027-02-19, 3 days past | OVERDUE
MS-2 | 2026-014 | Draft report to supervisor | due 2027-02-27, in 5 days | DUE SOON
MS-5 | 2026-021 | Triage note to supervisor | no due date | Missing date

The email arrives with the subject "Milestone digest: 3 to act on, 1 overdue" and those three lines in the body. If every milestone were Done or Later, both counts would be 0 and the body would read "No milestones overdue or due in the look-ahead window". The email still arrives.

Setup: one spreadsheet, one Zap, three steps. Input: the daily schedule and the milestones you keep current. Output: one email to you with two counts in the subject and the milestones that need action in the body. Time saved: an estimated minute a day, compared with checking your case list for what is next. The digest is only as current as your last edit, so marking milestones Done and entering dates is the price.

What to Do When It Breaks

• No email arrived this morning. 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 each weekday. 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 Left matches today's date arithmetic.
• A milestone you know is close is missing. The digest only reflects the rows. Check that the milestone has a row, that the due date is a real date and not text, and that Done is not Yes.
• A milestone due on a weekend arrives late in your mind. Look Ahead Days counts calendar days. If you run weekdays only, raise the number so a Saturday or Sunday due date shows up before the weekend.
• Dates show as long numbers. A date was joined without TEXT. Re-paste the Digest Line formula exactly.
• A formula shows #REF! or #NAME?. A tab was renamed. Rename it back, or fix the tab name inside the formulas.
• Line breaks collapse in the email. Switch the Gmail step to plain text if it offers a body type, and re-test.

Variations

• Simpler version: skip Zapier and open the Summary tab each morning. The counts and lines are the same.
• Extended version: add a Google Form for logging a new milestone. Form answers land on their own responses tab, and you copy each one into Milestones yourself. Never point a Form at the Milestones tab, because it would overwrite your formulas. The Zap still runs on its schedule.
• Weekly instead of daily: use the Every Week trigger and pick Monday if a daily email is more than you need.

What to Do Next

• This week: build the tabs and test with dummy rows. Then switch the Zap on and read the first real emails against your own list.
• This month: adjust Look Ahead Days after you see how early you want the warning.
• Advanced: build the sister digests for document requests and case aging, 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 from your own procedures. It does not set a deadline or state what any procedure requires. Your supervisor and your unit's procedures decide that. Zapier and Google may change their screens, so follow the labels you see.