Quick Steps

Holiday Planner documentation

3 Quick Steps to start working

  1. The Holiday Planner is blank and ready to use. To begin you will need to change the Calendar to the correct Year using the Spin Button on the "System" Worksheet - change any other settings there if you wish, like the Start Day of the Week for the Calendars or whether you wish to display Bank Holidays or Weekends on the Calendars
  2. Now set up all of your Employees on the "Admin" Worksheet. You can format a Section Header by adding a colon ':' sufix after the Text - now when it is pulled through to the "Summary" Worksheet and "Jan" through to "Dec" Individual Month Calendar Worksheets the Section Headers will automatically be Formatted to Bold using Conditional Formating. Section Headers are not taken into account when calculating Holiday Days, Training Days and Absence Days
  3. Add your Holiday, Training and Absence Codes to the "Jan" through to "Dec" Individual Month Worksheets. Optional, hide and Protect the Holiday Planner

Acknowledgement

Holiday Planner was written by Mark Kubiszyn

  • the latest version is version 2.1
  • as per terms of the Software License, you should leave the license Worksheet in the Workbook - it can be hidden if preferred

' © Copyright/Author:
' Mark Kubiszyn 2012-2018. All Rights Reserved
' Website/Follow:
' http://www.kubiszyn.co.uk/
' https://www.facebook.com/Kubiszyn.co.uk/
						

Purchase

You can Purchase Holiday Planner for £2.99 via FastSpring by clicking here

The Admin Worksheet

Select the "Admin" Worksheet. This Worksheet is where you setup your Employee Names and any Section Headers together with the relevant Holiday Entitlement for the Calendar Year

The first Column should be used for the Employee Name which does not have to be unique. Where appropriate, enter data into the other Columns, Holidays Carried, Holiday Entitlement, Additional Holidays and Holidays Sacrificed - these will be picked up in the order designed in the "Summary" Worksheet

On this Worksheet, you can add any formatting that you like, move Cells delete or add Rows and use Copy & Paste to complete all of your Employee data for the Year. Ensure that the first Row of Headers in Bold are not removed otherwise the Defined Name will not work correctly

Tip: Leave plenty of blank Rows underneath your Section Headers so that you can update any new Employees during the Year otherwise the Codes will need moving

The Summary Worksheet

Select the "Summary" Worksheet. This Worksheet is where you get an overview of all of your Employee Holiday Entitlement, together with totals for all of the Monitoring Codes

Overview data includes, Name, Holidays Carried, Holiday Entitlement, Additional Holidays and Holidays Sacrificed. The calculations on this Worksheet include, Holidays at Start of Year, Holidays Remaining, Holidays Booked, Jan->Dec Calendar Month Totals, Training Days, Absence Days and Absence Hours. If you want to monitor any of the additional 5 Configuration Codes you can switch them on in the "System" Worksheet and then they will also be visible here and begin to gather any information for the type of Codes that you are monitoring ie. Duvet Days, Compassionate Leave etc.

Holiday Entitlement is shown in Orange with White Font using Conditional Formatting. If the Entitlement is less than zero no Conditional Formatting is applied. Holidays Remaining is shown in Green with White Font using Conditional Formatting. If Holidays Remaining is less than zero a Conditional Format of Red with White Font is applied

Tip: Use the Autofilter to filter the Employees. You can toggle the Code Colour Highlighting on/off for the Configurable Codes on the "Summary" Worksheet

The Calendar Year Worksheet

Select the "Calendar Year" Worksheet. The Calendar view gives you a detailed view by Employee of all of the Holiday Days, Training Days and Absence Days booked in a Grid format for the selected Year. You can use the Zoom in Excel to scale the Window so that all of the Codes are visible within your view - Holiday Planner looks good from 60% through to 100%

