sheetsmith

Templates › Clubs and groups

Volunteer Rota

Made for anyone organising volunteers.

Keep a list of volunteers with their skills and availability, plan dated shifts, see which shifts still need someone and how many shifts each person has done. The summary carries a chart that redraws itself as you type.

Tabs
3
Columns
18
Fill themselves in
15

£7one payment, yours to keep

  • Opens in Excel, Google Sheets and Numbers
  • Delivered straight away
  • No macros, nothing to install
  • Change it free before you buy

Payment is handled by Stripe. Your card details go to them and never to us. Sales are final, which is why every tab, column and calculation is shown on this page first.

The workbook's summary and the table behind it
A row typed into the real file. Every figure that changes is the spreadsheet's own arithmetic.
The summary page, every figure calculated from the tables
The chart, drawn from the totals in the table beside it
What is inside: the tabs, the columns and what fills itself in
Where you type, and the shaded columns that work themselves out
The guide sheet that opens first and explains the file
Everything included in the download

Everything inside, before you pay

This is the whole design: every tab, every column and how each calculated one is worked out. White cells are yours to fill in; the shaded ones work themselves out and are locked so a formula cannot be overwritten by accident. There is room for 680 rows.

Volunteers

One row per volunteer: what they can do, when they are free, and a running count of their shifts. Room for 80 rows.

ColumnTypeWorked out as
Nametext
Contacttext
Main Skillchoice (list)
Other Skillstext
Availabilitychoice (list)
Activebool
Shifts DonenumberIF(ISBLANK([name]), "", COUNTIFS([Rota!volunteer], [name], [Rota!confirmed], "Yes", [Rota!date], "<="&TODAY()))
Hours DonenumberIF(ISBLANK([name]), "", SUMIFS([Rota!hours], [Rota!volunteer], [name], [Rota!confirmed], "Yes", [Rota!date], "<="&TODAY()))
Shifts Coming UpnumberIF(ISBLANK([name]), "", COUNTIFS([Rota!volunteer], [name], [Rota!date], ">"&TODAY()))

Rota

One row per shift. Leave Volunteer empty until someone is on it. Room for 600 rows.

ColumnTypeWorked out as
Datedate
Shiftchoice (list)
Rolechoice (list)
Hoursnumber
Volunteerchoice (from Volunteers › name)
Confirmedchoice (list)
StatustextIF(ISBLANK([date]), "", IF(ISBLANK([volunteer]), "Unfilled", IF([confirmed]="Declined", "Needs cover", IF([confirmed]="Yes", "Confirmed", "Awaiting reply"))))
Days AwaynumberIF(ISBLANK([date]), "", [date]-TODAY())
Notestext

Summary

The state of the rota at a glance.

MeasureWorked out as
Upcoming shifts with nobody onCOUNTIFS([Rota!status], "Unfilled", [Rota!date], ">="&TODAY())
Upcoming shifts needing cover after a declineCOUNTIFS([Rota!status], "Needs cover", [Rota!date], ">="&TODAY())
Upcoming shifts awaiting a replyCOUNTIFS([Rota!status], "Awaiting reply", [Rota!date], ">="&TODAY())
Upcoming shifts confirmedCOUNTIFS([Rota!status], "Confirmed", [Rota!date], ">="&TODAY())
Shifts in the next 7 days still unfilledCOUNTIFS([Rota!status], "Unfilled", [Rota!date], ">="&TODAY(), [Rota!date], "<="&(TODAY()+7)) + COUNTIFS([Rota!status], "Needs cover", [Rota!date], ">="&TODAY(), [Rota!date], "<="&(TODAY()+7))
Active volunteersCOUNTIF([Volunteers!active], TRUE)
Shifts done this yearCOUNTIFS([Rota!confirmed], "Yes", [Rota!date], ">="&DATE(YEAR(TODAY()),1,1), [Rota!date], "<="&TODAY())
Volunteer hours given this yearSUMIFS([Rota!hours], [Rota!confirmed], "Yes", [Rota!date], ">="&DATE(YEAR(TODAY()),1,1), [Rota!date], "<="&TODAY())
Most shifts done by one personMAX([Volunteers!shifts_done])
Active volunteers with no shifts done yetCOUNTIFS([Volunteers!active], TRUE, [Volunteers!shifts_done], 0)