Matilda

Select the "Planner" Worksheet. This Worksheet is where you setup your Employee Names and any Section Headers together with the relevant Holiday Entitlement for the selected Calendar Year. You can add Name, Title, Department, Holidays Carried, Holiday Entitlement, any Additional Holidays and Sacrificed Holidays. You can format a Section Header like "Mangement:" by adding a colon ':' sufix after the Text so that any calculations for that Header will be ignored by the Planner. If neccessary you can insert Columns here before the Formula to maintain any other Employee information like Employee Number etc.

The Employee does not have to be unique. On this Worksheet, within the Unprotected Cells, you can add any formatting that you like or move the Cells. The Autofilter can be used to filter by Employee or by Title and Department, useful, for example if you just want to view all Managers or all Staff

Underneath the Monitoring Codes section, you will see the 'Holidays at Start of Year' and 'Holidays Remaining', which will be calculated after you have added the Employee Holiday entitlement. Here, you will also see the calculations for anything booked for 'Holiday', 'Training', 'Absence' and the other 3 additional monitoring Codes (if switched on in the 'System' Worksheet)

Scroll right a little and you will see the first Day of the Calendar. The start Date for your Calendar needs to be setup in the 'System' Worksheet. Bank Holidays are shown in Yellow and the Weekends shown as a diagonal Grey. Enter 'hol' or 'hol half' in a Cell for an Employee - you will see the Cells highlighted dark purple for a single Holiday Day and light purple for a half Holiday Day by the Conditional Formatting for the monitoring Codes. This is where you enter all of your Codes. Try not to use Copy & Paste here as the Conditional Formats may change

Once you have entered some monitoring Codes, scroll back and you will see that all of the calculations have updated for the Codes that you entered. Here is a Screen Shot of the 'Planner' Worksheet:

Select the "Year" Worksheet. On the right-hand side of this Worksheet, you can select any of your Employees from the Drop-down Combo Box. All of the Codes that you entered on the 'Planner' Worksheet will be detailed here in a full Calendar Year as a Grid of highlighted Cells with the Cell Colour matching the Code colours - a lighter shade denotes a Half Day is booked

Again Bank Holidays and Weekend Days are highlighted for you to see alongside the monitoring Codes. You can change the Weekday Cell Format and the Calendar Month Cell Format ie. instead of 'MONDAY' you can display 'MON'. If your language is different to English, the Date calculations should pull through and display the Weekdays and Months in your own language

On the left-hand side you can see 'Entitlement', 'Booked', 'Remaining' and either 'Hours Worked' or 'Absence Hours' depending on the setting in the 'System' Worksheet. Below these are all of the monitoring Codes and additional monitoring Codes, calculations are displayed. Here is a Screen Shot of the 'Year' Worksheet:

Select the "Report" Worksheet. On the right-hand side you can use the Drop-down Combo Box to filter your Employees. The bookings by Month are shown on the Grid with all monitoring Codes highlighted depending on what has been entered on the 'Planner' Worksheet

There are 2 Date Selection Cells "B6" and "B7" where you can change the period or Date Range to be considered. This means you can create a simple report for an Employee that can be Printed to detail the Holidays accrued from one Date to another

Here are 2 Screen Shots of an Employee Keiko Jensen detailing the monitoring Codes by Month for a full Year and then the list breakdown of the full Holiday Days and half Holiday Days

Select the 'System' Worksheet. On this Worksheet you will find all of the configurable and system maintained settings for Matilda - let's have a look at each section

Information & Colours:

In this section there is instruction on what Cells can be edited and what Cells can't be edited. THere are also the Colours used for both full Day Codes and half Day Codes

General Settings:

This is the main section where you can edit most of the Matilda Plannner settings. Here is a list of the settings

Title:                                  Matilda
Sub-title:                              Holiday, Absence & Training Planner
Hours Tracking Type:                    Hours Worked
Calendar Start Date:                    01/01/2018
Number of Employees:                    100
Calendar Year Start Day of Week:        Monday
Show Banding on Calendar Year:          TRUE
Show Banding on Report:                 TRUE
Date Format for Report:                 dd mmmm yyyy
Calendar Year Weekday Format:           dddd
Calendar Year Month Format:             mmm
Week Days for Weekends:                 Sunday
                                        Monday
                                        Tuesday
                                        Wednesday
                                        Thursday
                                        Friday
                                        Saturday
Weekend Day 1:                          Saturday
Weekend Day 2:                          Sunday
Use UK Bank Holiday List:               TRUE
Display Yr-1 Bank Holidays:             FALSE
Display Yr+1 Bank Holidays:             FALSE
						

