Geek Documentation Logo

Holiday Planner

Version 3.6


Introduction

  • Price : £2.99
  • Purchase : Holiday Planner
  • Software Name : Holiday Planner · An Excel-based any Year, any Week Day small Business Holiday, Training and Absence Planner that works in Google Sheets and Microsoft Online
  • Software Version : v 3.6
  • Author : Mark Kubiszyn
  • Website : Kubiszyn.co.uk/
  • License : MIT License (MIT)

If you have already purchased this Software, let me take a moment to thank you for being a loyal customer
You are entitled to free lifetime updates for ALL future builds

This documentation is to help you understand the Software and to give you a more meaningful insight into what it can do. Please go through the documentation and read it carefully · Basic to Moderate skills are required

Requirements

You will need the following Excel Version & Prerequisite to use this Software

  1. Excel 2010, 2013 & 2016 (32bit or 64bit). A Google account if using via Google Sheets. A Microsoft 365 Subscription if using via Microsoft Online
  2. Basic Excel skills
  3. Windows PC · NOT a Mac!

No support is provided for customization or development of this Software






About Holiday Planner #back to top

An Excel-based any Year, any Week Day small Business Holiday, Training and Absence Planner, featuring Julian Calendar with UK Bank Holidays, ISO Week numbers, Week Days and Language-specific Dates ie. French, Korean, Romanian etc. The Holiday Planner allows you to plot Holiday Days, Training Days and Absence Days and 2 configurable Codes against 100 Employees, collating the Monthly statistics into a simple Date Filterable, Summary Worksheet. The Calendar Sheet view gives you a detailed view by Employee of all of the Holiday Days, Training Days and Absence Days booked in a Filterable, Grid format for the selected Year. Full Days Booked are shown as a solid Fill Colour and half-days are shown as shaded. The Planner allows a Year split ie. April to March and the Indiviual Month Sheets can be renamed once the Plannner is setup. Version 3 features a Report Sheet and has been lovingly crafted to work with Google Sheets and Microsoft Excel Online

Holiday Planner Image

Features include:

  • * Easy setup. No Admin Sheet, simply setup your Employees in the Summary Sheet
  • * Works in Google Sheets and Microsoft Online
  • * Perpertual Julian Calendar - change the Calendar to any Year & everything adjusts to fit
  • * Conditional Formatting for all Monitoring Codes
  • * A Summary Worksheet collates all of the Code statistics for all of the Employees
  • * - Filter the Summary Sheet by a Date up until to only show Bookings until that Date
  • * A Calendar Year Worksheet details all of the Holiday Days, Training Days & Absence Days in a single Grid with separate Totals Snapshot
  • * - Filter the Calendar Sheet by a Date up until to only show Codes until that Date
  • * - Show the current Date on the Calendar
  • * - Start the Calendar on any Day of the Week
  • * - Maintain a list of Employees and Holiday information that will not get pulled into the Individual Calendar Month Sheets or appear in the Calendar Drop-down using a Hashtag '#' prefix
  • * A Report Sheet to give a Period account of all of the Block Bookings for an Employee
  • * - Filter the Report Sheet by a Date up until to only show Codes until that Date
  • * 2 additional Configurable Codes are allowed
  • * Employee Names do not have to be unique ie. you can have John Smith under one section Header and then another John Smith underneath a different section Header on separate Rows and all of the calculations will still work. Subsequent John Smiths will have an incremental suffix ie. John Smith [2]
  • * Choose to use the automatic UK Bank Holidays or maintain your own Country-specific list of Bank Holidays or do both!
  • * Adjust the Dates to be Language-specific ie. French, Korean or English UK etc.
  • * The Holiday Planner features ISO Week Numbers 1-53
  • * Conditional Formatting automatically Formats the Codes, Weekend Days & UK Bank Holidays
  • * The Holiday Planner captures half Days for all Codes
  • * 100 Employees without modification
  • * You can choose different Days for the Weekend, for example in UAE (United Arab Emirates) 'Friday' and 'Saturday' can be set as Weekend Days
  • * Holiday Planner Worksheets are now Protected by default and all of the Formula is hidden. You can easily undo this by selecting Unprotect on the Review Tab on the Ribbon
  • * You can also lock the Planner down using a Password and Save it on a Network Drive, making it Read-only for your employees but Admin ready for yourself
  • * Codes are case insensitive meaning the Workbook interprets upper and lowercase letters as being the same
  • * The Workbook does not use any VBA
  • * Holiday Planner can be used to track 'Absence Hours' or 'Hours Worked' using a Drop-down option on the "Setup" Worksheet - in fact you can rename this to anything you like or add another name to the Drop-down Data Validation list to track Hours. You can enter any positive or negative Value in any of the Calendar Cells to record the Hours
  • * Easy Calendar Rotation. You can 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 allows a Split Year to be maintained ie. April to March
  • * Individual Month Sheets can be renamed to Jan, January or just J etc.





