Lets try and visualize what the elements of a simple holiday tracker would be - built entirely on google sheets without any other tools or scripting involved - once we lay out the elements of the system we can then also see what possible enhancements could be added to the system and what that would mean in terms of effort
Lets see how we want to structure our data on the sheet
The first thing would be a list of employees and their opening leave balances which we maintain in one tab
Next in another tab we lay out our list of holidays which we want to incorporate into a system - keep in mind that the list of holidays can change meaning there can be additions and deletions from the list
Next you capture all data related to vacation requests from your employees into one tab - you take in the start and the end date for the vacation request, record whether it was approved or denied and have any additional notes along with it - feel free to suggest any other data points that you feel might be valuable to capture
Once all this is setup, we can structure a dynamic calendar view to see the calendar for each employee - we can use conditional formatting options to mark out holidays and days on leave - for example a simple red and green color scheme, red for holidays and green for vacations taken
The tracker would essentially color out dates or range of dates in green if those have been taken as vacation days by a particular employee - this can be customized via formulas
Possible enhancements
- Integrating this google sheet based calendar tracker with your google calendar - this would involve some work in Google apps script which is going to create calendar events for holidays and vacation days taken off
- Capturing vacation requests using a form - instead of manually entering requests into a sheet you can use Sheetgo forms to allow vacation requests to come into the sheet via form submissions by employees
- Notification when a vacation request is approved or denied - you can use the Sheetgo email automation to send out a notification to an employee when their vacation request has been approved or denied
