A volunteer roster spreadsheet that catches gaps and double booking

A filled shift is not always a covered shift

A volunteer can appear on two shifts at once while both headcounts look fine. This roster shows the bookings behind the counts, flags overlapping confirmed assignments and separates a tentative offer from confirmed cover.

It is a practical social service technology task: a coordinator can build the template, inspect the example and use the same checks before the next programme session. Unlike a list of administrative automation opportunities, this guide supplies the actual inputs, formulas and expected output.

Use an organisation-approved workspace and volunteer codes rather than names or contacts in this starter. Staff approve role suitability, required headcounts and availability before confirming an assignment. The sheet only checks recorded numbers and times; it does not select suitable people, contact volunteers or send messages.

Create three tabs with separate jobs

Name the tabs exactly Shifts, Assignments and Summary. In Shifts, A:E are ShiftID, Start, End, Role and Required. Add Confirmed, Gap and Check in F:H. Each ShiftID must be unique; create a separate shift for each role and time slot.

In Assignments, A:C are ShiftID, VolunteerID and Status. Add Start, End, Conflict and Duplicate in D:G. Each row is one volunteer assigned to one shift. Use Confirmed only after a coordinator has checked availability and approved the role; use Pending for a proposed assignment and Cancelled for a withdrawn one. Cancelled rows remain visible but do not contribute cover.

Synthetic demonstration follows: every shift, role label and volunteer code below is invented, not real agency or personal data, a model output or an observed result. Welcome desk and Activity support are example staff-approved roles for this scenario, not recommendations about any volunteer's suitability.

Load the inputs and check the time cells

Import each CSV into its named tab starting at A1. If pasted text stays in one column, split it using commas. Format Shifts!B2:C5 as date and time. Do not type just a clock time: storing the date prevents a booking tomorrow being compared as if it were today.

Test =ISNUMBER(Shifts!B2) and =ISNUMBER(Shifts!C2) in unused cells. Both must be TRUE. If dates imported as text, replace B2 with =DATE(2026,10,19)+TIME(9,0,0) and C2 with =DATE(2026,10,19)+TIME(11,0,0) to establish the intended format, then correct the other rows. Every End must be later than its Start. Split an overnight booking into dated intervals rather than using an end time earlier than the start.

Select Assignments!A2:A1000 and use Data → Data validation → Add rule → Dropdown from a range, with Shifts!A2:A5 as the source.[3] Extend that source when adding shifts. For C2:C1000, create a single-selection Dropdown containing Confirmed, Pending and Cancelled, with invalid values rejected.[3] Never use a multi-select status.

Bring shift times into each assignment

Enter the following formulas in Assignments!D2 and E2, then fill both down to row 1000. They use the ShiftID to fetch the times. VLOOKUP searches the first column of a range; FALSE requests an exact match rather than an approximate match.[2] A missing shift therefore produces a visible error instead of silently borrowing another shift's time.

Keep these columns as formulas. Changing a shift's time should change every linked assignment, not leave several manually typed versions of the same booking. Before approving a roster, check for blank volunteer codes, invalid times, unknown shifts and formula errors.

Flag overlaps and duplicate assignments

Put the next formulas in F2 and G2 and fill them down to row 1000. COUNTIFS counts rows matching multiple criteria.[1] The overlap check compares confirmed assignments for the same volunteer. Two intervals overlap when the other start is earlier than this end and the other end is later than this start.

The current row meets those conditions itself, so the test looks for more than one match. CHAR(60) supplies the less-than sign in the start-time criterion. Strict comparisons mean a shift ending at 11:00 does not conflict with one starting at 11:00. Travel or handover time is not included: if a buffer is required, staff must put it into the recorded interval or check it separately.

The duplicate check catches the same volunteer confirmed twice on one ShiftID. It flags both copies, because retaining either copy is a source-record decision. Pending and Cancelled bookings do not trigger these confirmed-booking checks.

Calculate provisional coverage and the review queue

Enter the next formulas in Shifts!F2:H2 and fill them down through row 1000. Confirmed counts assignment rows, Gap is the shortfall against Required, and Check is the action label. A conflict or duplicate takes priority over a reassuring headcount and produces Review.

Review does not mean the gap has been solved. The headcount still includes that problematic booking, so both Confirmed and Gap are provisional until staff correct it. Without a conflict or duplicate, the labels are Gap, Over or Covered. Over means confirmed assignments exceed the agreed requirement; it asks the coordinator to inspect the plan, not dismiss someone automatically.

Check every row against the worked result

The supplied inputs produce the following complete shift summary. S01 and S02 need review because V001 appears in overlapping confirmed intervals. S03 is clean: V002 finishes S01 exactly when S03 starts. S04 has no confirmed cover because its two entries are Cancelled and Pending.

These expected values were independently calculated from the CSVs, and the common formulas were executed in LibreOffice. They have not been exercised in a signed-in Google Sheets session; compare your pasted results with this sample before using the file for operational scheduling.

Add a compact roster-wide summary

In Summary, put Metric and Value in A1:B1. Type the metric labels from the table below into A2:A9, then enter the matching formulas in B2:B9. Required and Confirmed are shift-assignment slots, not distinct volunteers. Conflict rows count both sides of an overlap, not the number of people involved.

Use it before confirming the next roster

Each day, enter proposed assignments as Pending, check availability through your normal coordination process and only then mark approved bookings Confirmed. Look first at errors and Review, then Gap and Over. Do not approve the whole roster simply because the sum of Confirmed looks large enough.

For visible flags, select Assignments!A2:G1000, choose Format → Conditional formatting → Custom formula is and use =OR($F2="CONFLICT",$G2="DUPLICATE").[4] On Shifts!A2:H1000, use =OR($H2="Review",$H2="Gap",$H2="Over"). These rules read their own tab's results; the text labels still carry the meaning when colour is unavailable.

To repair the sample, replace V001 on S02 with another staff-approved, available volunteer code such as synthetic V006, then confirm V003 for S02 and V005 for S04 after approval. The repaired example should show every shift Covered, zero gap slots and zero conflict rows. This is a test edit, not an automatic allocation rule.

Troubleshooting and common questions

Why is a full shift marked Review? A confirmed booking has a conflict or duplicate. Fix the assignment, not the label. Copying a confirmed assignment into another row should flag DUPLICATE and CONFLICT on both copies; delete the test afterward.

Why do lookup cells show an error? Check the ShiftID exists once in Shifts. Do not hide the error with a blank fallback. A missing time makes overlap checks unreliable.

Why did a new row not count? These ranges stop at row 1000. Extend all formulas, dropdown rules and formatting together before exceeding that limit. If formulas fail to parse, check straight quotation marks and your locale's formula separator; these examples use commas.

Can we store personal contacts or message people? Neither is needed here. Use the approved volunteer register and communication process separately. This workbook has no messaging step.

Does Covered mean ready to run? It means the recorded confirmed headcount meets Required without these detected conflicts. Staff must still check role approval, valid times, travel needs and whether the volunteers have agreed to attend.