Holiday, Training & Absence Planner

Any Year Holiday, Training & Absence Planner with Julian Calendar for Excel

Version 1.4
25Mar 2017

Holiday Planner Documentation

By: Mark Kubiszyn
Holiday Planner YTD Summary Excel Screenshot

Holiday Planner Calendar Excel Screenshot

Holiday Planner Calendar Months Excel Screenshot


An Excel-based any Year Business Holiday, Training and Absence Planner, featuring Julian Calendar with UK Bank Holidays, ISO Week numbers, Week Days and Dates. The Holiday Planner allows you to plot Holiday Days, Training Days and Absence Days against an unlimited amount of Employees, collating the Monthly statistics into a simple YTD Summary. 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. Full Days Booked are shown as a solid Fill Colour and half-days are shown as shaded. Options allow you to show or hide Bank Holidays and Weekends on the "Calendar" Worksheet or the individual Calendar Month Worksheets.

NEW for release 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

FEATURES

- Perpertual Julian Calendar - change the Calendar to any Year & everything adjusts to fit
- A YTD Summary Worksheet collates all of the Code statistics for all of the Employees
- A Calendar Worksheet details all of the Holiday Days, Training Days & Absence Days in a single Calendar Year Grid
- The Calendar Worksheet can start on a Sunday or a Monday (ISO Weeks will only be shown correctly when the start of the Week is set to Monday)
- Individual Calendar Month Worksheets simplify the booking process
- UK Bank Holidays are automatically populated using Conditional Formats
- The Holiday Planner automatically adjusts to handle the Leap Year in February
- The Holiday Planner features ISO Week Numbers, Day of Week & Date
- Conditional Formatting automatically Formats the Codes, Weekend Days & UK Bank Holidays
- Conditional Formatting can highlight Section Headers in the Calendar Month Worksheets
- The Holiday Planner captures half Days for all Codes
- The Calendar is easy to setup and adjust for a new Year
- The Holiday Planner background can be changed quickly by modifying a single Cell
- Unlimited Employees - simply drag down the Cells on the YTD Summary Worksheet (Planner allows 100 Employees without a minor Formula modification - see below for more information)
- You can lock the Planner down using a Password making it Read-only for your employees
- You can Hide the highlighting for the Bank Holidays & the Weekends on the Calendar and the individual Calendar Month Worksheets
- Codes are case insensitive meaning the Workbook interprets upper and lowercase letters as being the same
- The Workbook does not use any VBA & the Formulas used are very simple to understand
- 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
- You can now begin the Calendar in a different Month ie. April. This rotates any Worksheets 'Years' that are not within the current Year to be the next Year ie. April 2016
- This means that you can track Holidays, Training Days etc. from April 2016 through to March 2017 - reorder the Worksheets so that it is all in sequence

REQUIRED

Excel 2010 or Excel 2013

QUICK START - SETTING UP THE HOLIDAY PLANNER

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. The UK Bank Holdays will also be calculated based upon the selected Year. Now, enter your Employees into the shaded area on the "YTD Summary" Worksheet. In order for the Holiday Planner to begin calculating anything, you need to add an Employees Holiday entitlement underneath the Header 'Holiday Entitlement'. There are additional Columns for you to add any Holiday Days carried over from the previous Year, Additional Holiday Days (for example, Days owed or bought from the Company) and any Holiday Days that have been sacrificed (for example, sold back to the Company). Any of these can be hidden if required - the calculations will still work.

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.

To increase the number of Employees, simply select the last Row in the "YTD Summary" Worksheet, "B23:K23" and drag the Cells down. Now, select all of the Worksheets from the "Jan" Worksheet through to the "Dec" Worksheet. Select the last Row in the "Jan" Worksheet, "B23:AM23" and drag the Cells down. Select the "YTD Summary" Worksheet again to deselect the 'Grouped' Worksheets. Any additional Employees will appear in all of the individual Calendar Month Worksheets. Please note: to extend the Holiday Planner past 100 Employees, please see the Q&A section below for the minor Formula modifications

It is recommended that you Hide the "Setup", "Info" and "License" Worksheets when you are finished setting up the Holiday Planner.

That's all there is to it - you are ready to begin using the Holiday Planner.

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 can be collapsed or expanded as required giving you a quick view for every Employee across the entire Year.

BOOKING DAYS ON THE INDIVIDUAL CALENDAR MONTH 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 accordinlgy and the Monthly calculations will update on the right-hand side of the Worksheet for the current Row. You should see a 1 value. Try entering other Codes to see how the Worksheet calculations update. To clear a Worksheet, simply select all of the Cells in the Calendar Grid from "E7:AI23" 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:AI23". 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 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.

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

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

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.

THE INFO WORKSHEET

The "Info" Worksheet is used to provide some information on setting up the Calendar each Year with additional comments and a list of features. It is provided for convienience within the Holiday Planner File itself instead of using this online documentation. The "Info" Worksheet also includes an attribution to the author - please leave this in situ as it also contains links to the online documentation and for purchasing the Holiday Planner. 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 Mangers and Employees to has access to the File. To allow this but to prevent any data changes you can Password Protect the File so that only you 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.

Q&A

Q. Can I change the Background Colour of the Holiday Planner?
A. Yes, simply modify the Custom 'Background' Style. Click on a Cell anywhere outside of a Grid without Text. On the "HOME" Tab of the Ribbon, right-click on the 'Background' Cell Style on the 'Styles' Group and select 'Modify...' to modify the Style. Click 'Format...' and on the 'Fill' Tab of the 'Format Cells' Dialog, choose a new Fill Colour. The Background Colour will update and render the Holiday Planner in your new Colour.

Q. How do I extend the Holiday Planner for more than 250 Employees?
A. You need to select the last Row in the "YTD Summary" Worksheet, "B23:K23" and drag the Cells down for 1000 or 10,000 Rows (however many extra that you require). Now, select all of the Worksheets from the "Jan" Worksheet through to the "Dec" Worksheet. Select the last Row in the "Jan" Worksheet, "B23:AM23" and drag the Cells down for 1000 or 10,000 Rows (however many extra that you require). Select the "YTD Summary" Worksheet again to deselect the 'Grouped' Worksheets.

Now you need to adjust some Formula. On the "Calendar" Worksheet turn on 'Headings' and unhide Columns "AE:BH". In Cell "AH5" change the Formula from '$B$106' to '$B$XXX' where XXX is the number of Rows of Employees. Do the same for the Formula in Cells "AH27:AH38" and the Holiday Planner will pickup all of the extra Employees. Hide the Columns again

Q. Can I change one of the Codes for a bespoke Name and Code to track 'Duvet days' (my Companies name for Employee days allowed in lieu)?
A. Yes, you can change the 'Training days' Name and Code. Click on the "Setup" Worksheet and scroll to the bottom. Here you will find the 'Configurable Name & Code' section. For example, simply change 'Training day' to 'Duvet day' and the Code from 'tt' to 'dd'. Repeat this for Half-days and for the Header that tracks Training days. Now you can track Duvet Days

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