In this article, we will go through the steps involved in using the PTO Manager Excel template. To learn more about the features of the template, please visit the Product page.

This article is part of a series of support articles for PTO Manager Excel Template. 


Overview of Steps

  1. Enter Weekends and Holidays in the Home sheet
  2. Enter policy inputs in the PTO Policy Settings sheet. Review first accrual window.
  3. Enter one employee’s data in Employees sheet. Review the PTO Report sheet.
  4. Enter list of all employees in Employees sheet
  5. When employee takes PTO, enter PTO info in Time Off sheet
  6. View PTO balance and balance trends in PTO Report sheet
  7. View PTO days on calendar view in Calendar sheet
  8. (Optional): To apply adjustments, enter in PTO adjustments sheet

DETAILED STEPS

Step 1: Enter Weekends and Company Holidays

To begin with, we will enter weekends in Home sheet.

Settings – Select Weekends to exclude from working days 

Here, I have selected Saturdays and Sundays as Weekend days. This will be used by the template to count every Saturday and Sunday as non-working day. If an employee takes paid time off (PTO) that includes a Saturday and Sunday, those 2 days will not be counted against the PTO balance.

Company holidays are given the same treatment. We can enter all our company holidays in the table.

Settings – Enter Company Holidays to exclude from working days 

Weekends and Holidays will be the same for all employees in your organization.

 

We are done with the Home sheet. Let’s move to the PTO Policy Settings sheet.

Step 2: Enter PTO Policy Settings

Though the template is simple to use, there is a lot of flexibility when it comes to designing the PTO policy.

There are some terms to understand and since different companies may have different interpretation for those terms, it is important to state clearly each one of them. I will explain all the possible options in each setting so that you can pick the one that applies to your company.

PTO Policy General Settings – Sample Illustration – Weekly 

START DATE
Let’s say you begin using this template on 1st Nov, 2016. You want to track PTO from 1st Nov in this template.

If you have employees who have already been working in your company and have accumulated a PTO balance, then you would need their balances as of 31st Oct, 2016. We will enter this balance data in a later step.

 

GENERAL SETTINGS
The general settings apply to the three types of PTO (template supports 3).  The following inputs are provided.

 PTO UNIT
We can choose to track employee PTO in units of Days or Hours. If we choose Hours, we must enter PTO taken by employee in Hours. If we choose Days, we can just enter PTO dates (which we will discuss later) and ignore hours taken off.

If you choose ‘Days’, please note that partial days (example: half day) are not allowed as PTO. When you choose Hours, you can enter any partial number of hours as PTO.

 

PTO ACCRUAL PERIOD

This is to inform how we accrue (at what frequency) the annual PTO rate. For example, a company may grant their PTO weekly. Every week, certain hours of PTO will be added to the PTO balance of the employee.

This template supports the following accrual frequencies: Weekly, Every 2 Weeks, Twice a Month, Monthly, Quarterly and Annual.

PTO Accrual Period – Weekly, Every 2 Weeks, Twice a Month, Monthly, Quarterly, Annual 


We will see each of these in more detail. But before that, we need to introduce a couple of other terms. 


FIRST ACCRUAL PERIOD BEGIN DAY
Continuing with the example scenario of weekly accrual, let’s say the accrual happens every Monday in your company. 


November Month accrual days for a Weekly PTO Accrual Scenario 


In the image above, I have shown 6 accrual days (Mondays) from Oct 31st to Dec 5th.

Since Nov 1st (when we started using the template) is a Tuesday, the active accrual period then is from Oct 31st to Nov 6th. So, our First Accrual Period Begin Date is Oct 31st, 2016.

Weekly PTO Accrual Scenario Illustration 


We enter this First Accrual Period Begin Date in the settings. 


PTO Manager Excel Template – Settings – First Accrual Period Begin Day 


If the First Accrual Period Begin Date is after the Start date, then an error message appears. 


Data validation Error when First period begin date after Start date 


Please ensure that the First Accrual Period Begin Date is not after the Start Date. 


ACCRUAL TIMING
Since we accrue at the beginning of the accrual period, we choose that setting as well. In some companies, accrual happens at the end of the period.

Accrual Timing – Beginning or End of accrual period 

Review First Accrual Period for accuracy
In the PTO Policy settings sheet, you will be able to instantly view the first accrual period calculated by the template.

It is very important that you review this and make sure that it meets your business’ expectations.

