Visit window tracker spreadsheet (free Excel and Google Sheets template)

Q: Why do my windows shift when I type an actual date?

Because the chain question on Setup is TRUE. In that mode each visit counts forward from the previous visit's actual date, so Week 4 follows the real Week 2, not the planned one. That is right for a protocol that says Cycle 2 Day 1 is 21 days after the actual Cycle 1 Day 1. If yours fixes every visit to the randomization date, set it to FALSE: the windows stay put, a late visit shows as done out of window, and nothing downstream moves.

Q: Day 0 or Day 1, which does the sheet assume?

Day 0 by default: the anchor is study day 0 and Week 2 is day 14. If your protocol has no Day 0, set the Day-1 question to TRUE and type the protocol's own numbers (Day 1, Day 15, Day 29). The sheet then subtracts one calendar day from every positive study day, only where a visit counts from the anchor; chained visits use plain day differences and need no shift. Screening days stay negative under both conventions. The tell is the schedule of assessments: if the first column after baseline is Day 2, it is a Day-1 protocol.

Q: Calendar days or business days?

Calendar days. Protocols count calendar days unless they say otherwise, so a window that closes on a Sunday closes on that Sunday. The sheet does not skip weekends or holidays; if a target lands on a Saturday, the visit gets scheduled somewhere inside the window, not moved past it. If your protocol genuinely counts working days, Opens and Closes need WORKDAY instead of plain arithmetic, a change to make deliberately with the protocol page open.

Q: Can I put several studies in one file?

Yes, one tab set per study. In Google Sheets, duplicate Setup, Tracker and Summary, rename them (Setup ABC-301, Tracker ABC-301), and fix the sheet names inside the copied formulas with find and replace. In Excel, copying the file is simpler. Either way each Summary tab sees only its own study, so a morning check across six studies is six tabs. That holds up to about six, which is where I stopped trusting my own spreadsheets.

Q: Is this a source record?

No. It is a tracker. The CRF and the chart are the record, and the protocol, its amendments and your site's SOPs govern what counts as in window. A visit showing done out of window here is a prompt to open the protocol and the deviation SOP, not a deviation report in itself. Print it if you like a paper list on the desk; do not file it as source. The same line is on the Read me tab and in the print footer.

This is a visit window tracker you can download and use today, with every formula written out below so you can check it, change it, or paste it into a sheet you already have. Type the protocol's schedule once on the Setup tab and each subject's ID and anchor date on the Tracker tab. Every visit gets a target date, a window-open date, a window-close date, and a status against today: upcoming, opens soon, in window, overdue, done, done out of window, or missed. Type the actual date when a visit happens and the status turns green. Turn chaining on and the next window recomputes from that real date. It is a tracker, not the source record, and it holds nothing but subject IDs and dates.

Download the Excel file (47 KB; opens in Excel, Google Sheets and Numbers; no email, no sign-up).

A Google Sheets copy link is coming; the Excel file opens in Sheets via File → Import in the meantime.

Four tabs. Read me is the how-to. Setup holds the anchor event's name, two TRUE/FALSE questions (Day 1 or Day 0; chain or not), the "opens soon" lead time, and the schedule table, prefilled with a sample from Screening at −14 to Safety Follow-up. Tracker is one row per subject, six columns per visit: Target, Opens, Closes, Actual, Status, Days left. Summary counts each state per visit.

How the formulas work

Everything below is the Week 2 block for the first subject, row 4. Week 2 is Setup row 13 (study day B13, window before C13, window after D13); Baseline, the visit before it, is Setup row 12. The subject's screening date is B4, the anchor date C4. Week 2's columns are P Target, Q Opens, R Closes, S Actual, T Status, U Days left; the Baseline block before it has its Target in J4 and Actual in M4. Setup B4 is the Day-1 question, B5 the chain question, B6 the lead days, B7 the earliest study day (=MIN(B11:B22)).

Column	Formula	In plain words
Target (`P4`)	`=IF(Setup!$B$13="","",IF(AND(Setup!$B$5,Setup!$B$12<>"",Setup!$B$12>=0,Setup!$B$13>Setup!$B$12),IF(OR(M4="",M4="missed"),IF(J4="","",J4+(Setup!$B$13-Setup!$B$12)),M4+(Setup!$B$13-Setup!$B$12)),IF($C4="",IF(Setup!$B$13<0,IF($B4="","",$B4+Setup!$B$13-Setup!$B$7),""),$C4+Setup!$B$13-IF(AND(Setup!$B$4,Setup!$B$13>0),1,0))))`	Three pieces, explained below.
Opens (`Q4`)	`=IF(P4="","",P4-Setup!$C$13)`	Target minus the window-before days. Blank if there is no target yet.
Closes (`R4`)	`=IF(P4="","",P4+Setup!$D$13)`	Target plus the window-after days.
Actual (`S4`)	typed	The one cell you touch per visit. A date, or the word `missed`.
Status (`T4`)	`=IF(P4="","",IF(S4="",IF(TODAY()>R4,"Overdue",IF(TODAY()>=Q4,"In window",IF(Q4-TODAY()<=Setup!$B$6,"Opens soon","Upcoming"))),IF(S4="missed","Missed",IF(OR(S4<Q4,S4>R4),"Done - out of window","Done"))))`	No actual date: already closed is Overdue, open today is In window, opens within your lead days is Opens soon, otherwise Upcoming. An actual date inside the window is Done, outside it is Done - out of window. The word missed is Missed.
Days left (`U4`)	`=IF(OR(P4="",S4<>""),"",R4-TODAY())`	Days until the window closes. Negative means it closed. Blank once done.
Summary count	`=COUNTIF(Tracker!$T$4:$T$33,"In window")`	One per state per visit.
Closing within N days	`=COUNTIF(Tracker!$U$4:$U$33,"<="&$B$3)-COUNTIF(Tracker!$U$4:$U$33,"<0")`	Windows with 0 to N days left and no actual date.

