The use of Google Sheets to develop a flexible rota system
During the COVID-19 pandemic it was apparent that a rota system that supported flexibility and transparency was essential for maintaining specific skill provision. This article describes a solution using the free, cloud based platform ‘Google Sheets’ that met these demands and continues to provide benefits as we move past the peak of the pandemic.
The advantages of this solution are:
- Openly editable to ensure changes in staff availability and staff required can be instantly and easily adjusted.
- Accurately reports the staff rostered for each shift, including the number of doctors competent in complex airway management.
- Allows the ability to fill shifts with locum staff when required.
- Easily expandable as new staff joined the ICU team.
- Maintains existing rota patterns to protect rest periods, less than full time trainees, and pay contracts.
- Ability to monitor hours worked, to ensure appropriate remuneration and safe working hours.
- Supports a self-rostering rota that reduces the workload on traditional rota coordinator roles.
In our case, the rota was accessible and editable by those supplied with the URL; however access can be controlled to specific users. Google Sheets allows one to control the editing rights to each cell; we only protected the formula-containing cells but further controls are possible, for example each row can be set to allow editing only by the administrator and the user to whom it belongs. To further address the consequences of mistaken editing, Google Sheets contains an editing history whereby every revision can be seen, and if needed the whole sheet reverted to an earlier version. It is also possible to set up an automatic backup, where a ‘copy’ of one’s sheet is saved at regular intervals.
The rota was designed as a table, with each column representing a 24-h period and each row a member of staff. Each cell therefore denoted a shift type for that 24-h period. The ‘Data Validation’ Tool was used to provide a list of shift types that could be assigned to each cell. ‘Conditional Formatting’ was used to provide a cell fill colour depending on the shift type (Figure 1). A custom formula (=AND(CELL=”Night”,CELL=”Shift Type”) within ‘Conditional Formatting’ highlights two incompatible shifts (Figure 2). In addition to our regular staff, a number of rows were dedicated to locums and other staff that joined during the expansion of ICU services. The result was a spreadsheet displaying a master rota that was easy to read and quick to access.
Given the need to maintain safe staffing levels, facilitate staffing expansion and promote rest, a way of closely monitoring staffing levels was mandatory. Figure 3 demonstrates a separate section below the rota, using the same columns for each 24-h period but each row assigned to a metric of staffing (e.g. total day/night staff, airway day/night). Variations of Formula 1 were used to provide a value for each metric across each 24-h period. Conditional formatting was used to highlight metrics that did not meet, or only just met minimum staffing requirements, with a traffic light-based colourway. As the number of required staff increased with ICU expansion, we could adjust the formatting, thus visually demonstrating which shifts required more or less staff. Staff were encouraged to adjust their shifts if they could cover another shift type instead.
Given the size of the rota it could be difficult to see who was rostered on each day. To aid this Formula 2 was used to produce a list of staff working on each shift that automatically changed with updates to the rota. This was placed below the rota requirements sections, and used the same columns for each 24-h period (Figure 4).
Additional sheets were added to record annual leave requests and extra hours records, so that individuals could record any overtime worked. A final sheet then recorded weekly and monthly time sheets using a combination of further ‘COUNTIF’ functions to summate the number of shift types worked over the column range of the defined time period (Figure 5). A ‘SUM’ formula could then produce the number of hours worked in that week. The additional hours function was included in this total to ensure that working hours were monitored accurately.
This is an example of an easily developed, adaptable and transparent rota system that was of particular use during the COVID-19 pandemic. A live and multi-user editable platform allowed the coordination of a large number of junior staff in a dynamic environment and facilitated hours monitoring, enforcement of rest periods, and support of less than full time trainees. For those with further interest a template rota is available.
Oliver McKinney
Ex-Junior Clinical Fellow in Intensive Care
Jennifer Price
Consultant in Intensive Care and Anaesthesia
Royal Free Hospital, London
Editorial disclaimer. Publication of this article does not imply endorsement by the Association of Anaesthetists. Readers should validate the rota system with their local HR department before relying upon it, and ensure GDPR compliance by ensuring that users consent to any personal data being included in the published rota.
Figure 1. Main rota view
Figure 2. Shift incompatibility
Figure 3. Rota requirements
Figure 4. Alternative rota view
Figure 5. Four-week hours record
Formula 1
‘=COUNTIF(COLUMN,"Shift Type 1")+COUNTIF(COLUMN,"Shift Type 2")+COUNTIF(COLUMN,"Shift Type 3")
This formula counts values within the specified column range only IF they are the same as the text in green.
Formula 2
=IFERROR(FILTER(COLUMN 1,COLUMNX=Cell))
The filter function looks at Column X (each 24hr period with the shift types recorded) for text matching the text in another cell (in this case the referenced cell would contain the shift type – Long Day, Short Day E.T.C). If it finds a cell in the specified 24 hr column that contains the shift type in the formula it returns the value in the corresponding cell from Column 1 (the list of names included in the rota).It therefore produces a list of the individuals rostered for each shift type in any 24 hr period. The IFERROR then ensures that if no individual is rostered for a shift, the cell is left blank.