Select a Name from the Drop-down Combo to view any associated monitoring Codes for the respective Employee. As well as the Employee Name, the "Calendar Year" Worksheet details 'Holidays at Start of Year', 'Holidays Booked', Holidays Remaining', 'Training Days', 'Absence Days' and 'Absence Hours'. The 'Key' or 'Legend' for the Codes is shown at the top of the Worksheet - full Days are shown as a solid Fill Colour on the Calendar for any Dates in the individual Calendar Months that are Booked (half-days are shown as Shaded). The number of Holiday Days Booked for the selected Name is shown below the Drop-down Combo underneath the Header, Holidays Booked. The ISO Week numbers are shown at the top of the Grid

The Calendar Year can start on any Day of the Week from 'Sunday' through to 'Saturday' and any Month from 'Jan' through to 'Dec'. The settings for both options can be changed on the "System" Worksheet adjacent to the Headings 'Start Day of the Week' and 'Begin the Calendar on Month' - toggle the options in the Drop-down Combo's

The Date Tracker Worksheet (beta test)

Select the "Date Tracker" Worksheet. This Worksheet displays a Report by Dates Booked in the Holiday Planner for the individual currently selected on the "Calendar Year" Worksheet. You can turn the Date Tracker on or off in the "System" Worksheet (alternatively, if it is not required, you can delete the Worksheet)

The Data Tracker is in beta test at the moment and I haven't decided whether to keep this in future builds or to remove it. It is setup to monitor 50 Days at the most for any of the 3 main Codes and Hours. If you find that Employees are exceeding this amount, you can select Cells "B57:L57" and drag the Array Formula down further to locate more Days/Hours - you will need to Unprotect the Worksheet first

Q. Why is the "Date Tracker" Worksheet in beta mode?

A. Well the reason is that I plan to do a much nicer, improved 'Report List' by Date at some point in the future. What I really want to see is a more concise list for all of the Codes throughout the Year, with the ability to select the Day and Month formatting according to the users choice, something like this:


ISO Week	Holidays				Days
3		Mon 16 Jan - Fri 20 Jan			5
12		Mon 20 Mar - Fri 24 Mar			5
23		Wed 7 Jun - Wed 7 Jun			1
						

The Date Tracker allows you to view all Dates Booked up until a specific Date using the Drop-down Combo Box

Booking Days/Hours on the Individual Calendar Worksheets

Select the "Jan" Worksheet. You will notice that Weekend Days and Bank Holiday Days are automatically Formatted by Conditional Formatting based upon your selected Year - you can turn these off using the settings on the "System" Worksheet if required

Select any Cell in the Calendar Grid and type 'xx' without the apostrophe's - the Cell will be highlighted accordingly as a single Day Holiday and the Monthly calculations for 'Holidays Booked' will update on the right-hand side of the Worksheet for the current Row. You should see a 1.0 value (remember you can also book half Days). Try entering other Codes to see how the Worksheet calculations update - all of the Codes are shown at the top of each individual Calendar Worksheet. You can change the Codes for the main 3 monitoring Codes and all of the 5 Configurable Names and Codes to whatever you like - select the "System" Worksheet and scroll right to the bottom

To clear a Worksheet, simply select all of the Cells in the Calendar Grid from "D6:AN105" and press delete. You can also clear all of the Calendar Year Worksheets at once by selecting all of the Worksheets from the "Jan" Worksheet through to the "Dec" Worksheet and on the "Jan" Worksheet clear the Range "D6:AN105". If you select the "Summary" Worksheet you will notice that the calculations have changed and updated any of the counts for Holiday Days, Training Days and Absence Days. Booked Holiday Days count will increase and the Holidays Remaining count will decrease in-line with your Days Booked

You can begin the Individual Calendar Months on any Day of the Week from 'Sunday' through to 'Saturday' on the "System" Worksheet. If you select 'Wednesday' each Calendar will begin on the first 'Wednesday' of each Month. Previous Dates that fall outside of this Range will be displayed on the previous Month at the end of the Month so that you can still book Codes within the Month (the exception being porior to January for the current Year - these are not shown)

