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
You will need the following Excel Version & Prerequisite to use this Software
No support is provided for customization or development of this Software
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
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
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" 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:
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
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
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
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
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
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)
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
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
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
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
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
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
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 '. 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
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
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
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),"")
Here I run through some of the settings, adjusting Employees, adding Codes and the new Date Filters
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
The below items have been identified as possible inclusions in future builds of this Planner
The below items have been identified as possible bugs
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
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