Introduction

Introducing Holiday Planner v4.1 for Excel

Holiday Planner · An Excel-based any Year, any Week Day small Business Holiday, Training and Absence Planner that works in Google Sheets, SharePoint and Microsoft Online

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 150 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. Holiday Planner has been lovingly crafted to work with Google Sheets and Microsoft Excel Online

Prerequisites

  • Excel 2010, 2013 & 2016 Version 16 (32bit or 64bit). A Google account if using via Google Sheets. A Microsoft 365 Subscription if using via Microsoft Online
  • Basic to moderate Excel Skills
  • Windows PC · NOT a Mac!
  • No support is provided for customization of this Software

Features

  • 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
  • Multiple Grouped Columns can be used for any Adjustments to any of the Codes or Hours Tracked
  • Conditional Formatting for 8 Monitoring / Tracking Codes
  • A Summary Worksheet collates 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
  • Adjust the weighting of the Half Day Codes so that you can track 2 Codes ie. Holiday Types, hol_1, hol_2 as Full Days. Aggregation Sheets use the new weighting and the Codes are differentiated or made distinguishable on the individual Calendar Sheets when plotted
  • - Filter the Report Sheet by a Date up until to only show Codes until that Date
  • 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 UK Bank Holiday Generator to add your Bank Holiday Dates or maintain your own Country-specific list of Bank Holidays
  • Adjust the Dates to be Language-specific ie. French, Korean or English UK etc.
  • The Holiday Planner features ISO Week Numbers 1-53 for Calendars starting on a Monday
  • Conditional Formatting automatically Formats the Codes, Weekend Days & UK Bank Holidays
  • With a slight modification, the Planner can be used to capture Holiday Hours for specific Employees
  • The Holiday Planner captures half Days for all Codes
  • Use the Updater to Copy Employees and Plot Codes from legacy Versions of the Planner
  • Use the Updater Formatting Tool to create your own Colour Themes and update all of the Planner Conditional Formatting ie. Marks Scheme, Pastel Chalk, Seaside
  • 150 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
  • Extensive Adjustment Sections are provided so you can adjust almost everything, even including Hours Worked in Lieu
  • 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.

Installation

Holiday_Planner_v4.1.zip is ready to use. Download the File after purchase from the link provided. Right-click on the Zip archive and extract all of the Files. Open the File of your choice using Excel

The standard Holiday Planner File is called Holiday_Planner_v4.1.xlsx. A Planner File that is unprotected and uses Node Toggling (Data Grouping) to hide all of the calculations in the individual Calendar Month Sheets is called Holiday_Planner_v4_Unprotected_Toggle.xlsx. This is particularly useful to see a collapsed concise overview of your Employees. In order to use the Node Toggling (Grouping) the Holiday Planner is Unprotected

Other Files may be opened which will have different Conditional Formatting Colour Schemes ie. Holiday_Planner_v4.1_Unprotected_Toggle_Pastel_Chalk.xlsx

There are also 2 more versions, Holiday_Planner_v4.1_50_Employees.xlsx and Holiday_Planner_v4.1_50_Employees_Unprotected_Toggle.xlsx which are limited to 50 Employees only - use these if you only require a limited amount of Employees as they are much faster in operation due to the Conditional Formatting. If you plan to use the Software in Google Sheets, SharePoint or Microsoft Online, then upload the relevant File(s) accordingly

Lastly the HP_Updater_v2.xlsm File allows you to change the Theme Colours for the Conditional Formatting of the Holiday, Training and Absence Codes

Usage

Setting up the Planner

The Holiday Planner Workbook is ready to use - as a visible example, all of the Codes are turned on and are plotted for Month 1 for Keiko Jensen. To begin with go to the Setup Sheet. Underneath the 'Configurable Settings' section, enter a Name for your Planner. Now choose your Language for the Dates that will be used on each Sheet where appropriate - the default is 'English (U.K.)' (language settings work in an Excel client and when using the Planner via Microsoft Online - language settings do not work with Google Sheets). Enter a Calendar Year in the form '2020' etc. and select the Calendar Month that you want to begin the Planner on ie. January (you can split the Planner by entering a Month such as April, for example you may run to a Tax Year Calendar as opposed to a regular Calendar, January through to December). Whenever you split the Calendar, this will be displayed on the 'Summary' and 'Calendar' Sheets to let you know the Planner is in a split Year

Next, select a Start Day for the Planner. This will begin the Planner on a specific Day on the 'Calendar' Sheet. Choose 2 Weekend Days. Weekend Days are highlighted on the 'Calendar' Sheet and the Individual Month Sheets. You can choose a single Weekend Day by setting both Days to the same Day ie. 'Sunday'. If yo plan to track Hours, then set the Tie Tracking to either 'Hours Worked' or 'Absence Hours'. An Adjustment Header can also be changed here to whatever you prefer (used when making adjustments)