The Target formula is three pieces, tried in order.

1. No study day on Setup row 13?            -> blank
2. Chain is TRUE, Baseline is at or after
   the anchor, and Week 2 comes after it?   -> (Baseline actual, or its target if
                                               there is no actual yet) + (14 - 0)
3. Otherwise, from the anchor:
     anchor blank and study day < 0         -> screening date + day - earliest day
     anchor blank and study day >= 0        -> blank
     anchor filled                          -> anchor + day, minus 1 if Day-1 and day > 0

With real dates: Subject 003 was randomized 2026-08-10. Week 2 is day 14, ±3, so the target is 2026-08-24, the window opens 2026-08-21 and closes 2026-08-27. The visit happened 2026-08-29, so Status reads Done - out of window. Chaining off, Week 4 stays at 2026-09-07. Chaining on, Week 4 becomes 2026-08-29 + 14 = 2026-09-12 and Week 8 follows from there. Which is right is the protocol's call, and it is the whole subject of the missed visit: which date do you count from now?

Piece 3's screening branch lets a subject exist before randomization. Subject 002 was consented 2026-09-01 with no anchor date yet, so Screening (day −14, the earliest in the schedule) lands on 2026-09-01 with a real window, and every post-anchor block stays blank until the anchor date is typed.

The formulas use only IF, AND, OR, TODAY, COUNTIF, MIN and SUM, so they work unchanged in Excel, Google Sheets and Numbers. No named ranges, no LET, no FILTER.

How to set it up for your protocol

On Setup, type the anchor event's name (Randomization, First dose, Baseline). The Tracker header uses that word, never "Day 0".
Answer the Day-1 question. If the schedule of assessments reads Day 1, Day 8, Day 15 with no Day 0, set it TRUE: every positive study day shifts back one calendar day, once, at the anchor. Get it wrong and every window is a day off, which on a ±2 window is a deviation. The reasoning is in Day 0 vs Day 1.
Answer the chain question. FALSE keeps every window on the anchor date, which is what most parallel-group protocols mean. TRUE re-anchors each visit to the previous visit's actual date, which is what cycle-based protocols usually mean. Read the protocol's visit-window section before choosing.
Replace the sample schedule with the protocol's visits, in order, with its own day numbers and windows. Negative days for screening.
On Tracker, delete the three example subjects and type your own: ID, screening date, anchor date. Type actual dates as they happen.
One study per file. Copy the file for the next study, or in Google Sheets duplicate the tabs and rename them.

What this sheet won't do

It will not remind you. It only knows what day it is when you open it, so a window that closed on Friday says so on Monday. It will not carry the visit's procedures, who is delegated to do them, or how your site does them; it says when, not what. It will not merge several studies into one view, so six studies is six files every morning. It keeps no history of who changed a date. And if a Target cell ever shows a typed date instead of a formula, someone typed over it, and everything to its right is wrong.

Questions I get about it

Why do my windows shift when I type an actual date?

Because the chain question on Setup is TRUE. In that mode each visit counts forward from the previous visit's actual date, so Week 4 follows the real Week 2, not the planned one. That is right for a protocol that says Cycle 2 Day 1 is 21 days after the actual Cycle 1 Day 1. If yours fixes every visit to the randomization date, set it to FALSE: the windows stay put, a late visit shows as done out of window, and nothing downstream moves.

Day 0 or Day 1, which does the sheet assume?

Day 0 by default: the anchor is study day 0 and Week 2 is day 14. If your protocol has no Day 0, set the Day-1 question to TRUE and type the protocol's own numbers (Day 1, Day 15, Day 29). The sheet then subtracts one calendar day from every positive study day, only where a visit counts from the anchor; chained visits use plain day differences and need no shift. Screening days stay negative under both conventions. The tell is the schedule of assessments: if the first column after baseline is Day 2, it is a Day-1 protocol.

Calendar days or business days?

Calendar days. Protocols count calendar days unless they say otherwise, so a window that closes on a Sunday closes on that Sunday. The sheet does not skip weekends or holidays; if a target lands on a Saturday, the visit gets scheduled somewhere inside the window, not moved past it. If your protocol genuinely counts working days, Opens and Closes need WORKDAY instead of plain arithmetic, a change to make deliberately with the protocol page open.

Can I put several studies in one file?

Yes, one tab set per study. In Google Sheets, duplicate Setup, Tracker and Summary, rename them (Setup ABC-301, Tracker ABC-301), and fix the sheet names inside the copied formulas with find and replace. In Excel, copying the file is simpler. Either way each Summary tab sees only its own study, so a morning check across six studies is six tabs. That holds up to about six, which is where I stopped trusting my own spreadsheets.

Is this a source record?

No. It is a tracker. The CRF and the chart are the record, and the protocol, its amendments and your site's SOPs govern what counts as in window. A visit showing done out of window here is a prompt to open the protocol and the deviation SOP, not a deviation report in itself. Print it if you like a paper list on the desk; do not file it as source. The same line is on the Read me tab and in the print footer.

I made this sheet because it's the one I wanted at my first site. It will carry you a good way. When the tabs multiply and you're the only one who knows why column Q went red, CRC Run Sheet keeps the same windows without the formulas, and prints a sheet for each visit. First study is free, no card. Subject IDs and dates only, same as this file.

This is a tracker, not the source record. The protocol, its amendments and your site's SOPs govern.