Team Vacation Planner Excel Template – Support

Posted on
Summary Report for Team Vacation Planner - Understaffed Days by Month

This article will present step by step instructions on how to use the Team Vacation Planner Excel template.

 

Purpose

The template is designed to help calculate number of employees available on any day and compare with number of employees needed on that day in the company. This will help us identify if there are any days where we are understaffed. By planning ahead of time, taking into account holidays and vacations coming up, we can be better prepared.

 

 

Overview of Steps

Before using the template, it is recommended that you are familiar with Excel tables. I have put together a brief article and video about how to use Excel tables for data entry. Please read this if you are new to Excel tables.

How to use Excel tables for Data Entry

 

Assuming you know how to enter data in Excel tables, let’s move forward to the overview of steps.

  1. Enter Planning period
  2. Enter Number of Employees needed per weekday
  3. Enter Company holidays
  4. Enter List of Employees and their information
  5. Enter dates of Vacation planned by the employees
  6. View Calendar to see if there are any understaffed days
  7. View Report to see summary of understaffed days by month.

 

Detailed Step by Step Instructions

 

Step 1: Choose Planning Period

The template allows planning for up to 366 days. We can choose any start and end date, that accounts to less than 366 days.

Enter Planning Period - Enter Start Date and End Date
Enter Planning Period – Enter Start Date and End Date

 

In the example above, we have entered Jan 1st to Oct 15th as Planning period.

 

Step 2: Set Number of Employees needed by Weekday

In some companies, the number of employees needed at work can be the same every day. However, in other companies, there may be a varying need based on the day of the week.

For example, in a company that provides Customer Service 7 days a week, more customers may be calling in during Saturday/Sunday compared to other weekdays. In such a scenario, we need more employees at work during Saturday/Sunday.

This template allows you to customize to handle such scenarios that easily. For each weekday, we can set the number of employees needed.

Set Number of Employees needed per weekday
Set Number of Employees needed per weekday

 

If we enter 0 as Employees Needed, then the template assumes that as weekend day. In the above image, Saturday and Sunday will be considered as Weekends (for reporting purpose).

During weekends, employees will not be available to work.

Step 3: Enter list of company holidays

Next, we enter a list of holidays in our company.

Enter list of Holidays in company
Enter list of Holidays in company

 

Holidays are days when company does not operate. Hence, Employees Needed will be automatically set to 0. Similarly, employees will also be set as unavailable.

We can enter any number of days as holidays.

 

Step 4: Enter list of Employees & their information

We enter the information about employees in the Employees table.

Enter list of Employees, their employment dates and work schedule availability
Enter list of Employees, their employment dates and work schedule availability

 

We enter the name of employee, hire date and termination date (if the employee has left the company).

As this template is designed to be used for long planning periods, we know that new employees may join and current employees may leave midway. Also, if you have contract employees, these situations are more likely. Instead of using a new file to change available employees, you can just update the hire date and termination date where applicable, in the same file. The template  will use this information and calculate available capacity automatically.

 

We can also handle varying work schedules of employees. For example, Employee 2 and Employee 4 do not work on Saturdays , but other employees do. We can just enter No for any weekday an employee doesn’t work.

 

Step 5: Enter Dates of Vacation planned by Employees

In the Vacations sheet, we enter the vacation planned by employees.

Enter Vacation dates of Employees - Vacation Start and End Dates
Enter Vacation dates of Employees – Vacation Start and End Dates

 

This is very simple. Enter Employee name, Vacation Start and Vacation End dates.

If an employee takes only one day off, please enter same date as Start and End dates. Please do not leave the End date empty.

As the employees takes more vacation, just add new rows to this table.

There is no limit to how many vacations can be entered.

This completes our data entry steps.

 

Step 6: View Calendar to see availability by day

The Calendar sheet is fully automated.

At the top of the sheet, we see the Availability Summary.

Availability Summary - Employees Needed and Employees Available - Understaffed and Overstaffed days
Availability Summary – Employees Needed and Employees Available – Understaffed and Overstaffed days

 