Next, you can choose what you want to Track by adjusting the Tracking Names and Codes to Names and Codes of your choice. Codes should be small single digits, but in theory can be any Text - it's best to keep them short ie. 'xx' or 'hol'. You can turn on or switch off the Tracking Codes for the Legends by selecting either '0' for off or '1' for on. Conditional Formats are used to Colour the Codes - the RGB values are also shown here for your convienience (use the Updater Formatting Tool to quickly change these to a scheme of your choice). In total, there are 8 Tracking Codes for 8 Full Days and 8 Half Days. These are used on the 'Summary' Sheet, 'Calendar' Sheet and the Individual Calendar Sheets to Colour the Plotted Codes, highlight the Tracked data or identify the Codes in the Legends

Scroll further down the 'Setup' Sheet. Here is where you should add your Bank Holiday Dates. A UK Bank Holiday Generator is available at the top of the 'Setup' Sheet. Simply enter a Year and the Bank Holidays will be generated for you. Just Copy and Paste these Dates into the list as Special Values. The Planner comes with 5 Years UK Bank Holiday Dates already supplied, however these will not be relevant for people who wish to track Employees for specific Country Calendars

Entering Employees & Holiday Entitlement

Enter your Employees on the 'Summary' Sheet, including their Holiday entitlement and any additional Days or Days Sacrificed etc. in the first 6 Columns, 'Dept.', 'Employee Name', 'Carry', 'Ent.', 'Add.' and 'Sacr.'. 150 Employees can be maintained here without any adjustments to the Formulas in the Planner. Employees can be moved or dragged around in this section within the area where the Cells are not Protected and everything will work fine - the Formulas will not break. 5 additional Columns are available here as spare slots for any additional workings - access these spare slots by using the Toggle (or Data Grouped) Button at the top of the Sheet

Employee Names need not be unique, for example, you can enter a Name that is exactly the same more than once ie. you may have a Staff member called 'Keiko Jensen' and also a Manager with the same Name. Both people can be tracked in the Planner - where you have people who share the same names, then a number will be sufixed accordingly to distinguish the person when choosing them in the Calendar or for reporting ie. 'Keiko Jensen' and 'Keiko Jensen[2]'

You can use the Autofilter to Filter Departments or Employees. Subtotal Formula is shown at the top of the Summary Sheet Section for Holidays. This enables you to quickly see a Percentage of Holidays Remaining per Department etc. The Summary Sheet provides detailed information on all of the Plotted Codes for each Month of the current Year. The Node Toggled version or Grouped Data version, allows you to toggle all of the relevant adjustment Sections of the Planner to Hide/Unhide the adjustments - this version is unprotected. The Summary Sheet is setup ready for Printing

Filtering the Planner by Dates

The Planner can be Filtered by Dates From and up until on the Report, Summary and Calendar Sheets. Enter the relevant Dates and the Planner will update accordingly. For example entering 07/01/2020 on the Summary Sheet will display ONLY plotted information from the 'From' Date up to and including the 'Until' Range. The same methodology applies to the other two Worksheets

The Calendar Sheet

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-Misc5 Days/Half-days booked in a Grid format for the selected Year. A Drop-down Combo Box is provided for you to select the Employee. A Legend is included detailing the Days/Half-days taken for the Date Filter for the selected Employee. 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 2020, would only display the Codes up until (and including) the last Day in January 2020. An overview is also detailed at the bottom of the Calendar. 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. The Calendar Sheet is setup ready for Printing

The Report Sheet

Select the 'Report' Worksheet. This Worksheet displays a detailed Report by Bookings for all of the Codes and Hours Worked/Absence. Select an Employee to view. The Report Sheet allows you to view all Dates Booked from and up until a specific Date. The Report Sheet is setup ready for Printing

Booking Days/Hours on the Individual Calendar Worksheets

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

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 "D5:AH153". 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. All of the Codes have 'Adj. in Lieu' adjstments that will aggregate on the Summary Sheet

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

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 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'

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. 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

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)

Password Protecting the 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_v4.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

Updating the Holiday Planner Colour Theme

Open the HP_Updater_v2.xlsx File. Ensure that your Holiday Planner is in the same Folder as the Updater and type the Name of your Holiday Planner in Cell "C7" ie. Holiday_Planner_v4.xlsx. Next, choose a Colour Swatch and Copy the entire Swatch, Pasting as Formats over the Colours Swatch on the Left-hand side. Press the 'Update Theme Colours' to finish the process and change the Colour Theme of your Holiday Planner. You can change the Colours manually if you wish - do not edit the Codes below in the System Section. The Codes in the Colour Swatch are for information only

