Weekly Equipment Calibration and Rental Return Digest: One Monday Email Before the Field Day

What This Builds

Calibration dates and rental returns tend to be tracked by memory, and the gap shows up on the morning of a field day. This build puts both on one sheet. You type each instrument's last calibration date and its interval, plus the return date for anything rented, and every Monday one email lists the calibrations coming due, the calibrations overdue, the items with a missing date and the rentals due back or already late. The outcome it serves is simple: no equipment surprise on the morning of the field day.

Prerequisites

• A new Google spreadsheet named "Equipment Calibration and Returns", separate from any other tracker
• A Zapier account on a paid plan, because the Zap has three steps and the free plan allows two-step Zaps only. The plan is Professional ($29.99/month). That is the total ongoing cost, since the Google account is one you already have.
• A Google account connected to Zapier for Sheets and Gmail
• Each calibration interval, taken from the manufacturer's manual or your firm's procedure
• A check of your firm's policy, and of what the client allows, on equipment codes in an outside automation tool

The Concept

Think of the sticker on a fire extinguisher. The sticker does not decide how often the check happens. It holds the date, and somebody else's rule sets the interval. Your Equipment tab is a sticker for every instrument you use. You type the interval from the manufacturer's manual or your firm's procedure, and the sheet does the date arithmetic. This guide states no calibration rule.

Field checks that the manufacturer calls for before each use are logged in the field, not in this digest. This sheet tracks dates that are weeks apart, not the checks done on site.

A Zapier lookup that finds no row halts the Zap, Zap History shows the run as safely halted, and no email is sent. So the Zap never searches the Equipment tab. It looks up one Summary row that always exists, filled by formulas. A quiet week produces an email that says none and zero. There is no AI step, no loop, no second lookup and no formula in Zapier, and every date and count is computed by the sheet.

Data caution. The sheet holds equipment codes and dates only. Client names, site names and addresses, project details and results stay in the firm's approved systems. Equipment codes are internal, but Zapier is an outside tool, so check whether your firm and the client allow even codes to pass through it, and check Zapier's own pages for how long Zap History is kept. If the answer is no, use the sheet as a manual Monday checklist and skip the Zap.

Build It Step by Step

Part 1: Build the Equipment tab

Name the first tab Equipment. Lay out the columns first.

Format columns B, D and E as dates. Add the dropdown to F2 through F301 (Insert, then Dropdown, or Data, then Data validation, with the list yes and no). Menu names change, so look for the closest match.

D2, filled down to row 301:

=IF(OR(A2="",B2="",C2=""),"",B2+C2)

G2, filled down to row 301:

=IF(A2="","",IF(OR(B2="",C2=""),"MISSING DATE",IF(D2<TODAY(),"CAL OVERDUE",IF(D2-TODAY()<=Summary!$B$4,"CAL DUE SOON",""))))

H2, filled down to row 301:

=IF(OR(A2="",E2="",F2="yes"),"",IF(E2<TODAY(),"RETURN OVERDUE",IF(E2-TODAY()<=Summary!$B$4,"RETURN SOON","")))

Read the calibration flag from the outside in:

• A blank row (no equipment code) returns blank.
• An item with a code that lacks a last calibration date or an interval returns MISSING DATE.
• A next calibration before today returns CAL OVERDUE.
• A next calibration today or later, and no more than Look ahead days away, returns CAL DUE SOON. A date of today is 0 days away and counts.
• Everything else returns blank.

Read the return flag the same way:

• A blank row, a blank return date (owned equipment) or Returned = yes returns blank.
• A return date before today returns RETURN OVERDUE.
• A return date today or later, and no more than Look ahead days away, returns RETURN SOON.
• Everything else returns blank.

The calibration flag has four IF functions and ends in four closing parentheses. The return flag has three IF functions and ends in three. G2 tests for the missing input before it reads D2, so a blank D2 is never compared with TODAY().

Part 2: Build the Summary tab

Add a second tab named Summary.

B2:

=COUNTIF(Equipment!G2:G301,"CAL DUE SOON")

C2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Equipment!A2:A301&" ("&TEXT(Equipment!D2:D301,"yyyy-mm-dd")&")",Equipment!G2:G301="CAL DUE SOON")),"none")

D2:

=COUNTIF(Equipment!G2:G301,"CAL OVERDUE")

E2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Equipment!A2:A301&" ("&TEXT(Equipment!D2:D301,"yyyy-mm-dd")&")",Equipment!G2:G301="CAL OVERDUE")),"none")

F2:

=COUNTIF(Equipment!G2:G301,"MISSING DATE")

G2:

=COUNTIF(Equipment!H2:H301,"RETURN*")

H2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Equipment!A2:A301&" "&Equipment!H2:H301&" ("&TEXT(Equipment!E2:E301,"yyyy-mm-dd")&")",Equipment!H2:H301<>"")),"none")

Check the parentheses in each formula. Every range in one FILTER runs from row 2 to row 301, so the heights match. The dates sit inside TEXT() so they join as 2026-10-20 and not as a serial number. IFERROR turns the empty-result error into the word none. The star in "RETURN*" matches both RETURN SOON and RETURN OVERDUE. The counts and the H2 filter look for exact words or non-blank flags, so rows filled down past your last item, which return blank, are never counted.

The Summary tab on this sheet has its own Key row. Do not copy this layout into another digest's sheet, because each digest has its own columns.