To Book Absence Hours (or Hours Worked if you have changed this on the "Summary" Worksheet Drop-down Combo), simply type in the number of hours ie. 1 or 1.5 and the Conditional Formatting will pick up the values and the Formula will calculate them on the right-hand side

All of the Days/Hours booked will be aggregated or detailed as Dates on the "Summary" and "Calendar Year" Worksheets

You can select any of these Individual Worksheets to view the Holiday Days Remaining for each Employee

Depending on the Start Day of the Week selected, you can Book Codes in January for February for example, but the calculations will only capture the February Bookings on the February Worksheet. If I did not allow this, then the Individual Month Worksheets would be missing Days prior to the Start Day of the Week for each Month because they would not begin on the 1st of the Month

Tip: You can Copy Codes and then press Enter to apply them without changing any of the Conditional Formatting. Use the Tab or left/right arrow keys to move through the allowed Cells quickly

Do not use Cut & Paste on the Individual Month Worksheets, otherwise the Conditional Formatting will add a new Conditional Format or aggregate Ranges together

Splitting the Calendar ie. for a Tax Year April to March

To split the current Year ie. to have the Calendar and Individual Month Worksheets to run from April 2017 to March 2018 you can do the following:


On the "System" Worksheet, set the 'Begin the Calendar on Month' Drop-down Combo to the number '4', 'April'

Now select the 3 Worsheets "Jan" through to "Mar" - select "Jan" first
- hold down the SHIFT Key and then select "Mar" (all left Mouse Button clicks) 
Drag the selected Worksheet after the "Dec" Worksheet

That's it - now the Calendar is in 'Split' Year mode - enter your Codes as normal

						

The License Worksheet

The "License" Worksheet contains details on the licensing for the Holiday Planner Software - please read this carefully. The Holiday Planner License Worksheet can be hidden, leaving the License declaration on the "Summary" Worksheet

The System Setup Worksheet

The "System" Worksheet is used to tweak the settings for the Holiday Planner. The following settings are provided for you to use along with a description of what they do:

i. Calendar Settings

Year with Spin Button
- use the Spin button to change the Calendar Year to any Year that you want

Begin the Calendar on Month (Drop-down Combo)
- use to select what Month the Calendar begins on the "Calendar" Worksheet, default is 1, January. Remember to move the Worksheets into the order you require if you choose any other Month than January - this will then be considered a 'Split Year' ie. Current Year / Next Year

Start Day of the Week (Drop-down Combo)
- used to set the start Day of the Week on the "Calendar Year" Worksheet and Individual Calendar Month Worksheets to one of the 7 Days in a Week

Show Weekends on Calendar (Drop-down Combo)
- used to select whether or not to show the Weekend Days on the "Calendar Year" Worksheet

Show Bank Holidays on Calendar Year (Drop-down Combo)
- used to select whether or not to show the Bank Holiday Days on the "Calendar Year" Worksheet

Always Set Individual Calendars to 1st of Month (Drop-down Combo)
- used to force the Individual Calendar Worksheets to always begin on the first of the Month - set this to False if you want to use a different 'Start Day of Week'

Set Day for the first Weekend Day (Drop-down Combo)
- used to set which 2 Days are displayed as Weekend Days on the "Calendar Year" Worksheet and Individual Calendar Month Worksheets

Set Day for the second Weekend Day (Drop-down Combo)
- if you want a single Weekend Day set both Days to the same ie. Saturday and then only that Day will be displayed as a Weekend Day

Show Bank Holidays on Calendar Months (Drop-down Combo)
- use to select whether or not to show the Bank Holiday Days on the individual Calendar Month Worksheets

Show Weekends on Calendar Months (Drop-down Combo)
- use to select whether or not to show the Weekend Days on the individual Calendar Month Worksheets

Show Row Banding on Calendar Months (Drop-down Combo)
- used to display Row Colour Banding on the Individual Calendar Month Worksheets