First Accrual Period Window and Accrual Periods per year for review – Weekly (Beginning) 


A check mark appears where the accrual happens. Here, in this example, accrual happens at the beginning of a period.
If the accrual timing is End of the period, then a check mark will appear next to 6th Nov (End) and 6th Nov will be the accrual day.

We can also see the number of accrual periods per year.

It is 52 for Weekly, 26 for Every 2 weeks, 24 for Twice a Month, 12 for Monthly, 4 for Quarterly and 1 for Annual.

 

The data entry in ‘General Settings’ is the same for Weekly, Every 2 Weeks, Quarterly and Annual accrual frequencies. So, let’s look at ‘Twice a month’.

Twice a Month Accrual Period
For ‘Twice a Month’, we don’t need to provide First Accrual Period Begin Date. We will enter 2 days instead.

Twice A Month Accrual Period Setting 

The template will then take those two days as the accrual days every month. In the example above, accrual will happen 1st and 15th of every Month. 

First Accrual Period Window and Accrual Periods per year for review – Twice A Month 

First accrual period will be 1st Nov to 14th Nov. Number of accrual periods per year will be 24. 

You can also choose ‘Last day’ for the second day and the template can automatically assign the last day of each month, whether it is 28th (Feb) or 29th (Feb – Leap year) or 30th or 31st. 

Twice A Month Accrual Period Setting Last Day option 

The First accrual window will change accordingly. 

First Accrual Period Window and Accrual Periods per year for review – Twice A Month last day 

Monthly Accrual Period
For Monthly, we don’t need to provide First Accrual Period Begin Date. Instead we will choose a day of Month. The options are 1 to 28 and Last day. 

I have chosen the 1st of every month here. 

The first accrual period will be Nov 1st to Nov 30th and the accrual will happen on 1st Nov. 

First Accrual Period Window and Accrual Periods per year for review – Monthly Beginning 

This completes the steps in setting the accrual days and periods.
Now, we need to inform the template the amount of PTO accruals and other inputs.

Entering PTO details
The template supports 2 types of PTO to be tracked. For example, Vacation and Sick days are common in companies. Both can be tracked in this one file.
For the upcoming illustrations, let’s assume the following accrual period inputs in General Settings.

PTO unit is ‘Days’. Accrual happens on 1st of every Month.
We can choose how many types of PTO we need to track. 

Number of PTO Types to track 

Let’ start with just selecting 1 type of PTO.

We can then enter more details about that PTO type.

PTO Policy PTO Type 1 Settings – Sample Illustration – Monthly 

We can give a name. I have entered ‘Vacation’. You can customize it as needed. 

ANNUAL PTO ACCRUAL RATE
Annual Accrual Rate is the PTO that an employee accrues in one year. In our example, the company offers 12 days of PTO per year. 

Monthly PTO Accrual – PTO Balance trend chart 


12 days will be given to the employee annually at 1 day each month for 12 months. 


ANNUAL PTO ROLLOVER POLICY
As an employee continues to accrue PTO every period, the balance keeps growing, assuming there are no vacations taken. Typically, companies do not want employees to accrue a very large balance.

Two reasons:

  1. Employees are encouraged to take regular time off to maintain a healthy work-life balance.
  2. Companies may consider remaining PTO balance as cash that needs to be paid to employee if employee leaves the company. So, a very high balance could mean more liability for the company.

So usually, there is a rollover policy. This determines how many hours of PTO can the employee carry over from one year to the next year.

The template allows three possibilities.

Annual PTO Rollover Policy Setting – Options – Zero Rollover, Rollover Limit, Unlimited Rollover 

Let’s see them one by one. I will explain the impact of each option and this should help you pick the one you prefer for your company.
Zero Rollover: Employee loses all the PTO balance at the end of the year and starts from scratch in the next year.

Choose Zero Rollover option in the drop down.

Annual PTO Rollover Policy – Zero Rollover Limit 

To understand the impact of this setting, see chart below. 

Annual PTO Rollover Policy – Zero Rollover Limit – On Balance Chart 

At the end of 1 year of employment (Nov 2017), the employee’s PTO balance comes from 12 days to 1 day. The employee loses all 12 days of PTO due to Zero rollover policy.

Then, employee accrues 1 day for Nov 2017. So, the balance becomes 1 day.

Rollover Limit: We can set a limit on how many hours are carried over.
I have chosen a 4 day roll over limit in this example.