Monitoring Codes:

This is the main section where you can maintain all of the standard and additional monitoring Codes. Here is a list of the Codes

Holiday:                                hol
Holiday Half-day:                       hol half
Training:                               train
Training Half-day:                      train half
Absence:                                abs
Absence Half-day:                       abs half
	
Config Code 1:                          rest
Config Code 1 Half-day:                 rest half
Name:                                   Rest Days
Turn on Configurable Code:              TRUE
	
Config Code 2:                          comp
Config Code 2 Half-day:                 comp half
Name:                                   Comp. Lv
Turn on Configurable Code:              TRUE
	
Config Code 3:                          misc
Config Code 3 Half-day:                 misc half
Name:                                   Misc
Turn on Configurable Code:              TRUE
						

Bank Holidays (UK List):

This section is where you will see the 3 Years of UK Bank Holidays if you choose to use them

Bank Holidays (Bespoke List):

This section is where you will see the manual Bank Holiday list that you can edit for any Dates for the next 3 Years

Select the "Planner" Worksheet. You will notice that Weekend Days and Bank Holiday Days are automatically Formatted by Conditional Formatting

Select any Cell in the Calendar Grid and type 'hol' without the apostrophe's - the Cell will be highlighted accordingly as a single Day Holiday and the calculations for 'Holidays Booked' will update on the left-hand side of the Worksheet for the current Row. You should see a 1.0 value (remember you can also book half Days). Try entering other Codes to see how the Worksheet calculations update. You can change the Codes for the main 3 monitoring Codes and all of the 5 Configurable Names and Codes to whatever you like by changing any of the Conditional Formats on the 'Planner', 'Year' and 'Report' Worksheets

To clear the Worksheet, simply select all of the Cells in the Calendar Grid from "R7:NS106" and press delete

To Book Absence Hours (or Hours Worked if you have changed this on the 'System' Worksheet 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 'Year' and 'Report' Worksheets

You can filter the Worksheet for any Employee

To split the current Year simply change the Calendar Start Date on the 'System' Worksheet to run from April to March ie. '01/04/2019'. The 'Planner' and 'Year' Worksheets will update accordingly. The 'Report' Worksheet will need the Start and Finish Dates updating manually when you come to generate a new report

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:

1. Select a Worksheet (you can only Unprotect Worksheets one at a time)

2. On the 'REVIEW' Tab of the Ribbon, select 'Unprotect Sheet' from the 'Changes' Group
						

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

						

Once Matilda 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 Matilda.xlsx File

2. File->Save As...

3. When the 'Save As' Dialog Box pops up click the small 'Tools' Drop-down next to the 'Save' Button and select 'General Options...'

4. On the 'General Options' Dialog enter a Password to modify and click 'OK'

5. Continue to save the File either over the existing File as the same File Name or as a new File with a new File Name

						

Matilda 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 'Planner' Worksheet and all of the calculations will still work. 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

Matilda is setup for 100 Employees. Please Note: Matilda requires you to have at least 2 Employees for the Drop-down Combo to work correctly on the 'Year' and 'Report' Worksheets. It is recommended that you leave Matilda as 100 Employees, but if you want to reduce the Planner, for fewer Employees, you need to do the following:

Before you start: Unprotect the 'Planner' Worksheet and apply the Headings

1. Adjust the 'Number of Employees' setting in the 'System' Worksheet

2. Delete the unneccesary Rows in the 'Planner' Worksheet

Now: Protect the 'Planner' Worksheet again and hide the Headings
- that's it, everything will work as it did before
						

Matilda is setup for 100 Employees. To extend Matilda to allow more Employees, you need to do the following:

Before you start: Unprotect the 'Planner' Worksheet and apply the Headings

1. Adjust the 'Number of Employees' setting in the 'System' Worksheet

2. Unhide the Hidden Formula on the 'Planner' Worksheet from Column "OS" onwards

3. Press CTRL+Down Arrow in Column "A" to go to the last Cell.  Select from the last Cell 
in Column "A" until the last Column of Formula ("A106:OZ106" as designed) and drag down
for as many new Rows as you added for more Employees (+6 more for the Headers)

Now: Protect the 'Planner' Worksheet again and hide the Headings 
- that's it, everything will work as it did before
						

The Planner Worksheet:

Booking some Holidays for Xerxes Miles for February

The Year Worksheet:

Viewing those Holidays for the entire Year

The Report Worksheet:

Viewing the Holidays as a Report