Quick Steps

Holiday Planner documentation

2 Simple 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 "Setup" Worksheet - change any other settings there if you wish, like whether you wish to display Bank Holidays or Weekends on the Calendars
  2. Now set up all of your Employees on the "YTD Summary" Worksheet. You can Format a Section Header by prefixing the Text with a '>' symbol (without the apostrophe's) and pressing CTRL+B to Bold it in the "YTD Summary" Worksheet - now when it is pulled through to the individual "Jan" through to "Dec" 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

Acknowledgement

Holiday Planner was written by Mark Kubiszyn

  • the latest version is version 1.4
  • 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-2017. 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 YTD Summary Worksheet

Select the "YTD Summary" Worksheet. As already discussed above, this Worksheet is where you setup all of your Management and Employee Names

The first 5 shaded Columns are for you to populate. Where appropriate, enter data into these Columns, Name, Holidays Carried, Holiday Entitlement, Additional Holidays and Holidays Sacrificed. The Holiday Planner will not start calculating anything unless one of the last 4 Columns has data entered into it. The calculations on this Worksheet include, Holidays at Start of Year, Holidays Remaining, Holidays Booked, Jan->Dec Calendar Month Totals, Training Days and Absence Days

The Grouped, Jan->Dec Months at the top of the Worksheet can be collapsed or expanded as required giving you a quick view for every Employee across the entire Year

The Calendar Worksheet

Select the "Calendar" 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

Select a Name from the Drop-down Combo in Cell "B26" on the Worksheet to view any associated Codes. As well as the Employee Name, the 'Calendar' 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 can start with either a "Monday" or a "Sunday". The setting can be changed on the "Setup" Worksheet adjacent to the Heading "Start of Week" - toggle the option in the Drop-down Combo

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 "Setup" 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 Name for the 'Training day' and 'Training half-day' configurable Codes to whatever you like - select the "Setup" Worksheet and scroll right to the bottom

To clear a Worksheet, simply select all of the Cells in the Calendar Grid from "E7:AI256" 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 "E7:AI256". If you select the "YTD 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

The Info Worksheet

The "Info" Worksheet details how to set up this Holiday Planner including additional information on hiding Columns, extending the Calendar for more Employees (the Holiday Planner allows for 250 Employees without any Formula modification), Custom Formats used to hide zeros, changing the Background Colour for the Holiday Planner and how to track 'Hours Worked' instead of 'Absence Hours'

The Setup Worksheet

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

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

Begin the Calendar on Month
- use to select what Month the Calendar begins on the "Calendar" Worksheet, default is 1, January

Start of Week (Drop-down Combo)
- use to select either Sunday or Monday for the "Calendar" Worksheet to set the start of the Week

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

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

Set Weekends
- use to set which 2 Days are displayed as Weekend Days on the "Calendar" Worksheet and individual Calendar Month Worksheets. If you want a single Weekend Day set both Days to the same ie. Saturday

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

Configurable Name & Code (scroll to the bottom of the Worksheet)
- used to allow the Training Code to be swapped for a Configurable Name and Code of the users choice

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.

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

Screen Shots

Holiday Planner YTD Summary Worksheet:

Holiday Planner YTD Summary Worksheet

Holiday Planner Calendar Worksheet:

Holiday Planner Calendar Worksheet

Holiday Planner Individual Calendar Worksheet:

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

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