Annual PTO Rollover Policy – Rollover Limit of 4 days 

The impact can be visually seen below. 

Annual PTO Rollover Policy – Rollover Limit of 4 days – On Balance Chart 

At the end of 1 year of employment (Nov 2017), the employee’s PTO balance comes from 12 days to 5 days. It is 5 because 4 days are carried over and the employee accrues 1 day for Nov 2017.

Unlimited Rollover: Here the employee does not lose any PTO, and will carry over everything to next year. This is an unusual policy for a company.

Annual PTO Rollover Policy – Unlimited Rollover Limit 

At the end of 1 year of employment (Nov 2017), the employee’s PTO balance increases from 12 days to 13 days. The employee carries over all the 12 days and accrues 1 day for Nov 2017. 

Annual PTO Rollover Policy – Unlimited Rollover Limit – On Balance Chart 

Hence, the balance becomes 13 days.

You might have noticed that I talked about this rollover happening after ‘completion of 1 year’. That is because we had chosen Work Anniversary as the PTO Rollover Timing. This setting is also customizable per your business policies.


PTO ROLLOVER TIMING

Companies may apply rollover at calendar year change (Jan 1st of every year) or on work anniversary dates (varying for each employee based on hire date). Some companies may also align this with their financial calendars. To support that, we have the ‘Custom Date’ option. Let’s learn more about this setting.

PTO Rollover Timing Setting – Options – Calendar Year, Work Anniversary, Custom Date 

Since we have already seen how Work Anniversary option works, let’s see how Calendar Year option impacts the balances. I am using Zero Rollover policy for this illustration.

PTO Rollover timing – Zero Rollover and Calendar Year 

The PTO balances are shown below. 

PTO Rollover timing – Zero Rollover and Calendar Year – On Balance chart 

When it is Jan 1, 2017, the balance becomes 1 day. Balance from 2016 cannot be carried over and Jan 1, 2017 brings a new 1 day of PTO accrual. Then, the employee accrues 1 day each month and reaches 12 on Dec 1, 2017. Then, on Jan 1, 2018, balance is set to 1, exactly as it was done on Jan 1, 2017. 

Custom Date
Let’s review the last option in PTO Rollover timing: Custom Date. 

PTO Rollover timing – Zero Rollover and Custom date 

We have chosen Apr 1, 2017 as the rollover timing. This is to inform the template that Apr 1st of every year, PTO rollover policy should be implemented.

The PTO balances are shown below.

PTO Rollover timing – Zero Rollover and Custom Date — On Balance chart 

When it is Apr 1, 2017, the balance becomes 1 day. Balance from Mar 31, 2016 cannot be carried over and Apr 1, 2017 brings a new 1 day of PTO accrual. Then, the employee accrues 1 day each month and reaches 12 on Mar 1, 2018. Then, on Apr 1, 2018, balance is set to 1, exactly as it was done on Apr 1, 2017.

PROBATIONARY PERIOD & MAX PTO BALANCE

Let’s cover next 2 settings together.

Probationary Period and Max PTO Balance Settings in PTO Policy 

PROBATIONARY PERIOD

In some roles, employees may not be awarded any PTO for the first X number of days. For example, employee may not earn any PTO during the first 30 days of employment. You can set that easily in this template as shown above in the Probationary Period.

 

MAXIMUM ALLOWED PTO BALANCE

The rollover limit (that we have discussed earlier) only applies to the balance as of specific timing (calendar year/work anniversary/custom date). Some companies can set a limit on maximum PTO balance an employee can carry at any time. We can set that amount in the Maximum Allowed PTO Balance.

We can see the impact of these settings on the balance chart as shown below.

Probationary Period and Max PTO Setting in PTO Policy – On Balance chart 

Now, we move to the final part of the PTO Policy settings.

ACCRUAL RATES VARY BY TENURE

Companies increase the annual accrual rate for employees who stay with the company for more years. This is a loyalty initiative which rewards loyal employees with better PTO benefits. We can handle such scenarios as well.

We would choose YES to this first.

PTO Accrual rate varies by Tenure Setting 

Once we choose ‘Yes’, the accrual rate and max PTO balance mentioned in the above image does not apply. Instead we must enter that information in the table below.

PTO Accrual rate varies by Tenure Setting - Enter data by Months
PTO Accrual rate varies by Tenure Setting – Enter data by Months

 

