Automation Recipe: COBRA Deadline Tracker That Never Misses a Notice

What This Builds

Instead of manually tracking COBRA qualifying events in a spreadsheet and hoping you remember to send notices on time, this automation watches for terminations entered in a shared Google Sheet, automatically calculates all three critical COBRA deadlines (employer notice, plan administrator notice, election deadline), creates a reminder task in your task manager, and sends you an email alert for each approaching deadline. Once built, it runs continuously — you'll never miss the 14-day COBRA notice window again, and the $110/day/participant penalty risk drops to near zero.

Prerequisites

• Comfortable using Zapier for at least one basic automation (if not, start with their tutorial at zapier.com/learn)
• A Zapier account (Free plan works; Starter plan at $20/month if you need more zaps)
• Google Sheets (for the termination tracking spreadsheet) — free
• Gmail or Outlook email for notifications
• Optionally: Asana, Trello, or Monday.com for task creation (or use Google Tasks, which is free)
• Time to build: 1.5-2 hours
• Cost: Zapier Starter $20/month (or free with limits); Google Sheets free

The Concept

Think of this like a trip-wire system. Every time a new row appears in your "Terminations" spreadsheet, Zapier wakes up, looks at the qualifying event date, does three date calculations (employer must notify plan in 30 days, plan must send election notice in 14 days after notification, employee has 60 days to elect), and creates calendar reminders or tasks for each deadline. It's like having an assistant who watches your spreadsheet 24/7 and taps you on the shoulder when a deadline is approaching.

Build It Step by Step

Part 1: Set Up Your Termination Tracking Spreadsheet

Create a Google Sheet with these column headers in Row 1:

• Column A: Employee Name
• Column B: Employee ID
• Column C: Qualifying Event (termination, divorce, death, Medicare entitlement, loss of dependent status, reduction in hours)
• Column D: Qualifying Event Date (format: MM/DD/YYYY)
• Column E: Employer Notification Date (when you notified your plan administrator — fill this in when done)
• Column F: COBRA Election Notice Due Date (auto-calculated — leave blank, Zapier fills it)
• Column G: Election Window End Date (auto-calculated — leave blank, Zapier fills it)
• Column H: Status (Pending / Notice Sent / Elected / Waived)
• Column I: Notes

Name this sheet tab "COBRA Tracker."

Part 2: Create Your Zapier Account and First Zap

1. Go to zapier.com and sign up or log in
2. Click Create Zap (the orange button)
3. Name your zap: "COBRA Deadline Tracker"

Step 2a: Set the Trigger

• Search for and select Google Sheets as your trigger app
• Choose the trigger event: New Spreadsheet Row
• Connect your Google account when prompted
• Select your COBRA Tracker spreadsheet and the "COBRA Tracker" sheet tab
• Click Test trigger — Zapier will grab the most recent row to confirm it can read your sheet

What you should see: A row of sample data from your spreadsheet confirmed in the Zapier interface.

Part 3: Add Date Calculation Steps

Step 3a: Calculate the Plan Administrator Notification Deadline

