SIT Library Use Case: Roster Smarter

Roster creation is part of library operations, whether for manning a service desk, conducting information literacy classes or library tours, complicated by the need to account for operations requirements, staff leave requests, and even staff personalities.

While there are a host of roster planning tools on the market, small libraries like Singapore Institute of Technology’s (SIT) find it hard to justify their cost. Instead, it uses Microsoft Excel to generate rosters using a few simple formulas. However, SIT library enhanced this process with an initiative to automate Microsoft Outlook calendar entries from the Excel roster to block out rostered duties.

Before Automation

Previously, the library’s Roster Planner would create the monthly roster with Microsoft Excel taking into account different resources permutations, then notify their nine staff via email and update the schedule on a shared Microsoft Outlook calendar for staff to individually copy over onto their own Outlook calendar. The process was lengthy and took three hours, not including time taken for each staff member to update their own calendar. Shift swops also required verbal negotiations, followed by another manual update by the Roster Planner and the staff. This process increased the risk of missed shifts or time clashes due to human error or missing entries in the Outlook calendar.

Image 1. SIT Library’s Roster for September 2020.
Image 2. SIT Library’s Roster Planner manually inputted the roster into library staff’s shared calendar for colleagues to refer to.

After Automation and How it Works

To make Roster planning less tedious for staff involved, SIT Library implemented a new process to automate appointment setting and staff notification. The new process also allowed staff to swop shifts on their own without involving the Roster Planner, and update the shared calendar at the same time. The fix is so quick and easy that SIT Library has shared the process below for other colleagues to try: 

Step 1. Create a Microsoft Excel template (referred to as “Template_A”) for the Roster Planner’s inputs.

Image 3. Create Template A to begin planning your library’s roster.

Step 2. Convert the grid table to a list table, making it easy to use in macro, using functions like INDEX and TIME to transfer the data from Template_A to List_A.  

Image 4. List_A has all the data from Template_A.

Step 3: Although there are many ways to use macros to automatically generate calendar appointment from a Microsoft Excel list, SIT Library adapted scripts from Slipstick Systems to  populate List_A.

Step 4: The macro triggers an appointment from staff’s default login account mailbox. Within the default calendar, SIT Library appended the text “.Folders(“Roster”)” (in bold and italics) to the script to create appointments from the specified sub-calendar:

<Set CalFolder = lNs.GetDefaultFolder(olFolderCalendar).Folders(“Roster”)>

A control button was also added for ease of use.

Image 5. Control button to assign the macro.

Step 5: To prevent accidental activation, another script was added to the macro:  

<If MsgBox(“Run the job?”, vbYesNo) = vbNo Then Exit Sub>

Image 6. Just in case, this script stops accidental activation.

Step 6: Additional cells were added to Template_A and List_A to cater for new scenarios like additional shifts, new staff or changes to shift durations, making it easier for staff to maintain and use the template without editing the macro.

Contributed by:
Tan Leh Leh
Library Specialist
SIT Library