Show Column Banding on Calendar Year (Drop-down Combo)
- used to display Column Colour Banding on the Calendar Year Worksheet

Company Name (Text Box)
- used to display your Company Name throughout the Holiday Planner

Number of Employess (Text Box)
- used as an INDIRECT Formula in the Calendar Year Worksheet - adjust this value when the Holiday Planner is reduced or extended for more or fewer Employees

Show Row Banding on the Summary (Drop-down Combo)
- used to display Row Colour Banding on the Summary Worksheet

Show Code Colours on the Summary (Drop-down Combo)
- used to toggle Code Colour Highlighting on the Summary Worksheet for the Configurable Codes

Show Holiday Code Colours on the Summary (Drop-down Combo)
- used to toggle Holiday Code Colour Highlighting on the Summary Worksheet

Show Holiday Code Colours on the Calendar (Drop-down Combo)
- used to toggle Holiday Code Colour Highlighting on the Calendar Worksheet

iii. System Codes

Holiday (Text Box)
- used to monitor Holidays full Days. All codes are lower case only - even if you enter an upper case code, the system will only recognise this as lower case

Holiday Half-day (Text Box)
- used to monitor Holidays half-days

Training (Text Box)
- used to monitor Training full Days

Training Half-day (Text Box)
- used to monitor Training half-days

Absence (Text Box)
- used to monitor Absence full Days

Absence Half-day (Text Box)
- used to monitor Absence half-days

Track Absence Hours or Hours Worked (Drop-down Combo)
- change this as required to track either 'Absence Hours' or 'Hours Worked'

iv. Configurable Names & Codes x 5

- used to calulate and report on full / half-days. 5 configurable codes are available - 3 have been set to Duvet Days, Compassionate Leave and Rest Days but you can change these to whatever you want

Configurable Name & Code (Full day) (Text Box)
- these are the Names and the Codes that you can adjust to track different days

Configurable Name & Code (half day) (Full day) (Text Box)
- these are the Names and the Codes that you can adjust to track different half-days

Name (Full day) (Text Box)
- this is the Text that appears on the" Summary" Worksheet and Individual Calendar Worksheets ie. "Jan", "Feb", "Mar" etc.

Turn on these Configurable Codes? (Drop-down Combo)
- this allows you to toggle the Name and Code on/off at any time

The rest of the settings are automatically calculated and should not be adjusted or changed in any way - they are used to calculate and Conditionally Format the UK Bank Holidays / Leap Year in February. You can Hide this Worksheet once the setup process is complete.

Unprotecting the Worksheets

The Holiday Planner Worksheets are now Protected by Default to prevent any over-writing of Cell Formula. You can Unprotect each Worksheet in the Holiday Planner if you wish. To Unprotect a Worksheet, do the following:

1. Select a Worksheet (you can only Unprotect Worksheets one at a time)
2. On the 'REVIEW' Tab of the Ribbon, select 'Unprotect Sheet' from the 'Changes' Group

Tip: You can add a Password to Protect each Worksheet but I usually Password Protect the entire Workbook before saving on a Network Drive for all Employees to view as 'Read-only' - see the Section below

Password Protecting the Worksheets

You can Password Protect each Worksheet in the Holiday Planner if you wish. The Holiday Planner has been designed with Locked/Unlocked Cells already built in to each Worksheet. To Protect a Worksheet, do the following:

1. Select a Worksheet (you can only protect Worksheets one at a time)
2. On the 'REVIEW' Tab of the Ribbon, select 'Protect Sheet' from the 'Changes' Group
3. Ensure that the 'Protect Worksheet and contents of locked cells' is checked
4. Ensure that at least 'Select locked cells' and 'Select unlocked cells' are checked (you can choose to leave 'Select locked cells' unchecked if you don't want any Cells to be Selected at all
5. Optional - add a Password of your choice for each Worksheet (leave this Blank if you want to be able to quickly Protect/Unprotect the Worksheet)

Tip: I usually Password Protect the entire Workbook before saving on a Network Drive for all Employees to view as 'Read-only' - see the Section below

Password Protecting the Holiday Planner

Once the Holiday Planner has been setup for the Year, you may want Managers to administrate and have write access to the File but Employees to only have read-only access for viewing the File. To allow this but to prevent any data changes you can Password Protect the File so that only you (or other Managers) can access it and make changes - you will make the File Read-only and here's how to do it:

1. Open the HolidayPlanner.xlsx File
2. File->Save As...
3. When the 'Save As' Dialog Box pops up click the small 'Tools' Drop-down next to the 'Save' Button and select 'General Options...'
4. On the 'General Options' Dialog enter a Password to modify and click 'OK'
5. Continue to save the File either over the existing File as the same File Name or as a new File with a new File Name

Now when the File is reopened it will prompt for the Password but you may also open the File in Read only mode by pressing the 'Read Only' Button

Duplicate Names

Holiday Planner can use duplicate naming for Employees. For example, if you have an Employee named 'John Smith', you can add the name more than once on the "Summary" Worksheet and all of the calculations will still work. The reason for this is simple, suppose you have a "John Smith" working underneath a Management Header and also a "John Smith" working underneath a General Staff Header - you need to be able to add Holidays, Absence etc. for both Employees, regardless of the fact that they share the same name. When you use duplicate names, all of the calculations will still work performiing aggregate totals and collating data for any of the monitoring Codes in the Holiday Planner

Reducing the Holiday Planner for Fewer Employees

The Holiday Planner is setup for 100 Employees. Please Note: the Holiday Planner requires you to have at least 2 Employees for the Drop-down Combo to work correctly on the "Calendar Year" Worksheet

To reduce the Holiday Planner for fewer Employees, you need to do the following:


Before you start: Unprotect the "Summary" and "Jan" through to "Dec" Worksheets

1. Delete the unneccesary Rows (assuming Worksheets Run Jan->Dec)
   - left click on the "Jan" Worksheet"
   - hold down the SHIFT key and left-click on the "Dec" Worksheet
   - hold down the CTRL key and left-click on the "Summary" Worksheet
   - left-click again on the Summary Worksheet to bring that Worksheet into focus
   - highlight the Rows that you want to remove
   - right-click on the Row Headers on the left-hand side and select 'Delete'

2. Adjust the Number of Employees in the "System" Worksheet
   - left-click onto the "System" Worksheet
   - scroll down to where you see 'Number of Employees' and change this to the new Number of Employees that you want
   - Tip: you can tell the number of Employees by the last number visible in Column "A"

Now: Protect the "Summary" and "Jan" through to "Dec" Worksheets again
						

Extending the Holiday Planner for More Employees

The Holiday Planner is setup for 100 Employees. To extend the Holiday Planner to allow more Employees, you need to do the following:
- ensure that there isn't an Employee in the last slot, otherwise the Codes for the last Employee will be copied down

Before you start: Unprotect the "Summary" and "Jan" through to "Dec" Worksheets

1. Extend the Ranges (assuming Worksheets Run Jan->Dec)
   - left click on the "Jan" Worksheet"
   - hold down the SHIFT key and left-click on the "Dec" Worksheet
   - hold down the CTRL key and left-click on the "Summary" Worksheet
   - left-click again on the Summary Worksheet to bring that Worksheet into focus
   - select Cell "A105" through to "AY105" (this Formula is required in the "AY" Column)
   - grab the tiny handle in the Cell "AY105" and drag down for as many Rows as you want
   - select the new Rows that you just added, right-click on the Row Headers and change the Row Height to '32'

2. Adjust the Number of Employees in the "System" Worksheet
   - left-click onto the "System" Worksheet
   - scroll down to where you see 'Number of Employees' and change this to the new Number of Employees that you want
   - Tip: you can tell the number of Employees by the last number visible in Column "A"

Now: Protect the "Summary" and "Jan" through to "Dec" Worksheets again
						

Screen Shots

Holiday Planner Summary Worksheet:

Holiday Planner YTD Summary Worksheet

Holiday Planner Calendar Year Worksheet - shown for one Employee, Evy Paskell, Calendar starting on 1st of each Month:

Holiday Planner Calendar Worksheet

Holiday Planner Individual Calendar Month Worksheet - shown at 80% detailing the entire Month, with Bank Holidays and Weekends displayed, Calendar starting on 1st of each Month:

Holiday Planner Individual Calendar Worksheet

Support

Support is only given to Purchased Software. You should only contact me on the Support Email address supplied by FastSpring - please provide proof of purchase ie. Email address or Order Reference

Changelog

- Please Note: all future updates and previous versions are available on request for anyone who has purchased a License for Holiday Planner

28.10.2017 - (Version 2.1) New Date Tracker Worksheet to allow inidivdual Dates to be displayed for Holidays, Training & Absence. You can also use the Drop-down Combo to check remaining Dates up until a specific Date. Bug fix on the December Individual Calendar Worksheet to display the next Year January Holidays when 'Always Set Individual Calendars to 1st of Month' is set to 'False'


12.10.2017 - (Version 2) Complete re-write. Start the Individual Calendars or Year Calendar on any Day of the Week. Force Individual Calendar Months to start on the 1st of the Month but start the Year Calendar on any Day of the Week. A new "Setup" Worksheet to maintain all of your Employees and their Holiday Entitlement separately. Use different Codes for Holiday, Training & Absence. Brighter Colours, with Row and Column Banding and 5 Configurable Codes. Indexing Formula is used throughout the Holiday Planner to lookup the Employees and an improved use of Dynamic Defined Names for list entries. Use duplicate names on the "Summary" Worksheet ie. 'John Smith' can be repeated more than once underneath any section Headers and all of the calculations will still work throughout the Holiday Planner. In order to prevent Screen Flicker in 2010 when using Conditional Formatting a tiny TextBox Object is hidden within each Individual Calendar Worksheet which stops flickering when rendering Conditional Formatting


25.03.2017 - (Version 1.4) Add the ability to set the 2 Weekend Days ie. for UAE "Friday" and "Saturday" can be set as Weekends and displayed on the "Calendar" Worksheet and individual Calendar Month Worksheets


22.03.2017 - (Version 1.3) Enable the Holiday Planner for 250 Employees without Formula change. Make the Formula on the individual Month Worksheets dynamic


17.12.2016 - (Version 1.2) Start the Calendar on any Month of the Year. This means that depending on your Year End you can now Book Holidays, Training Days etc. from say, April 2016 through to March 2017 - all of the individual Worksheets reflect the current Year or the next Year depending on where they fall. I have also moved the Weekend Conditional Formats to the bottom of the list so that any Holidays, Training Days etc. can be viewed over and above a Weekend on the "Calendar" Worksheet. You can now change the Training name and Codes to whatever you like, so you could monitor a different type of Code or Day. I have also made some adjustments to the Formula on the "Calendar" and "YTD Summary" Worksheets to allow the re-ordering of all of the individual Calendar Worksheets without affecting the Formula. Lastly, I have extended the Holiday Planner to allow 100 Employees without any Formula modifications and will add a 'How to' section to the Website to extend for any amount of Employees


05.05.2016 - (Version 1.1) Holiday Planner can now be used to track 'Absence Hours' or 'Hours Worked' using a Drop-down option on the 'YTD Summary' Worksheet. You can enter any positive or negative Value in any of the Calendar Cells to record the Hours. The 'Calendar' Worksheet has also been updated to display 'Holidays at Start of Year', 'Holidays Booked', Holidays Remaining', 'Training Days', 'Absence Days' and 'Absence Hours'


05.03.2016 - (Version 1) release