We can set the Annual PTO Accrual rate and Maximum PTO balance. In the example above, the employee will receive at the rate of 12 days annually in the first 24 months, then rate of 15 days annually in months 25 to 36 and rate of 18 days annually after 36 months of tenure.

Important: Please make sure that the first entry here is for 0 completed months.

 

You can enter more or less rows as needed. Read how to enter and delete tenure tiers

 

PRORATING WHEN ACCRUAL RATE CHANGES

If the work anniversary happens to be in the middle of an accrual window, then we have to pro-rate the PTO accrued.
Let’s take an example where an employee’s hire date is Jan 16th, 2016. Accrues 10 hrs a month in 1st year and then 20 hrs a month in 2nd year. So, for Jan 1, 2017, employee will earn 15.16 hrs. 15 days (from Jan 1st to Jan 15th) at the rate of 10 hrs per month and 16 days (Jan 16th to Jan 31st) at the rate of 20 hrs a month. The template does this prorating calculation by default.

 

Managing Two Types of PTO

This completes the policy settings for our Vacation PTO. If we need to manage two types of PTO for our company, then, we need to follow the following additional steps.
First, enter the number of Paid time off as 2.

Number of PTO Types to track set to Two
Number of PTO Types to track set to Two

 

Then, enter details of second PTO Type.

PTO Policy Settings for second PTO Type Sick days

 

Just like we entered for PTO Type 1, give this a name. I have given here, ‘Sick’ as name.

The settings are the same as we discussed earlier. The only thing we need to be aware of is that Rollover timing and Probationary period are automatically set to match with PTO Type 1’s. Other settings can be different between PTO Type 1 and PTO Type 2.

PTO Policy Settings for second PTO Type Sick days - On Balance Chart
PTO Policy Settings for second PTO Type Sick days – On Balance Chart

 

We are now set up to track two types of PTO. Green line represents Vacation days balance and Blue line the Sick days balance.

 Managing Three Types of PTO

Similar to the previous step for two types, enter the types of Paid time Off as 3 and give the details of the 3rd PTO.

Here, we have given the third PTO as a personal paid off time.



The balance chart for this scenario looks as below:



Step 3: Enter a sample employee data and review PTO Report

Enter one employee’s data in Employees sheet so that we can review the calculations and ensure that it works as expected.

Let’s enter one employee name and hire date. In this case, hire date >= Start Date (Nov 1st). Hence I don’t need to provide any starting balances. Starting balance carried over is zero.

Sample Employee Data to check balance calculations
Sample Employee Data to check balance calculations

 

In the PTO Report sheet, choose Employee name from the drop down.

Choose Employee from drop down in PTO Report to view accrual rates

 

To refresh all the calculations, press Shift+F9. If the keyboard shortcut doesn’t work in your computer (sometimes keyboard shortcuts are taken over by other applications), then go to Formulas ribbon and press Calculate Sheet.

Employee PTO Report - Calendar - Refresh using Calculate Sheet option
Employee PTO Report – Calendar – Refresh using Calculate Sheet option

 

We can see the employee’s details as well as current accrual rate for the employee.

Further below in the report, we can see the accrual days.

Employee PTO Report – Calendar – Review accrual days for accuracy

 

1st of every month will be shown in light blue font indicating the accrual days.
This step should confirm that the accrual days are correct and meeting your business expectations. We should also check the amount of PTO accruing. For that we see the balance chart in page 2 of the report.

PTO Policy Settings for second PTO Type Sick days – On Balance Chart

 

Green line represents Vacation days Balance and Blue line Sick days Balance, light green Personal time-off balance.

Please note that once the settings are entered once, they don’t have to be modified again. Once you start calculating balances using this template, please do not modify the settings later. Any change in settings will impact the Employee PTO balances.

 

Step 4: Enter All Employees’ data


I have entered 10 employees’ data for the demo here.

Employee Data Sample for PTO Manager
Employee Data Sample for PTO Manager

 

If an employee has left the company, please enter termination date.

For employees who had balances as of Start Date (when you began using the template), then enter starting balances.

If you were manually tracking PTO balances for employees, you can just take the final balance for each employee and enter in the table. This is a one-time step to migrate from your previous system to this template.

For example, employee 6 who was hired in Mar 2014, has a balance of 4 vacation days and 2 sick days.

 

Step 5: Enter Paid time off information