The look-ahead value of 7 is a starting choice. The window is yours to set. Keep Calculation Mode on Automatic (File, Settings, Calculation). Google's calculation settings page offers Automatic and Manual only, with no scheduled recalculation, and TODAY() depends on recalculation. If a count looks stale, open the sheet before relying on the email. The spreadsheet's time zone (File, Settings, General) also decides what TODAY() means.

Part 3: Load the test rows

The rows below are invented. They assume a pretend TODAY() of Monday 2026-10-19 and B4 = 7. On another day, keep the same offsets from your real today. The intervals are invented to exercise the formulas and say nothing about any real calibration schedule.

The Summary row should then read:

EQ-02 shows the boundary: a next calibration exactly 7 days out is inside a 7-day window. EQ-06 shows one item carrying both a calibration flag and a return flag. EQ-08 shows that Returned = yes clears a return date long past. EQ-05, an item with no last calibration date, is counted once under Missing Date and nowhere else.

Part 4: Create the Zap

1. In Zapier, create a new Zap.
2. Choose Schedule by Zapier as the trigger and the Every Week event.
3. Choose Monday as the day (the field is labeled Day of the Week, or similar) and set the time of day for the email.
4. Add an action. Choose Google Sheets and the Lookup Spreadsheet Row event.
5. Pick the spreadsheet Equipment Calibration and Returns and the worksheet Summary.
6. For the lookup column choose Key (column A). Labels may be worded a little differently.
7. For the lookup value type summary.
8. Leave the other optional settings at their defaults.
9. Add a second action. Choose Gmail and the Send Email event.
10. In To, type your own address only. The digest goes to you and no one else.
11. Give it a subject such as Equipment this week.
12. Type the body below, replacing each bracketed name with the field of that name from the Google Sheets step.

Calibration due soon [Cal Due Soon Count]: [Cal Due Soon Items]
Calibration overdue [Cal Overdue Count]: [Cal Overdue Items]
Missing dates: [Missing Date Count]
Returns [Return Count]: [Return Items]

1. Test the Google Sheets step. It should return the whole Summary row.
2. Test the Gmail step and read the email.
3. Turn the Zap on.

The lookup returns the entire matching row, and that row is all Zapier sees. The Equipment tab never leaves Google.

What leaves your sheet

Zapier receives the Summary row and the email text. That means the word summary, the Cal Due Soon Count, the Cal Due Soon Items text, the Cal Overdue Count, the Cal Overdue Items text, the Missing Date Count, the Return Count and the Return Items text (equipment codes, flag words and dates). Zap History stores the data each step received and returned, so those values stay in your Zapier account. The last calibration dates, the intervals and the Returned flags stay in the Equipment tab. Check Zapier's own pages for how long Zap History is kept.

Use codes only. A client name, site name or address, project detail or result value does not belong in this sheet.

Zapier bills only successful action steps as tasks. The trigger, a search that halts and a failed action are not counted, so a normal run uses two tasks.

Real Example: Monday 2026-10-19

Setup: The eight invented rows from Part 3 and B4 set to 7. Input: At the scheduled hour on Monday the Zap fires and finds the summary row. Output:

Calibration due soon 3: EQ-01 (2026-10-20), EQ-02 (2026-10-26), EQ-06 (2026-10-19)
Calibration overdue 1: EQ-04 (2026-10-15)
Missing dates: 1
Returns 2: EQ-06 RETURN SOON (2026-10-23), EQ-07 RETURN OVERDUE (2026-10-16)

Check it by hand. EQ-01, EQ-02 and EQ-06 fall 1, 7 and 0 days out. EQ-04 was due 4 days ago. EQ-05 has no date. EQ-06 is back on 2026-10-23, 4 days out, and EQ-07 was due back 3 days ago. EQ-03 and EQ-08 stay off the lists. What you do next: You arrange calibration for EQ-04 before the next field day, return EQ-07 or ask the rental company about it, and find a date for EQ-05 from the manual or the firm's procedure. The sample is invented. Your real intervals come from the manufacturer or your firm.

What to Do When It Breaks

• A Monday passed with no email → This failure is silent, so build the habit of noticing it. Check that the Zap is switched on. Open Zap History and look for a run that Monday. A "safely halted" run means the lookup found no row, so check that A2 on the Summary tab still says summary, because a renamed Key cell stops the lookup. Check that the spreadsheet and the Summary tab still have their original names. If Zapier reports an expired Google connection, reconnect it.
• A rental never appears in the returns list → Confirm column E holds a real date and not text, and that Returned is not yes. A blank return date means owned equipment, so it is never flagged.
• Owned equipment shows a return flag → A stray date is in column E. Clear it.
• Next calibration shows blank for a row you filled → One of A, B or C is empty. The formula waits for all three.
• Dates show as numbers in the email → The TEXT wrapper was removed from the C2, E2 or H2 formula. Restore it from Part 2.
• Counts look stale → Open the sheet and keep Calculation Mode on Automatic.

Variations

• Simpler version: Drop the rental columns and the last two Summary columns, and track calibration only.
• Extended version: Raise the aim from dates to readiness. Add a Needed for column that holds the next field event code, and use it to decide which flagged item to deal with first. The formulas do not read it.

What to Do Next

• This week: Run the test rows. Then replace them with your live equipment.
• This month: Check each interval against the manufacturer's manual or your firm's procedure and note the source in a spare column.
• Advanced: Pair it with the Weekly Monitoring Event Schedule Digest, each in its own spreadsheet.

Advanced guide for environmental scientist professionals. This build needs a paid Zapier plan. Zapier and Google update their screens often, so if a label differs from this guide, look for the closest match and check the vendor's help pages.