Employee Vacation Planner

• Version
• 26'205 Downloads
• 2.1 Mb File Size
• January 4, 2022 Updated
• 0 Number of comments
• ★★★★★

  ★★★★★
  Rating

This is an Employee Vacation Planner template you can use to plan and track taken vacation by your employee. Setting up, planning and tracking are done in Vacation Tracker worksheet while seeing monthly summary can be done in Vacation Summary worksheet.

Because of different summary report/dashboard required by many companies, I created a plain vacation table with necessary format to ease you to utilize it properly. Fully editable, you can generate your own reports and tweak it to fit your needs.

Features

Here are just a few things you can do with this template:

• See and tweak all formulas
• Modify current worksheet layout and formulas
• Modify, add or remove conditional formatting rule
• Add your company logo
• Personalize its layout to suit your company style
• Create your own report, dashboard and chart
• Calculate each vacation type taken
• Map your employee’s vacation pattern
• Map your department attendance performance
• Add new employee spreadsheet and calculate monthly wages based on hour instead of days
• Duplicate the worksheet and track employee’s vacation based on department or full- time/part-time employee
• Create report that will summarize the last 3 month attendance performance
• Rank your employee’s attendance performance based on their disciplines where you may use it to evaluate and give them rewards or warning
• Create individual employee attendance summary

But, you must have knowledge in Excel to tweak those formulas properly. Remember to keep one fresh copy of this spreadsheet to make sure that you can start from the original one if you messed with your formulas up.

How to use Employee Vacation Planner (full time version)

Getting Started

• Fill your company fiscal year start date in cell D6
• Select your attendance observation period in cell D7. There are 2 options, today or end of fiscal year. This selection is used to calculate the number of working days. You can switch this selection any time.

Customizing Vacation and Holiday Codes

• There are 4 codes (A,B,C,D) with respective colors available to be personalize to meet your company vacation codes in cell H4-H7.
• Change its code name as well as its description. For example, you can change A to L and type Leave in its description. So, if you typed L in Vacation Tracking Table, its cell color will turn into orange.
• Change holiday color code from R to H or any other letter or you can leave it as it is.

Note:

• You can’t change non working days color. It will stay dark blue.
• R will be calculated as additional holiday if it is put on employee non working days, as well as Vacation Codes

Tracking Vacation Taken

• Type number in No column as a reference, or you can change it to type your employee ID. You can leave it empty as well since this number won’t be used in any formula calculation.
• Type employee’s name
• Type employee’s hired date. It will take the first date of your company fiscal year as its default if you leave it empty.
• Type employee’s resignation date. It will take the end of your company fiscal year as its default if you leave it empty.

Based on hired and resign dates, dark grey color will be shown if respective employees are not yet working or have resigned at that observation period.

• Type employee’s department (optional).
• Type employee’s working hour per day in Work Time/Day column. Basically, these values are not processed yet in this spreadsheet, but you might need it if you want to customize this spreadsheet to calculate your employee’s Working Hour for other purposes.
• Mark employee’s working day in respective column. Type “v” for their working days and leave them blank for non working days.
• Type employee’s vacation allocation per year.

After above step, your vacation tracking table is ready to be filled to plan and track your employee vacation. Every time you type vacation codes into respective employee’s row, its cell color will turn into color that you have specified in code table. You can type partial vacation taken in those cells. But, there are only 3 partial vacation available to be calculated. Those are 1/2, 1/4 and 1/8. As you can see in dummy codes typed in the spreadsheet, I put 1/2C for half day C-type vacation taken by John Doe 1 at 5th January 2015. At the end of vacation tracking table, you will get the summary of total vacation used, and total vacation left as well as total employee’s working days.

This worksheet is protected with password but not the workbook. You can add worksheets and create report or another calculation based on result in vacation tracker worksheet. You can create your own dashboard, calculate working times, create weekly and monthly observation tables, rank your employee attendance, etc.

If you are looking for a vacation planner that can be customized based on hour, you can try a different model I am including - "Part Time Employee Attendance Vacation Tracker". This model will suit you who want to combine part-time and full-time employees within a single spreadsheet. 

As you can see in screenshot above, you can put full-time and part-time employees within a single worksheet, set their leaves allocation and typing their working hours in respective columns. You can find it filled in the spreadsheet that you download as a reference.