Click + to add an action step. Search for Formatter by Zapier (it's a built-in Zapier tool, no account needed).

• Action event: Date/Time
• Transform: Add/Subtract Time
• Input: Select the Qualifying Event Date field from your trigger
• Modify: 30 Days
• Output Format: MM/DD/YYYY

This gives you the deadline by which the employer must notify the plan administrator.

Step 3b: Calculate the COBRA Election Notice Deadline

Add another Formatter step (same type):

• Input: Select the output date from Step 3a (the 30-day deadline)
• Modify: 14 Days
• Output Format: MM/DD/YYYY

This gives you the deadline for the plan administrator to send the COBRA election notice.

Step 3c: Calculate the Election Window End Date

Add another Formatter step:

• Input: Select the Election Notice Due Date (output of Step 3b)
• Modify: 60 Days
• Output Format: MM/DD/YYYY

This gives you the employee's election deadline (60 days from notice date).

Part 4: Create an Alert Email

Add an action step. Select Gmail (or Email by Zapier if you want to avoid connecting your Google account again).

• To: [your work email address]
• Subject: COBRA Action Required: [Employee Name] - Event: [Qualifying Event Date]
• Body:

COBRA qualifying event logged for [Employee Name] (ID: [Employee ID]).

Qualifying Event: [Qualifying Event] on [Qualifying Event Date]

KEY DEADLINES:
- Employer notifies plan administrator by: [30-day deadline output]
- Plan sends election notice by: [44-day deadline output]
- Employee election window closes: [104-day deadline from event output]

Status: Pending
Action Required: Notify [TPA Name] and confirm election notice will be sent by [notice deadline].

View tracker: [link to your Google Sheet]

Part 5: Update the Spreadsheet with Calculated Dates

Add a final action step. Select Google Sheets → Update Spreadsheet Row.

• Spreadsheet: your COBRA Tracker
• Row: the row that triggered this zap
• Column F (COBRA Election Notice Due Date): the 44-day deadline output
• Column G (Election Window End Date): the 104-day deadline output

What you should see: After the zap runs on a test row, columns F and G auto-populate with the correct deadlines.

Part 6: Turn On the Zap

Click Publish or Turn on Zap. From this point forward, every new row added to your COBRA Tracker spreadsheet will automatically trigger the deadline calculations and email alert.

Real Example: End-to-End Workflow

Setup: Your COBRA Tracker sheet is connected to Zapier. A new row is entered:

• Employee: Maria Garcia
• ID: EE-00847
• Qualifying Event: Involuntary termination
• Event Date: March 14, 2026

What Zapier does automatically (within 2 minutes):

1. Reads the new row
2. Calculates: Employer notification deadline = April 13, 2026
3. Calculates: Election notice due = April 27, 2026
4. Calculates: Election window closes = June 26, 2026
5. Sends you an email: "COBRA Action Required: Maria Garcia - Event March 14, 2026" with all three deadlines
6. Updates the spreadsheet with deadlines in columns F and G

What you do: Notify your TPA or COBRA administrator that day. Mark Column E with today's date (your notification date). Change Column H from "Pending" to "Notice Sent" when the election notice goes out. No manual deadline tracking needed.

Time saved: 5–10 minutes of manual date calculation and reminder setup per termination; eliminates missed deadlines.

What to Do When It Breaks

• "Zapier isn't triggering on new rows" → Check that your Google Sheet is connected correctly. Go to the Zap → re-test the trigger. Sometimes Google Sheets connection needs to be re-authorized in Zapier's Settings → Connected Accounts.
• "The dates look wrong" → Verify the date format in your spreadsheet matches what Zapier expects (MM/DD/YYYY is most reliable). Go to Format → Cells in Google Sheets and set the column to "Date" format.
• "I'm getting duplicate emails" → Add a filter step after the trigger: only continue if Column H (Status) equals "Pending" — this prevents re-triggering when you update existing rows.
• "The row isn't updating with deadlines" → In the Google Sheets Update Row action, make sure you're mapping the correct row identifier. Use "Row Number" from the trigger output, not a static number.

Variations

• Simpler version: Skip the spreadsheet update step — just get the email alert and manually enter deadlines. Faster to build, less automated.
• Extended version: Add a second zap that monitors Column F (notice deadline) and sends you a 3-day-before reminder. Use Google Sheets → Find Rows trigger looking for rows where the deadline is 3 days away.
• Team version: Add a Slack notification step (instead of or in addition to email) to post in your HR team's Slack channel when a COBRA event is logged.

What to Do Next

• This week: Build the basic version (trigger → date calculations → email) and test with 3 historical terminations
• This month: Add the deadline reminder zap (3-day-before alert) for the notice deadline
• Advanced: Connect to your HRIS if it has a Zapier integration — auto-populate the termination spreadsheet when a termination is entered in Workday or ADP, eliminating the manual data entry step entirely

Advanced guide for Benefits Administrator professionals. Zapier automations require a paid plan for reliable multi-step zaps. COBRA compliance ultimately remains your responsibility — this automation assists but doesn't replace review.