The next step is to enter Paid time off information when employees plan to take time off.
Enter PTO taken by employees in the Time Off sheet.

Employee Name, PTO Start Date, PTO End Date and Time Off Type are required fields.
If an employee takes only one day off, please enter the same date in PTO Start Date and PTO End Date columns.

Enter Paid Time off dates and Paid Time Off type for each employee

 If the PTO unit was chosen as Hours, then PTO Hours column should be entered. An example is shown below.

Enter Paid Time off Hours and Paid Time Off type for each employee

 

Daily PTO hours: Please enter PTO hours taken for each day even if you are taking multiple days together as PTO.

 

Step 6: View Employee PTO Report

We can view the employee’s PTO balance at any time by viewing the PTO Report sheet.

User input cells are filled in yellow color for easier identification.

We can choose an employee and see the report.

To refresh all the calculations, press Shift+F9. If the keyboard shortcut doesn’t work in your computer (sometimes keyboard shortcuts are taken over by other applications), then go to Formulas ribbon and press Calculate Sheet.

Employee PTO Report – Page 1 – Current PTO Balances and Rates

 The report shows current balance as well as balance based on a given date.

The accrual days are shown in the calendar in light blue font. Vacation days are shown in black fill and Sick days in red fill, personal time off in blue fill.

Employee PTO Report – Page 1 – Accrual Days and PTO Days on Calendar

 

We can also see the monthly totals of PTO earned and PTO used by the employee.

Employee PTO Report – Page 1 – Monthly totals of PTO Earned and PTO used

 

The second page of the report has the starting balances information.

Employee PTO Report - Page 2 - Starting Balances, First Effective Accrual period
Employee PTO Report – Page 2 – Starting Balances, First Effective Accrual period

 

If the employee started earning PTO during the middle of an accrual period, then prorating will be done and will be displayed.


PRORATING WHEN EMPLOYEE STARTS IN MIDDLE OF ACCRUAL PERIOD

We all know that employees can start in a new job on any day. Just to illustrate the prorating, let us take a slightly different example. An employee was hired on 15th Nov. Probationary Period set to 0 days.

Prorating when employee starts in middle of an accrual period
Prorating when employee starts in middle of an accrual period

 

We can see that the first effective accrual window is still 1st Nov to 30th Nov, and the accrual happens on 15th Nov.

However, the amount is only 0.533 days of vacation because the employee only accrues for 16 days (15th Nov to 30th Nov) out of the 30 days in the month. So, 16/30 = 0.533. So, the employee accrues 0.533 days instead of 1 day. From the next month onwards, the employee will accrue 1 day per month of vacation. This prorating is only for the first period.

Thus the template can easily pro-rate the PTO awarded when an employee joins in the middle of an accrual window.


We can also see the trend chart which shows PTO balances and PTO used over time.

Employee PTO Report – Page 2 – PTO Balance and PTO Used – Chart

 

The duration of this chart timeline can be customized as needed.

 The Balance Sheet shows the available PTO balances of all employees in the Balance sheet.  View both types of PTOs: Current balance and balance as per your required date, as shown below:

Step 7: View calendar

The Calendar sheet is provided to help get a view of the entire team’s PTO for a month.

Paid Time Off Calendar – Sample – Employees paid time off for a month

 

  • We can change the Year and Month to view the calendar for that month.
  • The three PTO types (Vacation, Sick days and Personal time-off) are shown in Black, Red and Blue fill colors. Holidays are shown in Purple and Weekends in W.
  • If the employee is not employed on a specific day, then NE is displayed.

 

You can change the symbols used by just typing in the appropriate cells in the legend. For example, If I replace V with VC, then the calendar also will reflect that immediately.

Monthly PTO Calendar with changed Legend values

 

The sheet also shows the totals for the month for each employee.

Paid Time Off Calendar – Sample – Employees paid time off – month totals

 

This information could be used for resource planning for a month, as you have a good view of number of days each employee is expecting to work in the month.

Read how to extend the calendar to show more than 40 employees

(Optional) Step 8: Make Adjusments to PTO Balances

If you would like to add or remove PTO, outside the PTO policy settings you have entered, then you can use the Adjustment table.

 

Adjustments Table to add or subtract pto balances
Adjustments Table to add or subtract pto balances

 

Enter Employee name, Date, PTO Type and the amount of Adjustment to be made. To increase balance, enter positive value. To reduce balance, enter negative value.