timeoff1

Time Off Tracker Template

by with One Comment accountingcalendartemplatevacation

If you need to track employee sick days and vacation, then the time off tracker template can help you. This template can also help you decide whether to approve or deny time-off requests as well. Below, I’ll go over the main features of the template and how it works.

Setting up the time off tracker

On the Summary tab, you’ll start with a list of all the employees you want to track. This will include their hourly wage, start date, their beginning balance for their vacation days, as well as their vacation rate. The time off tracker template will also track sick days as well.

You can enter the current year and the cutoff date if you want to see up to a certain point in time. The annual hours and vacation rate percentage will impact the annual vacation day accrual. Annual hours you’ll probably want to set to either 2,000 (50 weeks x 40 hours) or 2,080 (52 weeks x 40 hours).

For sick days, there is a section off to the right on the summary page where you can enter the number of sick days people are entitled to annually. You can also specify a maximum number of people that can be off at any one time. This is related to approving requests which I’ll cover further down.

You’ll also see a section for holidays and blackout days for when you don’t want people taking time off. These lists can be as long as you like.

Entering and requesting time off

Whether you want to check if a person can book time off during a certain period or if you want to actually book it, you’ll do this through the Request.Form tab. Here, you can select the employee, the type of request (vacation or sick), and how long they will be off for. This template assumes employees do not work weekends. If the request includes a weekend, it will automatically account for that. Here’s a sample request for someone looking to take Jan. 24 – Jan. 31 off.

The available days, hours, days requested and hours requested will all automatically fill in once you enter all your selections. You’ll notice the days requested equals six, which isn’t counting Saturday and Sunday. It also assumes these are full days off and hence multiplies the days off by an eight-hour workday to get to 48 hours requested off.

If you want to see if this request complies with your policy (blackout rules, maximum people off) you can click on the Check Availability button. Then you will see the following summary:

The lights come up green for having sufficient time available and there being enough coverage. But it comes up red because the request includes a blackout day. Over on the right-hand side, you’ll see the person’s most recent time-off requests. It will also show any people who are off during this time.

You can still proceed and click the Post Time-Off Request button either way. It will post the information into the Timeoff tab:

If I double-click on any of the red boxes to the right, it will delete an entry. There is no data that needs to be entered on this tab, this is simply for record-keeping. The Timeoff tab is used to populate other areas of the spreadsheet.

If I were to go back and try and book another entry on Jan. 31 for a different employee, it will now tell me that someone is off during this time:

Note: you won’t be prevented from posting vacation if a person already has vacation booked for the same date. But if you make a mistake you can clear the duplicate entry from the Timeoff tab. You can also visually see who is off during a given period on the Calendar tab:

Blackout days are highlighted in black, vacation is dark blue, weekends are light blue and sick days are in yellow. In the above example it shows the one employee who took a vacation day during the blackout period. The color code for vacation overrides the blackout formatting. From this, you can also see that two employees were off on Jan. 31.

Entering in partial-day requests

If an employee is taking a half-day or only a certain amount of hours off rather than a full day, you can select the Partial Day option from the drop-down in the Request.Form tab. A new field will appear, allowing you to enter the hours per day:

In this example, this employee is requesting 5 hours off on both Jan. 30 and Jan. 31. The employee is requesting a total of 10 hours off, or 1.25 days. If you need to do a partial request, you can’t mix and match with full-day requests, you’ll need to do them separately. Even if the hours are different, multiple requests will be needed.

Whether an employee takes a full or a partial day off, it’ll look the same on the Calendar tab.

Adjusting for rate changes

One of the more complex calculations this time off tracker template factors in is if an employee moves to new pay rate or if their vacation rate changes during the year. This is where the Ratechanges tab comes into play. You select the employee, the date that the change is effective as well as the date it ends – this can be left blank if there’s no further rate change. The purpose of the end date is if there are multiple changes for an employee during the year.

Then, you enter the new hourly wage and vacation rate. Enter both numbers. For instance, if an employee now accrues 6% vacation rather than 4%, you’ll want to enter the new vacation rate but you’ll also want to enter in their hourly wage as well, even if it remains the same. This is important so that the formula calculates correctly.

This calculation will spit out a change in daily accrual as well as a total adjustment for the year based on the cut-off date. This adjustment will populate on the Summary tab under the Vacation Days Adj (Rate Changes) field.

As with any complex calculations, always be sure to double-check these numbers against your own. Especially when it comes to multiple rate changes a year, it’s important to ensure the data is entered correctly and that the correct number of days are being accrued. Although I’ve tested the spreadsheet, it’s impossible to factor in every possible situation and so I cannot guarantee 100% accuracy in all situations.

If you’d like to test out the template, you can download the free version here which is limited to tracking five employees. The full version has no limits and there are no ads and the code is fully unlocked:

If you liked this post on the time off tracker template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

TrackEmployeeVacation

Accrued Vacation and Time Off Template

by with No Comment templatevacationvbafree

Tracking employee vacation and time-off requests can be a bit of a headache. This template will help track time off as well as how much vacation has been used up and how much is remaining. It also helps to prepare your recurring journal entries to expense vacation.

How the Vacation Accrual Template Works

This template will help you track and reconcile vacation liability owed to employees. It will calculate this both in hours accrued and taken, and in dollar values if you need to as well.