Updating a Legacy Holiday Planner Verison for Employees and the Codes

Open the HP_Updater_v2.xlsx File. Ensure that your Holiday Planner is in the same Folder as the Updater and type the Name of your Holiday Planner in Cell "C7" ie. Holiday_Planner_v4.xlsx. Next, type the Name of your Legacy Holiday Planner File ie. 'HolidayPlanner_v3.6.xlsx'. Press the 'Update' Button to finish the process of updating the Employees and the Codes. Please Note the Updater_v2 File should only be used to convert Legacy Versions 3.4, 3.5 & 3.6 to 4. If you are using an earlier Version than 3.4 then this cannot be updated into 4

Reducing the Holiday Planner for Fewer Employees

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

Before you start: Unprotect all of the Worksheets. A quick way of doing this is File->Info and under Protect Workbook, click Unprotect for each Worksheet. Then perform step 1

1. Delete the unneccesary Rows on the Individual Calendar Worksheets
- left click on the '1' Worksheet or the first Worksheet if you have renamed it
- hold down the SHIFT key and left-click on the '12' Worksheet or the last Worksheet if you have renamed it
- 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'
- click on any other Worksheet Tab to deselect everything

Please Note: you will need to Delete one more Row on the Summary Worksheet than the rest of the Individual Calendar Worksheets, so on the Summary Worksheet, on the Row Headers on the left-hand side, on the last Row, highlight it and select 'Delete'

That's it. Now Protect all of the Worksheets again by clicking 'Protect Sheet', OK, on the 'Protect' Group of the 'Review' Tab on the Ribbon if you need Worksheet Protection. you can add a Password here for any of the Worksheets if you want to

Extending the Holiday Planner for More Employees

The Holiday Planner is setup for 150 Employees. To extend the Holiday Planner for more Employees, you need to do the following:

Before you start: Unprotect all of the Worksheets. A quick way of doing this is File->Info and under Protect Workbook, click Unprotect for each Worksheet

Select all of the Calendars Sheets 1-12, then holding down CTRL key, left click Summary Sheet to Group the Sheets
Click on the Summary Sheet Tab and scroll down to the bottom
Select the last Cell right across the Sheet to EQ and drag down as far as you want. Click on Calendar Sheet to deselect the grouped Sheets
Then adjust the Data Validation (Cell "I1") on the Calendar Sheet for the Employee for example: ='1'!$BY$5:$BY$250 where 250 is the last Row down on Sheet 1
Unhide the hidden Columns on the right-hand side of the Calendar Sheet and select Cell KT2, change the Range to the new Row: =MATCH(I1,'1'!BY5:BY250,0) where 250 is the last Row down on Sheet 1
Now you should see everyone from first Employee until the last one at the Bottom of Sheet 1 on the Calendar Sheet and be able to plot Holidays etc.

That's it. Now Protect all of the Worksheets again by clicking 'Protect Sheet', OK, on the 'Protect' Group of the 'Review' Tab on the Ribbon if you need Worksheet Protection. you can add a Password here for any of the Worksheets if you want to

Calculate AM & PM Shifts into Days

You may want to enter both AM and PM Shift values as hours and then calculate these into Days ie. working on a Day being 8 Hours. To do this you can edit the Planner on each Individual Calendar Worksheet and then update the Summary Sheet. Start by Unprotecting the Summary Worksheet and all of the Individual Calendar Worksheets using 'Unprotect Sheet" on the "Protect" Group of the "Review" Tab on the Ribbon

