How to extend calendar view in PTO Manager Excel Template

Posted on

Our Small Business PTO Manager Excel template can be used to calculate and report PTO balances for employees in a company. One of the features of the template is the Calendar which shows the monthly view of PTO taken/scheduled by the employees. By default, this sheet was limited to show 40 employees at a time so that it can fit within 1 page to print. If your company has more than 40 employees to track PTO, then this could be a limiting factor. In this blog post, we will learn how to extend the Calendar view to show more than 40 employees.

The default calendar view looks like this.

PTO Manager - Calendar View - Default
PTO Manager – Calendar View – Default

 

It would stop with 40th Employee.

Calendar shows Employees 1 to 40
Calendar shows Employees 1 to 40

 

For this exercise, let’s assume we have 80 employees in our company. We will use the following simple steps to view more than 40 employees.

 

VIDEO DEMO

 

 

Option #1 (40 employees at a time)

If you don’t need to see data for all 80 employees in the calendar at the same time, then we have a very easy solution already built-in.

Just type 41 in cell A7.

 

PTO Manager Calendar - Enter different starting number
PTO Manager Calendar – Enter different starting number

 

Now, you will be able to see data for employees 41 to 80.

Calendar shows Employees 41 to 80
Calendar shows Employees 41 to 80

 

Option #2 (More than 40 employees at a time):

This involves a few more steps.

 

Before we begin, please save a copy of the file for backup. We will be making formula changes and it is always recommended to create a backup.

Tip: It is also recommended to create backups regularly even if you don’t make formula changes.

 

Step 1: Unprotect sheet

Please unprotect sheet with password indzara

Unprotect Sheet
Unprotect Sheet

 

Step 2: Select Row 45

Click on 45 to select row 45.

Click on 45 to select row 45
Click on 45 to select row 45

 

Step 3: Right click and choose ‘Copy’

Right Click and Choose Copy
Right Click and Choose Copy

 

Step 4: Select rows 46 to 86

Select rows 46 to 86 (we need to select 1 row more than our desired expansion. Since we want to expand by 40 rows, we select 41 rows)

Step 5: Right click and choose ‘Insert Copied Cells’

Right click and choose Insert Copied Cells
Right click and choose Insert Copied Cells

 

Step 6: Select row 87

Click on 87 to select row 87

Click on 87 to select Row 87
Click on 87 to select Row 87

 

Step 7: Clear Row 87

Right click and choose ‘Clear Contents’

Right click and choose Clear Contents
Right click and choose Clear Contents

 

Step 8: Change Print settings

Since the calendar will be more than 1 page long, we need to make some modifications to the Print Settings.

First, Click on ‘Print Titles’ in the Page Layout ribbon.

Select Print Titles
Select Print Titles

 

Set rows 1 to 6 to repeat. This allows the 6 rows to print on second page (employees 41 to 80) as well – allowing the reader to align the dates to the calendar.

Select rows 1 to 6 to repeat in the Page Setup
Select rows 1 to 6 to repeat in the Page Setup

 

Step 9: Protect sheet

Now we are done with making the changes to extend the calendar. We can protect the sheet again to prevent unintentional editing of formulas.

Protect sheet
Protect sheet

 


Recommended

  • Support Page: For all the help articles on Small Business PTO Manager Excel template
  • Product Page: For product features and highlights of the PTO Manager Excel Template

How to increase tracking period in PTO Manager Excel Template

Posted on

The Small Business PTO Manager allows tracking PTO accruals and balances for employees.

For more about the template – Product Page

It is designed such that we can continue to use the same file for many years. By default, the tracking period is set to 2 years. This was set so that the file size is smaller, and the calculations are faster. When we extend the tracking period, Excel needs to do a lot more calculations and store a lot more data. So, we limit the default period to 2 years, but provide an easy option for the user to extend the period in few simple steps. In this blog post, we will see how we can easily extend that to more than 2 years.

 

If you prefer video demos, please watch the video.

 

VIDEO DEMO

 

Before we start the process, it is important that we understand the Start Date concept used in the template first.

You can choose to start tracking PTO in any year from any date. By default, the template has 1-Jan-2018 as starting date.

Start Date - Date of tracking PTO
Start Date – Date of tracking PTO

 