The first tab is the EmployeeInfo tab which is where you will need to enter your employee information. The Per Day $ and Vacation Rate fields are automatically calculated. Everything else, you’ll need to enter in. In the below example I’ve filled in some sample employee information:

Vacation accrual template showing Employee Info information.

You can replace the data in the table and you can also add rows by just typing the employee name in the next blank row in column A and the table will automatically re-size.

If you do not want to track dollars accrued then you can simply leave the wage field blank. This template will allow you to track vacation dollars and hours even if there are wage or vacation rate changes. In the above example, Jack Smith had a wage of $10 from January 1 until May 31. And from June 1st his wage changed to $15 and vacation rate increased from 4% to 6%. I’ll go over this calculation later in this post to show you that it has calculated properly in the reconciliation tab.

If there are no rate changes you can leave the end date field blank. You only need to use the end date field if the employee has a change in vacation days or wages, or is no longer with the company. In the latter case you will also want to set their status to Inactive. Doing so will exclude them from the JE tab and calculation.

The next section is to ensure the calculation is done correctly and the right percentages are used – daily hours will likely stay as 8 and the annual hours to used in the vacation rate calculation. So if you are paying an employee 4% vacation that is equivalent to 10 vacation days based on 2,000 hours.

Also on this tab is a section to enter the holidays for the year, and this is to ensure that the vacation calculation ignores these non-working days.

The cutoff date is up to what date you want to reconcile to. So if you wanted to know what the vacation liability looks like at the end of the year you would set it to Dec 31, as I have below.

Vacation template with fields for holidays, daily hours, and date cutoffs.

The next tab is the TimeOff tab. This is where you can select when the employee took vacation, and I added an option for sick as well in case you wanted to track that as well.

Time off tab in the vacation tracker template.

In the above example, John Burns is away January 3rd, 4th, and 5th, so I enter the 3rd as the start date, and the 5th as the end date. The next business day would be the day he is back at work. The hours taken reflects the average daily hours from the previous tab. The wage information also comes from that tab.

On the Calendar tab you will see a year-long calendar off the time off. This isn’t part of the vacation calculation but allows you to visually track vacation and sick time taken. 

Chart showing days when an employee is off work.

You see above that the days for January 3,4, and 5 are highlighted for John Burns to indicate he took vacation on these days. Sick days will show in a different colour. Weekends and holidays are also filled in with light grey. This would be more of a scheduling tool when you are dealing with many employees and wanted to minimize time off conflicts and overlaps. 

The Reconciliation tab shows a summary of all of the time earned and vacation time taken off.

Vacation reconciliation showing vacation hours accrued.

You can enter a starting balance if you carry forward vacation from the previous year, and adjustments for any items not captured in the hours earned or time off taken (e.g. vacation pay out). The other cells are formulas and should not be modified.

Verifying the Vacation Reconciliation Calculations

I will refer to my example above where Jack Smith had a change in wage and vacation rate. His wage won’t affect this calculation but his vacation rate will.

From January 1 to May 31 he will accrue at a rate of 4%. Since he works 8 hours a day x 106 working days which fall within this range once holidays are factored in, that is a total of 848 working hours. 4% of this total is 33.92 vacation hours.

The second range is from June 1 until the end of the year (you can adjust the cut-off date in the EmployeeInfo tab). During this range there are 145 working days x 8 hours for a total of 1,160 total hours worked. At his new rate of 6% this is 69.60.

If I total 33.92and 69.60 that gives me 103.52 hours earned which is what the reconciliation shows.

If you track dollars there is a separate section for the dollars accrued which works in the same way.

Again, going back to my example earlier, Jack Smith had a $10 wage from January 1 to May 31st at a 4% vacation rate. If I multiply 106 working days by his daily pay (daily hours of 8 x wage of $10 = $80 per day) then I arrive at 106 x 80 = $8,480. And 4% of this is $339.20.

Now from June 1st to Dec 31st that is 145 working days x his new daily rate of $120 ($15 wage x 8 daily hours) is a total wage of 17,400. And 6% of 17,400 is $1,044.00.

If I add the two amounts together, I get $1,383.20 (1,044.00 + 339.20). As you see this equals the dollars earned for Jack Smith.

Vacation Accrual Journal Entry

The last tab is the JE tab which is used to generate your vacation accrual journal entry.

A vacation accrual journal entry.

You will need to specify the GL accounts, pay period, start, and end dates. There is also a space where you can put an adjustment for an employee. For instance, your accrual might be for a two week period but if someone was terminated or shouldn’t have accrued for any period of time here is where you can make the adjustment.

The employees listed as active will show up in this list as well as their wage during the dates you enter – as long as you don’t have rate changes that occur in the middle of a pay period there should be no issues here.

Please note this spreadsheet should be used to help reconcile to your payroll provider’s data to ensure accuracy and completeness. Do not use this spreadsheet alone in determining your vacation liabilities as I do not offer any guarantees as to its accuracy.

Download the Vacation Accrual Template

Free version: Download Here. The free version is limited to 4 spots for employees on the EmployeeInfo tab and the sheets are locked and there is an ad in the Excel ribbon.

For the full version (no ads, locks, or limits to # of employees) click on the following button: