Requirement
The client, a global IT outsourcing company required vacation and training tracker so that staff could plan and book time off without compromising the overall levels of cover required.
Challenges
Staff were based in India and UK, so public holidays were different. The spreadsheet had to be used by up to 20 people, so had to be robust and shared.
Calendar display showing bookings and Public Holidays
Solution
The solution was an Excel spreadsheet with just four tabs. Users entered their vacation or training dates into a list, the data from which was used to populate a 6 month calendar view of all staff’s bookings. The calendar allowed the user to see where there were conflicts or other issues.
There was also a tracker that summarised the total number of days taken by each team member so that they and their manager were always aware of annual vacation planned and taken versus their entitlement.
Design Highlights
The design is clean and uncomplicated, and the user interface very simple. The solution uses two calendars – for India and UK – conditional formatting and array formulas to build the calendar display.
We’ve resisted the temptation to add code and user forms because sometimes they’re really not necessary.