Included in this spreadsheet are summary worksheets where you can see their attendance hours are grouped into a weekly, monthly and custom time period summary that will ease you calculating the amount of wages you have to pay to them, if your company are paying them on certain day or dates and based on number of their total working hour in certain period.

How to use Employee Vacation Planner (part time version)

Here is instruction on how to use this spreadsheet.

Vacation Tracker worksheet:

Type Attendance Period Starting Date

There is no Setup worksheet in this template. You must type your employee’s names in Vacation Tracker worksheet directly. Before you type them, fill your company fiscal year start date in cell D6 (Attendance table starting date will always start on Monday. If company fiscal year start date doesn’t fall on Monday, starting date will start from the previous Monday’s date.

Customize Attendance/Vacation/Leave Codes

You need to enter these codes as well as its hour inside white cells in attendance table for attendance calculation

Vacation Codes

There are 4 vacation codes (A,B,C,D) in Free and Protected version with respective colors available to be personalize to meet your company vacation codes. You can change its code name as well as its description (optional). The codes must be 1 letter.

• Overtime Code: This code is hardcoded inside any attendance formulas. You CANNOT change Overtime Color Code. You need to enter number of working hours or holiday/non working day codes inside light blue cells in attendance table for attendance calculation.
• Holiday Code: There is only one code and it is customizable.
• Non Working Day Code: There is only one code and it is customizable.

Adding Employee’s Names

There are 25 rows in Free version and 250 rows in Paid version  that can be filled with your employee’s names.

• Type number in No column as a reference, or you can change it to type your employee ID (optional)
• Type your Employee Name
• Type your Employee Hired Date. It will take the first date of your company fiscal year as its default if you leave it empty.
• Type your Employee Resign Date. It will take the end of your company fiscal year as its default if you leave it empty.
• Type your Employee Department (optional) as additional information for your employee.
• Type your Employee Status. It should be typed Permanent of Part Time (optional).
• Type your employee’s Vacation and Overtime allocation per year (in hour format).

After you finished typing your employee’s names, you can start plan and track their daily attendance.

Typing Employee’s Attendances

There are 2 separate attendance columns. Light blue and white columns.

Light blue columns: Need to be filled with number of working hours, holiday or non working day letter

• Working Hour Format : [Hour] -> Number only. For example if your employee’s is working 8 hours per day from Monday to Sunday, you can type 8 in each light blue cells.
• Holiday Day Format : [1 Letter]. You can type R (you can change it with your own code) for any official holiday
• Non Working Day Format : [1 Letter}. You can type N (you can change it with your own code) for any days where your employees are not working. For example, your company’s working days are from Monday to Friday only. It will make Saturday and Sunday as non working days where you can type N on it.

White columns: Need to be filled with number of hours taken for Vacation or Overtime as well as its codes.

• Vacation Day Format : [H][1 Letter]. For example if your employee is taking 3.5 hours for A (you can change with your own code) type Vacation, you can type 3.5A in respective cell.
• Overtime Format : [H][O]. For example if your employee is taking 2 hours as overtime, you can type 2O in respective cell. This code can’t be modified.

Dark grey color will be shown if respective employees are not yet working or have resigned at those observation dates. At the end of attendance tracking table, you will get the summary of total vacation and overtime used, total vacation and overtime left.

Vacation Summary Worksheets

Customizing Reports

There are 3 report worksheets you can see to summarize your employee’s attendance. All of these reports should ease you on calculating your employee’s wages. You can group their working hours based on week, month or specific time period.

• Weekly Report. You can see employee attendance report week by week for the whole 54 weeks. No customization available in this worksheet.
• Monthly Report. You can see employee attendance report month by month for 12 or 14 months. You need to hide the first and the last month by selecting the available option if you want to see the summary of one full year. You will need it if there are last days of preceding year or first days of following year are included in this report because of weekly based attendance table. Or, you can just leave those columns empty in attendance tracker worksheet.
• Custom Report. You can see employee attendance report based on specific month, week or date period. Select period that you want to see and then select its starting and end time. This custom report only available in paid version.

If you have some knowledge of excel, you can develop this Attendance Worksheet to more suited your company’s need. For example, you can

1. Change its layout, fonts and colors
2. Differentiate report for permanent and part time employees
3. Combine report for non working hour and working hour based employees
4. Create attendance performance charts based on its report
5. Add hourly rate, taxes, budget or any related accounting and finance item and calculate it based on certain period. You can created accounting and finance report as well.
6. And much more…