As a team leader or a manager of a team, it is important to know the staff coverage available every day, especially during the holiday season.
This Google Sheet Template is developed to provide a simple and effective solution to help assess the coverage of team members’ availability during a particular month.
This Google Sheet Template can be used every month to ensure that you are aware of the exact coverage and plan accordingly.
This template is designed for Google sheets, but if you are looking for an Excel template, please visit Employee Vacation Planner
Free Google Sheets
How to Use the Employee Vacation Planner Google Sheets Template
Steps to use this template
- Make a Copy and save this template in your Google Drive.
- Configure the input entries.
- Dashboard and the Calendar are populated automatically.
Step 1: Make a Copy of the sheet.
Click on the link above and then make a copy of the sheet as shown in the screenshot below.
You can save the copy in your own google drive for your use. Once copied, it can also be shared with your colleagues to collaborate as needed.
Step 2: Configure the input entries.
This Google Sheet Template is one of the simplest templates to get started. Following are the inputs needed for this template to populate the dashboard and the calendar automatically.
- Start Date – The template builds out the calendar for 31 days beginning from the Start Date
- # of Employees needed daily – This data is used to identify understaffed days.
- Weekends – Select the weekend days of your company. These days will not be counted as working days.
- Holidays – Enter up to 7 holidays. These days will not be counted as working days.
- Vacation Dates of employees
- Enter Employee Name and the planned vacation dates.
- If an employee plans to take two periods of vacation (Jan 02 to Jan 03 and then on Jan 23), enter them as different entries in two rows.
- If an employee does not plan to take any vacation, then enter just the employee name in a row and leave the date columns blank. It is important that all employees appear at least once in the table.
- There is a limitation to add 1000 rows of employee vacation that can be captured.
Step 3: View Dashboard
Once the inputs are provided, the template automatically calculates the metrics as a quick summary.
- # of Employees
- # of Working Days
- # of UnderStaffed Days
In addition it also shows the number of employees at work for each of the 31 days. Holidays and Weekends will not display any numbers.
The Red bars indicate understaffed days. Understaffed days are days where the number of employees working is less than the number of employees needed.
Step 4: View Calendar
This Google Sheet template also has a vacation calendar that visually shows the vacation days on a calendar view. Let’s take a closer look at the top of the calendar.
Each employee is listed on the left. The holidays are shown in orange, weekends in gray and employee vacations are shown in green.
The calendar can display 31 days and 20 employees at a time.
Here is the look of the entire calendar (vertically)
A daily summary metrics is provided at the bottom of the calendar. Number of Employees on Vacation and Number of Employees at work for each day are summarized.
Now that we have the calendar and dashboard automatically updated, we can print or download as PDF. Following is a sample of the document.