You can change this Start Date to any date.

The template will then assume 2 things:

1) PTO balances of employees as of (31-Dec-2017) the date prior to Start Date will be entered in the Employees sheet.

Starting Balances for Employees
Starting Balances for Employees

 

2) All PTO for employees from 1-Jan-2018 will be entered in the Time Off sheet

Enter PTO for employees from Start Date
Enter PTO for employees from Start Date

 

This is how we ensure that you can use the template to track PTO in companies where you have employees who have been working already. They have already accrued balances and you can just enter them as starting balances and then use just this template to track new PTOs from now onwards.

 

Tracking Period Limit

In some cases, you may have detailed PTO dates for each employee tracked for the past few years and you may want to migrate that detailed data to this template. Or if you have purchased the template in 2017, you would reach the end of default tracking period of 2 years in 2019.

For example, we want to track PTOs from 2010. The Start Date would be 1-Jan-2010.

The PTO Report though will not show current balances as of today (March 2018 when this tutorial was written).

PTO Report not showing Balances
PTO Report not showing Balances

 

And the Balance Trend Chart will only track until 2 years from Start Date.

(To illustrate, I have entered 4000 as the number of days to display on the chart.)

Report Chart shows only 2 years
Report Chart shows only 2 years

 

Now that we understand the limitation, let’s start our process to extend the tracking period.

 

6-Step Method to increase tracking period

 

Overview of steps

  1. Backup file
  2. Unhide hidden CAL sheet
  3. Find the end of table and extend calculations down
  4. Check PTO Report sheet for verification
  5. Hide the CAL sheet
  6. Save changes in File

 

Step 1: Backup

We recommend saving copies (archiving) of your files regularly as there is no other backup system.

Similarly, before making any extensions like we are going to do now, please save a copy of your current file separately with a different name (for example PTO_Manager_Archived_2018_03_18.xlsx) and then use your main file for the following steps.

 

Step 2: Unhide the hidden CAL sheet

Right-click on a sheet name and then choose Unhide.

Right Click on a sheet name and then choose Unhide
Right Click on a sheet name and then choose Unhide

 

Choose CAL and then click OK

Unhide CAL sheet
Unhide CAL sheet

 

Step 3: Extend calculations

Find the end of the calculations table. Click anywhere outside the table. The last cell of the table will appear as shown below.

End of CAL table
End of CAL table

 

Hover over the right bottom corner of that cell. When the cursor becomes double edged arrow, click and drag down.

Hover, Click and Drag Down
Hover, Click and Drag Down

 

We dragged down until row 3653. This extends the calculations until 12/31/2019 (10 years from 1/1/2010).

Extended calculations for 10 years
Extended calculations for 10 years

 

Step 4: Verify

If you now go to the PTO Report sheet, you will see current balances.

PTO Report showing Balances
PTO Report showing Balances

 

The Chart will now track until 12/31/2019.

Balance Trend Chart for 10 years
Balance Trend Chart for 10 years

Step 5: Hide the CAL sheet

Now that we have completed extending the period, we need to hide the sheet.

Right-click on CAL sheet name and choose Hide.

Hide the CAL sheet
Hide the CAL sheet

Step 6: Save the changes

Until we save the file, all the changes made so far will not be retained.

Press Ctrl+S or use the File menu to save the file.

 

Now, we have extended the tracking period to 10 years from the default 2 years.


Recommended 

How to add tiers to the Tenure table in PTO Manager

Posted on

In this blog post, we will discuss the simple 4 step process to add tenure tiers to the Small Business PTO Manager Excel Template.

Small Business PTO Manager Template is a solution to manage employees’ PTO data in Small Businesses

In the PTO Manager template, we can set the PTO accrual to be based on a tenure table.

Tenure Table in PTO Manager Excel Template
Tenure Table in PTO Manager Excel Template

 

For example, employees start with 12 days of annual PTO accrual, and once they complete 24 months of employment (tenure), then they start earning 15 days of PTO every year. We implement this using the tenure table where the user can enter their choice of tenure months, PTO accrual and Max PTO, according to their company’s PTO/Leave policy.

By default, the table has 3 rows and the sheet is locked. In some companies, there could be more than 3 tenure tiers. Let’s see how we can add more rows to this table in the simple 4 step process.

 