Add in a Formula to allow you to enter AM/PM numbers into Cells (these 2 Formula will calculate AM and PM
In Sheet (1) in Cell “AP4” type AM Shift and in Cell “AQ4” type PM Shift and in Cell “AP3” type Days worked by Shift

In Sheet (1) in Cell “AP5” enter =SUMPRODUCT(--IFERROR(LEFT(D5:AH5,FIND("/",D5:AH5)-1),0))/8 and flick down
In Sheet (1) in Cell “AQ5” enter =SUMPRODUCT(--IFERROR(RIGHT(D5:AH5,FIND("/",D5:AH5)-1),0))/8 and flick down

Now type some Shift values for an Employee ie. in Cell "F6" type 2/6 and in Cell "G6" type 6/4. The Formula will now add up the AM Shifts to be 1 Day and the PM Shifts to be 1.25 Days, Here I add some more split shift values for Quincy Barrera

Calculating Hours as AM and PM Shifts in the Individual Worksheets

Then you can aggregate the Daily amounts on the Summary Sheet

In Sheet (Summary) in Cell “AC3” type AM Shift and in Cell “AD3” type PM Shift

You will need to manually adjust each Cell here for each Month
In Sheet (Summary) in Cell “AE4” enter =ROUND('1'!AP5,1)&"/"&ROUND('1'!AQ5,1) and flick down

In Sheet (Summary) in Cell “AC4” enter =SUMPRODUCT(--IFERROR(LEFT(AE4:AP4,FIND("/",AE4:AP4)-1),0)) and flick down In Sheet (Summary) in Cell “AD4” enter =SUMPRODUCT(--IFERROR(RIGHT(AE4:AP4,FIND("/",AE4:AP4)-1),0)) and flick down

Summary Days as AM and PM Shifts in the Individual Worksheets

Screen Shots from Holiday Planner Version 4

The Calendar Sheet (various Colour Themes are available)

The Individual Calendar Sheet

The Summary Sheet

The Report Sheet

The Summary Sheet with some Dummy Plotted Codes for 2020 in Pastel Chalk Colour Theme

Theme Colours - use the HP_Updater_v2.xlsx File to change your Holiday Planner Colours

FAQ

Q. 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?

A. To give the Employee back 1 Full Day or 1 Half-day make an adjustment to the WE in Lieu Column on the Individual Calendar Sheet. Then the Summary Sheet and Calendar will reflect the correct Holidays Remaining for the Year. There is also another Holiday Adjustment Column next to the WE in Lieu Column that can be used to make any additional adjustments - this is also then reflected on the Summary and Calendar Sheets


Q. How can I mark on the Planner so that I can pay an Employee for Hours Worked in Lieu

A. To do this use the Adj. in Lieu Column on the Individual Calendar Sheet. For example Yoshi works 40 hours in a Week (8 per Day). On Saturday she worked 4 Hours. To pay her double for the Saturday work, simply add 4.00 to this Column - the Summary and Calendar Sheets will reflect this adjusment in lieu of her Saturday working for payment - she will now receive 48 Hours pay. If you want to give it back to her as Holiday then mark 0.5 (Half-Day) in the WE in Lieu Adjustment for Holidays


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

A. Yes, you can - double-click on the Tab Name or use right-click->Rename and type a different Name. Tab Names for the Individual Calendar Months can be any legal Name


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

A. These will update automatically when you select your Language on the 'Setup' Sheet. Please Note: Language Settings do not work in Google Sheets


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

A. To split the Calendar to use from April to March instead of a normal Year from January to February, change the 'Start Month' to April on the 'Setup Sheet'. Click on the Tab '1' Sheet (assuming that you have not changed any of the Tab Names) or the first Sheet after the 'Calendar' Sheet (assuming that you have not changed the order of any of the Sheets). This Sheet is now April and your Calendar runs from April to March. If you click on the 'Summary' Sheet, you shoud see the split Year '2020/2021' (assuming your Calendar Year is 2020)


Q. Can I Insert Columns into the 'Summary' Sheet

A. Additional workings Columns have already been added to the Summary Sheet for you to use - Columns "H:L". Inserting Columns before Column "M" will mean that oyou have to adjust the 2 INDIRECT Formula in Columns "M" and "O" that keep the Employees in order if anything is moved around (the Column letters will need to be incremented accordingly): =SUM(INDIRECT("D" & ROW() & ":F" & ROW()))-@INDIRECT("G"&ROW()) and =@INDIRECT("M"&ROW())-N5+P5


Q. Does this Software work in Google Sheets/Docs?

A. 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 and the Language Settings will not work, however Google Sheets allows language translation


Q. I am using the Holiday Planner on a laptop at work and I can't Filter Dates on the Report, Summary or Calendar Sheets in Microsoft Excel Online?

A. 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


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

A. No, you can enter Bank Holiday Dates in any order for any Years


Q. Can I copy all of the data from legacy Versions to the latest Version?

A. Copying Employee data and plotted Holiday Codes can only be done from Version 3.6 to Version 4 using the HP_Updater_v2.xlsx File. You can also do this manually by Copying the Employees from the Summary Sheet in earlier legacy Versions and Pasting as Special Values over the Employees in the Summary Sheet in a newer Version. THe same can be done for the Individual Calendar Sheet Code data for earlier legacy Versions


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

A. You can change the Holiday Code (or other Codes) Half-day weight to allow tracking of the Half-day Holiday Code as a Full Day. Change the wweighting to 1.0 and then change both Code 'Full Days' and 'Half Days' to 'Hol1' and 'Hol2'. Now go and enter these Code Names into the Individual Calendar Sheets. The Colours will be different and both Days will count as 1.0. Any aggregated data on the 'Summary' and 'Report' Sheets will capture the Codes weighting as 1.0 respectively


Q. How do I change the Conditional Formatting Colours?

A. Use the HP_Updater_v2.xlsx File to change the Colour Theme/Scheme. Open the File. Enter the Name of your Holiday Planner in Cell "C7" and in Section 2. Copy and Paste a Theme as FORMATS over the Cells in "B17:B32". Press the 'Update THeme Colours' Button to change the Conditional Formatting for the plotted Codes and Legend entries


Q. I turned off a Code but it is still Coloured when I enter it?

A. Turning off the Codes does not prevent the Conditional Format from Colouring entered Codes that match! If you turned off the Code, then why are you entering it? Turning off Codes simply removes them from Legends and allows the complex reordering of the Codes


Q. One of my early Holidays is not showing on Reporting?

A. Ensure that the From Date is set earlier than the 1st of the Year ie. if in 2020, and you have a Holiday in 01/01/2020, set the From Date to 31/12/2019


Q. I removed all Holiday Codes, but Holidays Rem. Is showing higher than entitlement?

A. This is because you may still have Holiday Adjustments for Bank Holidays in Lieu or Weekends in Lieu – check for this in all of the individual Calendar Sheets


Q. I am a German user and setting up the Holiday Planner does not allow me to set the Language to German - I see 'Ddd' in the Start Day and Weekend Day1/Day2 Drop-downs after setting the language. Is there a workaround for this issue

A. Yes please refer to this link on Stack Exchange. You can try this. [1] Review->Unprotect the 'Setup' Sheet. If you see the Start Date with a period or fullstop in it, try over-typing it ie. 01.01.2020 with 01/01/202 to see if this fixes the issue. [2] For the Start Day Picker Drop-down, scroll down and change the Formula here to use “ttt” ie. You have to use german TAG t instead of english DAY d if your locale is set to german, for example =TEXT(A1;"ttt"). [3] You could also try to use ; semi-colon in any Formula. For German locality I believe you need to replace commas with semi-colon, so the formula would be: =TEXT(A1;"ddd")


Holiday Planner Version 4 for Google Sheets

This is a short video of Holiday Planner version 4 being used in Google Sheets. In this video I restrict the Codes to Holiday, Absence and Training. I then plot some Holidays, Absence and Training Dates in Sheet 1 (January) which I then display on the Calendar and Report Sheets · view this Holiday Planner video on YouTube

Updating the Colour Theme for Holiday Planner Version 4

This is a video of how to update Holiday Planner version 4 with a new Colour Scheme. In this video I update the current Planner with a Pastel Chalk Theme and then another custom Theme · view this Holiday Planner video on YouTube

Support

Support is 100% optional and I provide it for your convenience, so please be patient, polite and respectful

Support for my Software

  • 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

  • Make sure your question is a valid Software Issue and not a customization request
  • Make sure you have read through the FAQ's, online documentation and any related video guides before asking support on how to accomplish a task
  • 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
Remember to be patient, if there has been an issue with your download, Mark will always respond within 48 hours and will Email you the File directly if neccessary. 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

* 13.03.2020 - I have now added a smaller 50 Employee File to the Zip for when more than 50 Employees is not required

Future Builds

* There are currently no bugs identified

Changelog

You can find the version history in the Code Module for any Macro-enabled Software or read more information on the status of each release
- the latest Version including a description of any changes made is always shown first

23.02.2021 - (Version 4.1)

Renamed the main Holiday Planner v4.1. Rolled the Holiday Planner into 2021 and updated it for the next 3 Years of UK Bank Holidays. In 2021 there is a substitute Christmas Day for the 27th that was not showing for version 4 and likewise for January of 2022 as 03/01/2022 is a substitute New Year day. I have updated all 7 of the different Holiday Planner examples for the next 3 Years and adjusted the report dates to begin in 2021. I removed 2 older example Files

09.03.2020 - (Version 4)

- Complete rewrite of the Holiday Planner with a new Major release version 4. Release is now a zip file that includes a standard Version and an unprotected version with Toggle Nodes for Grouped Data. Also included are a variety of Colour Themes and the legacy Version 3.6
- An updater tool (HP_Updater_v2.xlsx) that can be used to copy Employee data is now shipped with the Software as standard
- A Theme Colour changer is also included to change the Conditional Formatting of all of the Plotted Codes and Legend entries. You can choose from a variety of shipped Colour Themes or create Themes of your own

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 Month 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

05.03.2016 - (Version 1) release