Annual Leave Calendar
Introduction
This is the simplest part of the project, and we actually worked with this doc for several months before we added Slack integration when our team was small.
Our code examples are going to work off the layout in this spreadsheet but if you are familiar with Google Sheets you can easily modify the layout or create your own version.
We won’t go through the setup of this sheet step-by-step as there are many components to it.
Instead you can get a copy of the sheet’s template and add it to your own drive.
You can get a link to the template, along with all the project examples here.
This doc is saved as a Microsoft Excel .xlsx file in order to preserve some of the formatting and formulae we have added to save you time.
In the case where all employees are under the same contract, and these allocated days never change you can assign these attributes to a single cell and read from there instead of setting attributes manually for each employee.
In order to get this working in your Google Drive, you will need to first upload that sheet to your Drive and then open the sheet in Google Sheets.
Note
This tutorial was originally written for our Blog in 2023 but we wanted to dust it off and re-post it for our Knowledge Hub. You will therefore see that some of the images still contain the 2023 dates and that might also mean some of the cell references are incorrect.
When the sheet is opened you need to convert the .xlsx file to a Google Sheet.
You can do that from the “File” menu.
We want to convert the template to Google Sheets so we can make use of Google’s App Scripts for the next part of the tutorial.
You can now delete the original .xlsx file as we dont need it anymore.
You might also want to rename the sheet at this stage. Please note that this setup works for each year with minimal changes so there is no need to have a different sheet for each working year.
Instead you can add a new tab for each year.
Preparing Sheet
The first thing you will need to do is setup the sheet with the following attributes:
Correct Dates
The example sheet is already set up for 2024.
You’ll see at the top of the sheet (cell A4) we have the starting date (1st Jan) and the weekday of this date for the given year.
The weekday upon which the 1st falls on will be different from year to year so this needs to be adjusted accordingly.
Weekends
Next we want to make sure Saturday and Sunday are grayed out for each weekend.
This step is entirely visual.
The scripts we create later will not care if these rows are coloured or not.
This is to help admins who update the doc to know which dates are weekends.
Bank Holidays
Bank holidays differ between country, region and year.
The next step is to update the sheet to add the correct bank holidays for your team.
You can do this by adding the “BH” cell defined at the top of the sheet.
In our reporting script this will allow you to inform employees and management when a bank holiday is approaching.
This is very helpful as we have found that often we forget entirely there is an upcoming bank holiday and some of the staff will start work on Monday morning.
This also works in the case where there is a company holiday that is given outside of public holidays or mandatory vacation days.
Employee Names
The reporting system needs to know the name of the employee under each column.
Admins that are managing and updating these sheets also need to know which column to update for each employee.
You can set this at the top of the sheet in Row 3.
Set Correct Year
At the bottom of the page you will see the current tab we are editing.
We need to set this to the correct year.
You can keep the naming convention as it is because the script we will make in the future will recognize this format.
Each time you want to start a new year, you can copy the current tab, rename the new tab with the new year and prepare the document once more. The script will automatically know how to read the tab for the current year if everything is prepared correctly.
Note
Once your current year is prepared it might be a good idea to copy it anyway and call the tab “template”. This will allow you to copy the template in order to create a new year without having to remove all of the allocated time for each employee in order to start the new year.
Set Employee Attributes
Although this sheet is designed to do most of the calculation for you, we do need to specify some custom parameters/attributes for each employee. Specifically how many vacation days and how many sick days each employee has.
Note
In the case where all employees are under the same contract, and these allocated days never change, you can assign these attributes to a single cell and read from there instead of setting attributes manually for each employee.
To set these attributes, scroll down to the bottom of the sheet, below 31st December (cell A368).
The two attributes are “AL ALLOWANCE” and “SICK ALLOWANCE”. You can set those for each employee you have names at the top of the column.
How To Use
Now that the sheet has been prepared correctly we can go through how to log vacation and sick days.
At the top of the sheet you will see some default cells.
These cells represent the different kinds of days you can log with this vacation tracker.
Each of these examples must be copy/pasted into a cell for the day your employee wants to take. They must be copied exactly in order for the calculations to work. But there is some customization that you can do if you want, once you know how the sheet calculates each type of cell.
OOO (Out Of Office)
This cell allows admins to mark employees out of the office or otherwise not available.
This does not mean the employee using one of their annual leave days but instead, that the employee will not be reachable for this day.
This can be used for occasions where the employee is traveling or attending a conference, or where an employee works part-time.
We have found this to be very useful when a project lead or manager is absent, but other members of the team were unaware or forgot about this absence.
The morning report will show the employee as unavailable so members of their team know not to “pm” them, or that they should seek assistance elsewhere in the team while that member is away.
AL (Annual Leave)
As you might expect, this is used to track annual leave days.
Any cell/day this is assigned to for the employee will be subtracted from their allowance of AL.
Half-Day AL: (Working Which Half)
This is used where the company allows for half-days to be contributed to total AL.
Many vacation trackers do not allow for this, which means that either the company disregards half-days, or the employee unfairly loses a full day of AL when they should not have to.
We typically add the time of day the employee plans to take off, i.e. “morning” or “afternoon”.
This is useful for the daily report in order for managers and other members of the team to know at what point an employee might no longer be reachable.
Sick Day & Half-Day Sick: (Working Which Half)
These cells work the same as AL and Half-Day AL but the totals get calculated for the allowed sick-days per employee.
Requested
This is something specific to the way we manage annual leave requests internally and may not apply to your needs.
When requesting AL internally, an employee must email the management team for approval. These requests are then discussed between management and the team leads on weekly calls.
To ensure that these requests don’t get lost if some of the management team fail to provide approval, an admin can mark these days as “requested” rather than marking them as “AL” if they are still unapproved.
Other (Specify)
These fields are similar to the OOO fields. They will not count towards annual leave but an explanation should be given as to why the employee is not available or why leave has been taken.
For example, these cells could be used where an employee worked a weekend, holiday or overtime in lue of AL. In this case the admin and manager will agree on the time off, but it will not be taken from the AL.
Another example might be maternity/paternity leave, where the employee will be absent, but they are still entitled to their full AL once they return.
Example Sheet
To show these cells being used in calculations you can play around with adding these for your employee’s days.
You will have to scroll to the bottom of the sheet in order to see the calculations. You can use the last week in December as a test week.
Now that your calendar is all set up, the next step is to fill it out and we can move onto daily and weekly reporting with Google App Scripts.
In the next tutorial we will look at hooking this sheet up to Slack using Google Sheets so we can send scheduled report to Slack informing us of who might be absent for a given day or week.