It shows the following information for each day in our planning period.

  1. Day – If it is a Holiday, you will see HOL. If it is Weekend day, WKD and if it is a working day WRK
  2. Employees Needed: This shows how many employees are needed at work on that day.
    1. If the day is a holiday, then this will be set to 0.
    2. Otherwise, this will be the number of employees needed for that weekday (that we entered in Step 2 of data entry in the Settings sheet)
  3. Employees Available: This reflects how many employees are available to work on the day.
    1. If the day is a holiday, this will be set to 0.
    2. If the day is a weekend, this will be set to 0.
    3. It will take into account, the Employees’ hire statuses, Employees’ work schedules on that weekday and vacations planned by employees .
  4. Employees on Vacation: This is how many employees who are employed on that day, but have taken a vacation.
    1. If it is a holiday, this will be set to 0.
    2. If the day is a weekend, this will be set to 0.
    3. Otherwise, this will reflect the count of employees who have planned vacations for that day.
  5. Availability – Needed: This tells us whether we are understaffed or overstaffed or right-staffed.
    1. This is just the difference between Employees Available and Employees Needed. If it is negative, we are understaffed (availability is less than need). If it is positive, we are overstaffed (availability is greater than need). If it is 0, we have the right number of staff (availability equals need).
    2. We will see a red flag where there is under-staffing.
      1. We can also create a similar flag for over-staffing if needed. We have to edit the conditional formatting rules to do that.

 

By knowing which days are understaffed or overstaffed, we can take actions accordingly. If we are understaffed, we can either increase capacity by hiring more permanent or temporary employees.

 

We can also see each employee’s availability on the calendar.

Availability Calendar shows each employees availability and vacation
Availability Calendar shows each employees availability and vacation

 

  • Green indicates the employee is available to work
  • Red indicates the employee is on vacation
  • Purple indicates a holiday
  • Gray indicates the employee is not working that day (Weekend or the employee does not work on that weekday)

 

This calendar is set up to handle 50 employees for 366 days.

 

Step 7: View Report to see monthly summary of availability

The Report sheet is also fully automated. It will show the overall summary and summary of availability by month.

Summary Report for Team Vacation Planner - Understaffed Days by Month
Summary Report for Team Vacation Planner – Understaffed Days by Month

 

We can see, for each month, the number of days in month, holidays, weekends, working days and understaffed days.

% Days Understaffed = (Understaffed Days / Working Days)

This sheet is set up as print-ready. You can print and share with team. You can also export to PDF and share PDF.

 

Extending the Team Vacation Planner template

Here are some ways to extend the template and customize further.

 

How to extend the calendar for more than 50 employees?

The template is designed to handle data for 50 employees by default. The Calendar sheet displays up to 50 employees. If you need for more than 50 employees, it is very easy to extend. Let’s say we want to extend to 55 employees.

 

Unprotect the Calendar sheet with indzara as password.

Unprotect or unlock sheet using indzara as password
Unprotect or unlock sheet using indzara as password

 

Select rows 59 to 63. Right Click and choose ‘Copy’.

Select 5 rows and copy
Select 5 rows and copy

 

Right Click on row 59 (row label) and choose ‘Insert Copied Cells’

Insert the 5 rows of copied cells
Insert the 5 rows of copied cells

 

You should now see the employees extended to 55.

5 new rows will be added to calendar
5 new rows will be added to calendar

 

How to edit the red flag in the Calendar?

We see a red flag when there is understaffing. We don’t have any flag for overstaffing. If you would like to edit these rules, it is easy.

In the Calendar sheet, select cell D7.

Click on Conditional Formatting menu and choose Manage Rules.

Open Conditional Formatting Rules
Open Conditional Formatting Rules

 

Click on Edit Rule.

Conditional Formatting Rules Manager - Icon sets
Conditional Formatting Rules Manager – Icon sets

 

We can see the rule used.

Edit conditional formatting rule to modify the red flag for understaffed days
Edit conditional formatting rule to modify the red flag for understaffed days

 