Getting Started #back to top

The download File

If you want to use the Holiday Planner in Google Sheets then upload the File to a Google Sheets Folder - I would set the Zoom to 90%. If you are using on a regular PC or Laptop, open the Holiday Planner File

The Holiday Planner download Workbook is blank and ready to use - as a visible example, the 3 main Codes are entered for Month 1 for Keiko Jensen. To begin you will need to choose your Language for the Dates and change the Calendar to the correct Year on the "Setup" Worksheet (language settings work in an Excel client and when using the Planner via Microsoft Online - language settings do not work with Google Sheets). You can also choose to use the default Bank Holidays list or use a Bespoke Bank Holidays list that you need to maintain

Quick Setup

Once you have setup some of the options in the "Setup" Sheet, add your Employees to the "Summary" Sheet, including their Holiday entitlement and any additional Days or Days Sacrificed etc. You can move these around within the area where the Cells are not Protected and everything will work fine

Now add your Holiday, Training & Absence Codes to the individual Calendar Months

The Setup Sheet #back to top

The "Setup" Worksheet is used to adjust all of the settings for the Holiday Planner. The following settings are provided for you to use along with a description of what they do - the Blue Coloured Cells are System Cells and should not be edited:

Holiday Planner General Settings

Holiday Planner Tracking Codes

Holiday Planner Automatic Bank Holiday List

Holiday Planner Bespoke Bank Holiday List

The Summary Sheet #back to top

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. The first six Headers are editable if you require any changes. There are also 5 hidden Columns "D:H" if you need to include any additional workings

Holiday Planner Summary Sheet

An overview of the Summary data includes, Dept. (Department), Employee Name, Carry (Holidays Carried), Ent. (Holiday Entitlement), Add. (Additional Holidays) and Sacr. (Holidays Sacrificed). The calculations on this Worksheet include, BH in Lieu (Bank Holidays in Lieu), WE in Lieu (Weekends in Lieu), Start of Year (Holidays at Start of Year), Rem. (Holidays Remaining), Booked (Holidays Booked), Jan->Dec Calendar Month Totals, Absence Days, Training Days, Misc1, Misc2 and Hours Worked/Absence Hours. If you want to monitor any of the additional 2 Configuration Codes you can set them up and switch them on in the "System" Worksheet - then they will be visible here and begin to gather any information for the type of Codes that you are monitoring ie. Duvet Days, Compassionate Leave etc.

Booked Holidays are coloured Green. The rest of the Codes are also coloured according to the Conditional Formatting

A Filter Box is included to allow you to filter all of the Summary values by an up until Date ie. setting this to 31/01/2019 with a Calendar Year of 2019, would only display the Totals up until (and including) the last Day in January 2019

You can maintain a list of Employees on the Summary Sheet wihout having them pulled into the Individual Calendar Sheets or being visible in the Drop-down for the Calendar Sheet by using a Hastag '#' as a prefix ie. #Temp, #Martin Thistle, #31 (Dept. Employee Name, Holiday allowance Days)

Tip: Use the Autofilter to filter a specific Department or Employee Name

The Calendar Sheet #back to top

Select the "Calendar" Worksheet. The Calendar view gives you a detailed view by Employee of all of the Holiday Days, Training Days, Absence Days and Misc1/Misc2 Days booked in a Grid format for the selected Year. A Filter Box is included to allow you to filter all of the Calendar Codes by an up until Date ie. setting this to 31/01/2019 with a Calendar Year of 2019, would only display the Codes up until (and including) the last Day in January 2019