Why we lock the spreadsheets

In some of our templates, we protect (or lock) the sheets using a password. This is done to prevent the user from accidentally editing/removing the formulas used. We also give the password to the customer so that they can unprotect and edit if they are familiar with Excel and formulas.

Auto-Expanding Excel Tables

If you are completely new to Excel tables, please visit our Introduction to Excel tables. Excel tables have a default feature by which if we enter data in the row immediately following the last row of the table, it will auto-expand and include the new row as part of the table. This is one of the breakthrough features that almost all the templates from indzara.com depend on. There is a catch though. This feature does not work if the sheet is protected.

 

VIDEO DEMO

 

Step 1: Unprotect or unlock the sheet

Navigate to the Review ribbon and click on Unprotect Sheet.

Step 1 Unprotect the sheet
Step 1 Unprotect the sheet

 

A dialog box pops up where you can enter the password indzara and click OK.

Enter password to unprotect
Enter password to unprotect

 

Step 2: Enter data to add to the table

Now that the sheet is unprotected, we can add rows to the table.

To add one row, type the data in the row immediately following the last row of the table.  Here, we will type the number in cell B40 (last row of the table is 39) and press Enter key.

Enter data and press Enter Key
Enter data and press Enter Key

 

After pressing the Enter key, the table will expand to include row 40. You can confirm this by seeing the border line now move to row 40.

Table expanded with Border
Table expanded with Border

 

To add one more row we can enter data in row 41 and press Enter key. As simple as that.

 

Step 3: Protect the sheet

Once we have entered as many rows as possible, we can protect the sheet again to prevent accidental modification of formulas.

In the Review ribbon, choose Protect Sheet option.

Protect the sheet
Protect the sheet

 

Enter a password and then click OK. There is no need to change any of the other options.

Enter password to protect
Enter password to protect

 

Now you have protected the sheet again.

 

Step 4: Save the changes

After protecting the sheet, please save the changes to the file. Otherwise, the changes you made will not be saved.

Press Ctrl+S or use the File Menu.

Save changes
Save changes

 

 

If there are any questions about this article, please post them in the Comments below.

Small Business PTO Manager – User Guide – Step by Step Tutorial

Posted on
Employee PTO Report - Page 1 - Accrual Days and PTO Days on Calendar

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. For more support, please visit the Support Page.

 

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

If you prefer to watch video tutorial, please see the demo video

 

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
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
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
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 both types of PTO (template supports 2).  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
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
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
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
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
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
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)
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
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 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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 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
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
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.

 

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
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
Employee PTO Report – Calendar – Review accrual days for accuracy

 

For 2016, we see only 1 accrual day. Dec 1st. This is because the employee has 30 days probationary period and will be eligible to accrue only from Dec 1st.
If we change the year to 2017, we will see the accrual days for 2017.

Employee PTO Report - 2017 Review accrual days for accuracy
Employee PTO Report – 2017 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
PTO Policy Settings for second PTO Type Sick days – On Balance Chart

 

Green line represents Vacation days Balance and Blue line Sick days Balance.
You can see that the sick day balance drops down on Nov 1, 2017 since we have Zero Rollover policy and the balance for Vacation days continue to grow, as we have Unlimited Rollover policy for that PTO type.
Verify whether the numbers match your expectations. Once you are comfortable with the calculation results, we are ready to enter all the employees’ data. If the numbers do not match your expectations, please contact me (email link is provided in the Home sheet).

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
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
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
Employee PTO Report – Page 1 – Current PTO Balances and Rates

 

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.

Employee PTO Report - Page 1 - Accrual Days and PTO Days on Calendar
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
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
Employee PTO Report – Page 2 – PTO Balance and PTO Used – Chart

 

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

 

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
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 two PTO types (Vacation and Sick days) are shown in Black and Red 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 P, then the calendar also will reflect that immediately.

Monthly PTO Calendar with changed Legend values
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
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.


Small Business PTO Manager Excel Template – Support

Posted on
PTO Manager Excel Template - Product Features

Thanks for visiting the support page for PTO Manager Excel Template. The following are the support articles to help you get started with using the template. If there are any questions not addressed here, please drop a comment and I will add article/video demo about that topic.

 

Support Articles