You can change the icons used for the three options (+ve number, 0, -ve number).

After changing, Click OK in the dialog boxes and apply changes.

 

How can I change the colors used in the Calendar?

Click on cell D14

Click on Conditional Formatting menu and choose Manage Rules.

Conditional Formatting rules for calendar
Conditional Formatting rules for calendar

 

Click on any rule and select Edit Rule.

Change color for any rule
Change color for any rule

 

Click on Format to change color. In the image above, I have chosen the rule that applies purple color to Holidays.

After changing colors, click OK in the dialog boxes and apply changes.

 

How to remove ‘Frozen panes’ in the Calendar sheet?

In the calendar sheet, we have applied ‘Freeze Panes’ to set columns A to C, and rows 1 to 13 always visible. If this is not preferred, you can ‘unfreeze panes’ easily.

In the VIEW ribbon, choose Freeze Panes –> Unfreeze Panes.

Unfreeze Panes in Calendar sheet
Unfreeze Panes in Calendar sheet

17 thoughts on “Team Vacation Planner Excel Template – Support

  1. Hello inzara,
    Would it be possible for You to add in the template also summary of vacation dates per emploeyee as input of the beggining of the year in order to track remaining vacation dates of the staff (per person)?
    Thank You
    Josef

    1. Thanks.
      Are you referring to calculating the vacation balance of each employee given an annual quota? https://indzara.com/product/small-business-paid-time-off-manager/ template does that function.
      Can you please clarify?
      Thanks & Best wishes.

  2. Hi,

    I’m trying to use this with Google sheets but it doesn’t seem to be working right. It only shows one row for one employee on the first sheet, so just Employee 1.

    How do I add more employees? I copy the row and paste below it but it doesn’t correspond ton and populate the other sheets. Help?

    Regards,
    Miro

    1. Hello
      Thank you for using our template.
      This template is designed to work on MS Excel. Most of the features will not work on Google Sheets.
      The data is entered in the table format.

      Best wishes

  3. Can the number of days understaffed be change to percentage of employees available?

    1. In the Report, the metrics are based on days in a month. Hence calculating percentage of employees will be tricky. For example, an employee can be working one day and not on another. How can we calculate in that scenario? Can you please clarify with an example so that we can understand your request correctly?

      Thanks & Best wishes.

  4. Hi Indzara,

    In the calendar view (availability summary), can you help to enable me show this for 2 different category of resource? Example availability summary for all mechanical engineers and an availability summary for all electrical engineers. I believe you can help with this so please assist.

    What you currently have is for all resources per day but I want to be able to show this for different resource category to show which resource category if mostly understaffed per day.

    Your prompt response will be greatly appreciated.

    1. Hello
      Thanks for using the template.
      This template is based on individual employees, rather than on the category of skills.
      Best wishes

  5. In case if there are 3 separate vacations for every employee per year,
    How can I Input the periods for each employee ?
    Shall I repeat the same name 3 times to put the 3 vacations for the employee ? or there is another method to add 3 periods for every employee ?

    1. Sorry for the delayed response.

      Yes, please enter the employee name in 3 separate rows with different vacation dates.

      Please let me know if there are any questions.

      Best wishes.

      1. To come back to this question, do i also have to repeat it 3times in the calendar?

        1. Please enter only in the Vacations sheet. The calendar will automatically calculate (one row for each employee).
          Please let us know if there are any questions.
          Thanks & Best wishes.

  6. i just need a template to keep track of my employees monthly vacations .As we have policy of 11 months working and 1 month vacation .so, just need to have a track which employee goes which month depending on his/her joining date

    1. I am sorry. I don’t have a solution for that specific need.
      Best wishes.

  7. I loved the blog, I always visit but never write, I have taken great tips from excel here .. my students thank

  8. Does the template support half day durations?

    1. Sorry, it doesn’t support half day vacations.
      Best wishes.

Leave a Reply

Your email address will not be published. Required fields are marked *