Holiday Planner Calendar Sheet

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" Worksheet details 'Holidays at Start of Year', 'Holidays Booked', Holidays Remaining', 'Training Days', 'Absence Days' and 'Hours Worked/Absence Hours'. The 'Key' or 'Legend' for the Codes is shown at the right-hand side of the Calendar Grid - 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 a lighter Colour). The number of Holiday Days Booked for the selected Name is shown below the Drop-down Combo underneath the Header, 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 "Setup" Sheet. Other options are to use Uppercase for Calendar Days, Show the Current Date on the Calendar, Show the Weekends on the Calendar and show the Bank Holidays on the Calendar

Tip: turn on the option to show the Current Date on the Calendar on the "Setup" Worksheet

The Report Sheet #back to top

Select the "Report" Worksheet. This Worksheet displays a detailed Report by Bookings for the 3 main Codes and the 2 additional Codes. You can turn the Report on/off in the "Setup" Sheet. Select an Employee to view. The Report Sheet allows you to view all Dates Booked from and up until a specific Date

Holiday Planner Report Sheet

Here is an example of the Report for Booked Holiday Periods


Booked Periods
WK 1: 02 Jan 19 — 04 Jan 19 (3 days)
WK 6: 04 Feb 19 — 07 Feb 19 (3 and a half days)
WK 11: 14 Mar 19 (1 day)
WK 19: 08 May 19 — 09 May 19 (1 day)
WK 23: 03 Jun 19 — 04 Jun 19 (1 and a half days)
WK 31: 01 Aug 19 — 02 Aug 19 (2 days)
WK 45: 04 Nov 19 — 06 Nov 19 (2 and a half days)

						

Booking Days/Hours on the Individual Calendar Worksheets #back to top

Select the "1" Worksheet - this will always be the first Month for your Planner. You can rename the Sheets if you like - writing the Software like this allows a rotational or split Calendar without moving Sheets about. 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

Holiday Planner Month Sheets

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 'Booked' will update on the left-hand side of the Worksheet for the current Employee. You should see a 1.0 value (remember you can also book half Days). The remaining Days will also be updated underneath the 'Rem.' Header for the respective Employee. Try entering other Codes to see how the Worksheet calculations update - all of the Codes are shown in the Legend, at the top of each individual Calendar Month Sheet. You can change the Codes for all of the main monitoring Codes to whatever you like - select the "Setup" Worksheet and scroll down until you see the 'Tracking Codes:' section

To clear a Worksheet, simply select all of the Cells in the Calendar Grid from "M6:AW105" and press delete. You can also clear all of the Calendar Year Worksheets at once by selecting all of the Worksheets from the "1" Worksheet through to the "12" Worksheet and on the "1" Worksheet clear the Range "M6:AW105". 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, Absence Days and any other additional Codes. Booked Holiday Days count will increase and the Holidays Remaining count will decrease in-line with your Days Booked

To Book Absence Hours (or Hours Worked if you have changed this on the "Setup" Sheet Drop-down Combo), simply type in the number of hours ie. 1 or 1.5 and the Formula will calculate them on the left-hand side

All of the Days/Hours booked will be aggregated or detailed as Dates on the "Summary", "Report" and "Calendar" Sheets

You can select any of these Individual Month Sheets to view the Holiday Days Remaining for each Employee at any point in time

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 Sheets, 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 #back to top

To split the current Year ie. to have the Calendar and Individual Month Worksheets to run from April to March, on the "Setup" Sheet, set the 'Start Month of the Year' to April. Now the Sheet "1" will be April and you can rename it to 'Apr' or 'April' if you want. The "Calendar" Sheet will now run from 'April' to 'March'. If there are any Days before the 1st of April for the current Year, the Calendar will display them in a lighter, grey Colour Font - the same will apply to the end of March for the next Year where you may see some Days for April coloured in a lighter, grey Font

Holiday Planner Split Year Calendar

Unprotecting the Worksheets #back to top

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. Select a Worksheet (you can only Unprotect Worksheets one at a time). On the 'REVIEW' Tab of the Ribbon, select 'Unprotect Sheet' from the 'Changes' or 'Protect' Group depending on the Excel version that you are using. Protection does not apply to Google Sheets - you will need to maintain this yourself

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 #back to top

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 and the Formula hidden. Drop-down combo Boxes are allowed by default in order to Select an Employee. Dates up until Filters are also Unprotected. All System Formula on the "Setup" Sheet are Protected by default. 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' or 'Protect 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 Planner #back to top

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_v3.2.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'.  Reenter the Password to modify if prompted again 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

6. Close the FIle

Now when you reopen the File only the Administrator can edit the contents, whereas any other person ie. an Employee can see the contents as Read-only

						

Duplicate Names #back to top

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 and the second 'John Smith' will get an incremental suffix ie. 'John Smith [2]'. 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 #back to top

The Holiday Planner is setup for 100 Employees. To reduce the Holiday Planner for fewer Employees, you need to do the following:


Before you start: Unprotect the "Summary" and "1" through to "12" Worksheets

1. Delete the unneccesary Rows 
   - left click on the "1" Worksheet"
   - hold down the SHIFT key and left-click on the "12" 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 "Setup" Worksheet
   - left-click onto the "Setup" Worksheet
    - scroll down to where you see 'Number of Employees' and change this to the new Number of Employees (check this is correct by numbering your Employees in Column "A" on the Summary Sheet
  
3. Click Name Manager on the Defined Names Group of the Formulas Tab on the Ribbon.  Double-click the Employees Defined Name and in the "Refers to" box Select the new reduced Range for the Employees (this will default to Sheet "1" or the new Sheet Name if you have changed it) - ensure you Select all the way down to the last Cell with the Row Height of 24  
 
Now: Protect the "Summary" and "1" through to "12" Worksheets again
						

Extending the Holiday Planner for More Employees #back to top

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 "1" through to "12" Worksheets

1. Extend the Ranges 
   - left click on the "1" Worksheet"
   - hold down the SHIFT key and left-click on the "12" 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 "B105" through to "BA105" (this will include any hidden Formula in the "1" Sheet)
   - grab the tiny handle in the Cell "BA105" and drag down for as many Rows as you want
   - select the new Rows that you just added, right-click and change the Row Height to '24'

2. Adjust the Number of Employees in the "Setup" Worksheet
   - left-click onto the "Setup" Worksheet
   - scroll down to where you see 'Number of Employees' and change this to the new Number of Employees (check this is correct by numbering your Employees in Column "A" on the Summary Sheet)
      
3. Click Name Manager on the Defined Names Group of the Formulas Tab on the Ribbon.  Double-click the Employees Defined Name and in the "Refers to" box Select the new Range for the Employees (this will default to Sheet "1" or the new Sheet Name if you have changed it) - ensure you Select all the way down to the last Cell with the Row Height of 24  

Now: Protect the "Summary" and "1" through to "12" Worksheets again
						

Screen Shots #back to top

Holiday Planner Summary Sheet - filtered by Date to show all Codes and aggregated values up until 8th November 2019 (Please Note: from Version 3.2 the Row Banding has been removed to improve speed)

Holiday Planner Summary Worksheet Screen Shot

The same Holiday Planner Summary Sheet but shown in Google Sheets and including a second Keiko Jensen Manager (Please Note: from Version 3.2 the Row Banding has been removed to improve speed)

Holiday Planner Google Sheets Summary Worksheet Screen Shot

Holiday Planner Calendar Sheet - showing a duplicate Employee Keiko Jenssen

Holiday Planner Calendar Worksheet Screen Shot

Holiday Planner Report Sheet - detailing 2 and a half Days Booked Holiday and 1 and a half Days Booked Training for Xerxes Miles

Holiday Planner Report Worksheet Screen Shot






We normally don't work on weekends but if an Employee does, how I can mark it on the planner. Sometimes we pay them for that day and sometimes its added to their entitlement?

Regarding Weekend working, you can do either, but not both at the same time automatically, so adding 1 Day on a Saturday for Keiko and setting ‘Use Weekend in Lieu Calculation’ to TRUE means that Keiko working a Weekend will have that day or a factor of that day added to their entitlement. Or set to FALSE meaning that it is deducted from their entitlement. Otherwise, to do both manually, set the Setting to FALSE and then add an adjustment into the Summary Sheet in one of the 5 spare Columns. So I booked 2 Saturdays for Keiko, but I only want one of these to be an addition to their entitlement whereas the other I just want to pay normally, so I added ‘1’ to my adjustment in “D7” and then Unprotected the Sheet (Unhide the spare Columns) and changed the Formula in “Q7” (flick down) to:

=INDIRECT("O" & ROW() & ":O" & ROW())-INDIRECT("P" & ROW() & ":P" & ROW())+INDIRECT("D" & ROW() & ":D" & ROW())


Can I change the Worksheet Tab Names to the Months in my language?

Yes, you can - double-click on the Tab Name or use right-click->Rename and type a different Name



Can I change the Days of the Week to the Days of the Week in my Language for the 'Calendar' Worksheet?

These will update automatically when you select your Language on the "Setup" Sheet



We run our Calendar from April to March - how can I use the Calendar like this?

Follow the instructions for Splitting the Calendar



Can I Insert Columns into the 'Summary' Sheet

There are already 5 hidden Columns for you to use to add additional workings "D:H", but No, not without changing 2 Formula in Columns "O" and "Q" as these allow movement of the Employees without #REF errors. Make a Note of the Column Headers that these Formula reference and then after inserting Columns update these to the Columns where the Headers now reside



Does this Software work in Google Sheets/Docs?

Yes it does. The only issue is that Protection will need to be applied manually by yourself as this does not work when the Planner File is opened in Google Sheets



I can't Filter Dates on the Report, Summary or Calendar Sheets in Microsoft Excel Online?

Depending on the location of the server online you may need to enter the Date in a different Format. For example if I open Excel in UK on my PC I can enter 12/01/2019 in the Summary Date up until Box to restrict Aggregation to before and including the 12th of January 2019, however if I upload the File to Microsoft Online it then reverts to the Danish equivalent for the Date meaning I need to enter 01/12/2019 to restrict the Aggregation to before and including the 12th January 2019 because Excel recognises the Date Format as Month first, then Day, then Year



Does it matter in what order I add Bank Holiday Dates when maintaining the bespoke Bank Holiday list?

No this does not matter – Holiday Planner Dates for a bespoke list can be added in any sequence



Can I copy all of the data from Version 3.2 into Version 3.6+?

Yes without using a Macro, open both Versions. You do this manually by Copying the Employees from the Summary Sheet in Range B6:L105 in Version 3+ and Pasting as Special Values over the Employees in the Summary Sheet in Version 3.6. Then Copy each Individual Calendar data in Version 3+ by Copying the Codes from M6:AW105 and Pasting as Special Values over the respective Sheets in Version 3.6. For Version 3.3+ there is an Automatic Updater Macro Workbook to copy everything using a Macro - download it here (HP_Updater_v1.xlsm)



Can I use the Holiday Tracking Codes to Track 2 Holidays and count them both as 1 Day?

Yes but with some modifications. You will need to set up your Holiday Names and Codes first. Then you need to use REVIEW->Unprotect on each of the Month Sheets. In the Booking Header Formula, remove the 0.5 and then flick the Formula down the Range. Do this for all of the Months - the example below is just for the Month Sheet named '1':

Formula is this:
=IF(C6<>"",COUNTIFS($M6:$AW6,HP.HolidayCode,HP.1,IF(HP.SummaryFilter<>"","<="&HP.SummaryFilter,"<"&EDATE(HP.CalendarStartDate,HP.StartMonth)))+COUNTIFS($M6:$AW6,HP.HalfDayHolidayCode,HP.1,IF(HP.SummaryFilter<>"","<="&HP.SummaryFilter,"<"&EDATE(HP.CalendarStartDate,HP.StartMonth)))*0.5,0)

Change to:
=IF(C6<>"",COUNTIFS($M6:$AW6,HP.HolidayCode,HP.1,IF(HP.SummaryFilter<>"","<="&HP.SummaryFilter,"<"&EDATE(HP.CalendarStartDate,HP.StartMonth)))+COUNTIFS($M6:$AW6,HP.HalfDayHolidayCode,HP.1,IF(HP.SummaryFilter<>"","<="&HP.SummaryFilter,"<"&EDATE(HP.CalendarStartDate,HP.StartMonth))),0)

You will also need to amend the Tracker Sheet if you are using it. Unhide Columns "AI:RO" and then change the Formula in Column "AN" so that it looks like the one below from 0.5 to a 1 and flick down the Range. Repeat this for Columns "BF, BX, CP and DH" with the respective Formula:

Formula is this:
=IF(HP.RptOn,IF(OFFSET($AL$5,0,MATCH(AL11,$AL$4:$RM$4,0)-1,1,1)=HP.HolidayCode,1,0.5),"")

Change to:
=IF(HP.RptOn,IF(OFFSET($AL$5,0,MATCH(AL11,$AL$4:$RM$4,0)-1,1,1)=HP.HolidayCode,1,1),"")
                        

Videos #back to top

Video of Holiday Planner Version 3.1 (the latest Version is 3.2 with minor changes to improve speed)

Here I run through some of the settings, adjusting Employees, adding Codes and the new Date Filters

Support #back to top

Please remember you have purchased very affordable Software and you have not paid for a full-time Software design agency - I am but one man. Occasionally I may help with small tweaks, but these requests will be put on a much lower priority due to their nature. You have not PAID for Support, Support is 100% optional and I provide it for your convenience, so please be patient, polite and respectful

Support (limited) for my Software includes:

* Responding to questions or problems regarding the Software and its features
* Fixing valid (replicated) bugs and reported issues for the VERSION I HAVE WRITTEN

Software support does not include:

* Customization and installation services
* Support for third party software or ANY kind of development whatsoever

Before seeking support, please...

* Make sure your question is a valid Software Issue and not a customization request
* Make sure you have read through the documentation and any related video guides before asking support on how to accomplish a task
* Make sure to double check the Software FAQs or online documentation
* Ensure that you access to the VBOM is allowed and that Macros can run in Excel
* Make sure to provide 'proof of purchase' and state the name / version of the Software that you are having issues with when requesting support by Email or via Facebook

How to get Support

Contact Mark Kubiszyn on the Email address provided when you purchased the Software, including the Order Number
Contact Kubiszyn.co.uk via our Facebook Page - remember to be patient, if there has been an issue with your download, I will always respond within 48 hours and will Email you the File directly if neccessary or via Messanger. For other issues the response time may be considerably longer and I may choose to respond to specific questions only (as is my right), depending on what has been asked

Future Builds #back to top

The below items have been identified as possible inclusions in future builds of this Planner

Future Build Items:

* There are currently no items identified



Bug Fixes #back to top

The below items have been identified as possible bugs

Bug Fixes:

* There are currently no bugs identified


Version History (Changelog) #back to top

You can find the version history in the Code Module for any Macro-enabled Software or read more information below. The latest Version is always shown first


Changelog

                                
06.01.2020 (Version 3.6)                                
Fix for the 'moved 2020' May Bank Holiday which was still not being pulled through onto the Calendar when using the automatically calculated UK Bank Holidays
Cell "C138" on Setup changed to:
=IF(HP.Year=2020,DATE(2020,5,8),DATE(C133,5,1+((1-(2>=WEEKDAY(DATE(C133,5,1))))*7)+(2-WEEKDAY(DATE(C133,5,1)))))
Cell "C139" on Setup changed to:
=IF(HP.Year=2020,DATE(2020,5,25),IF(MONTH(C138+28)=5,C138+28,C138+21))

Fix for Substitute UK Christmas Bank Holidays
Cell "E145" on Setup changed to:
=IF(HP.UseBespokeBHList,"",C145)
Cell "E146" on Setup changed to:
=IF(HP.UseBespokeBHList,"",C146)
Cell "C145" on Setup changed to:
=IF(D142=7,C143+1,IF(D142=6,C143+2,IF(D142=1,C143+1,"")))

The Weekend Conditional Formatting needed moving up as the shaded Days from earlier and later Years were not correctly greying out the Font

                                      
                                
26.12.2019 (Version 3.5)                                
Add back the Conditional Formatting for the 2 additional Colour Codes for Misc1 and Misc2 including the Legends
Fix a bug in the Hours Worked/Absence Hours where Sheet 6 was doubling up as the Formula on the Summary Sheet, had Sheet '6' reference in twice 

                                      
                                
16.12.2019 (Version 3.4)                                
Change the Formula for Weekdays, Short Weekdays, Months and Short Months from a hard-coded Year ie. 2019 into the current Holiday Planner Year ie. '=PROPER(TEXT(DATE(YEAR(HP.Year),1,1),HP.LanguageCode&"dddd"))'
Add a Formula fix for the Year 2020 for UK-based automatically generated Bank Holidays as 2020 early May bank holiday - known as May Day, will be moved by four days to coincide with the 75th anniversary of VE Day

                                      
                                

01.12.2019 (Version 3.3)                                
Speed up the Formula that pulls through the Employee Names from the Summary Sheet for the first Individual Calendar Month Sheet.  Adjust the 11 other Individual Calendar Month Sheet Formulas to simply reference the adjacent Individual Calendar Month Sheet (this could also be set to the first Individual Calendar Month Sheet if required)                                
Allow the Summary Sheet to be renamed.  A Name can now be entered into the Setup Sheet that matches the new Sheet Name - now all of the Sheets can be renamed if required                                
Bug fix for the ability to maintain a list of Employees and Holiday information that will not get pulled into the Individual Calendar Month Sheets or appear in the Calendar Drop-down using a Hashtag '#' prefix - the last value Holiday Sacrifice was not working correctly - wrapping the subtraction for Column "L" using SUM() fixes the issue     
I have added a method of showing a departments entire holiday with measurements.  A Subtotal in value and percentage on the Summary Sheet has been added so that you can easily filter by Dept. ie. Staff and see the amounts and % of Staff off for any point of the Year at an aggregate level
I have added a Version Updater File, HP_Updater_v1.xlsm, which allows you to copy the Employee Codes from one version to another
                                
                                      
                                
                                
26.10.2019 - (Version 3.2)
Remove 8 Conditional Formats on each Individual Calendar Sheet including the Row Banding - additional Codes will not be coloured on the individual Calendar Sheets to improve speed 
Remove the Conditional Formatting Row Banding on the Summary Sheet
Change the Conditional Format Formula to point to 1 Bank Holiday List which will be both the UK List and a Bespoke List, meaning either can be maintained or both can be maintained (nested Conditional Format removed) - this applies to the Individual Calendars and the main Calendar
Bank Holidays and Weekends will always be shown on the Calendar and individual Calendar Month Sheets - option to display has been removed (improves the Conditional Format speed)
Extend the Formula in the Top part of the Calendar to include Month 7 for the very last Column to capture spill-over Month Days that would otherwise not be picked up (bug in 3.1)
Use a Hashtag in the Dept. Employee Name and some Numeric data slots to prevent the information being transferred onto any of the Individual Calendar Month Sheets

                                      

20.07.2019 - (Version 3.1)
Bug fix for when the Holiday is in rotation ie. April until March - Formula change

                                      


01.06.2019 - (Version 3)
Complete rewrite to allow the Planner to work with Google Sheets and Microsoft Online
The Summary, Calendar and Report Sheets are now Filterable by an up until Date
The Month Sheets no longer require moving for rotation or Year Split Calendars
Language settings now apply for Dates so the Weekdays, Months etc. can be displayed in UK, Korean, French etc.

                                      

                                
01.03.2019 - (Version 2.3)  Bug Fixes for the Formula on the Calendar and Date Tracker Worksheets to 
allow calculations with Employees with the same name to be correctly reported.  Either Absence Hours 
or Hours Worked can now be tracked on the Date Tracker Worksheet via 2 Formula changes.  The 
replacement of the Formula in the Jan-Dec Worksheets to correctly calculate Holiday
entitlement in the event of a Split Calendar Year ie. April to March.  Removal of the Defined Names 
DaysRemaining for Jan-Dec as they are no longer required

                                      

                                
01.11.2018 - (Version 2.2) Allows the renaming of Individual Calendar Worksheets.  Formula now
picks up the Names and these Names are used in the 'Calendar Year' and 'Date Tracker' Worksheets
Additional Formula has been added to the 'Summary' Worksheet to automatically pick up and clean
the Worksheet Names for the Months
Allows you to maintain a list of bespoke Bank Holiday Dates.  Also Bank Holiday Formula
has been updated to use a list of UK Dates with the option to turn off last Years and next
Years Dates from all of the Calendars.  I see a slight speed benefit for the Conditional Formatting
A futher change has been made to the Formula in the 'Summary' Worksheet, so instead of using the
Column to refer to the Index in the Formula, I use 1,2,3 etc. - this allows the insertion of new
Columns

                                      


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