Office Calendars

4 Any Year, Office Calendars with UK Bank Holidays for Excel

Version 1
16Oct 2016

Office Calendars Documentation

By: Mark Kubiszyn
Office Calendar1 Excel Screenshot

Office Calendar2 Excel Screenshot

Office Calendar3 Excel Screenshot

Office Calendar4 Excel Screenshot


Here are 4 Excel-based, any Year Office Calendars (*.xlsx), featuring Julian Calendars with UK Bank Holidays, ISO Week numbers, Week Days and Dates. 2 Calendars are in a Grid format and go across the Page split into 6 Month sections and the other 2 Calendars are in standard Calendar format of 4 Rows of 3 Months. The Office Calendars allows you to quickly format and Print a Calendar with your Holiday Days, Training Days and Date Ranges using simple Cell Styles, Fill Colours or Lists of Dates.

All of the The Calendars are perpetual, simply press the Spin Button to increment or decrement the Years. Options available include viewing Weekend Days, Bank Holiday Days or Calendar Month start Weeks as Conditionally Formatted Cells and the choice of starting the Calendar on a Sunday or on a Monday. To clear the Calendar, simply highlight the Cells and select the Calendar Style. To highlight Calendar Days, use Cell Fill or the Styles already added to the Office Calendar Workbook - these include 'Holiday', 'Training', 'Misc1' and 'Misc2' Cell Styles. If you are using the Lists to plot the Dates, fill in the Dates as single Dates or Date Ranges ie. 13/03 to 15/03 and the Conditional Formatting will highlight the Cells.

The Calendars are ideal to Print and display next to your computer in the office. To Print the Calendar, simply press CTRL+P - the Print Area is already setup, but you can adjust the Print settings as you wish

If you don't like the Colours for the Cell Styles or Conditional Formatting, simply change these to whatever you like

The File comes with the Blue Background as standard (a single Style called 'Background') - see below for how to quickly change this to white if you do not wish to Print the coloured background

FEATURES

- 4, Perpertual Julian Calendars - change the Calendar to any Year & everything adjusts to fit
- Maintain Lists of Events as single or Date ranges & the Calendars will highlight the Cells
- 2 different Calendar views, Grid format across the Page split into 6 Months or the standard 3 by 4 Months in Columns
- Start any of the Calendars on a Sunday or a Monday (ISO Weeks will only be shown correctly when the start of the Week is set to Monday)
- UK Bank Holidays are automatically populated using Conditional Formats (with option to turn off)
- The Calendars automatically adjusts to handle the Leap Year in February
- The Calendars features ISO Week Numbers, Day of Week & Date
- Conditional Formatting automatically Formats the Weekend Days & UK Bank Holidays (with option to turn off)
- The Calendars are easy to setup and adjust for a new Year, even the ones that use Lists
- All of the Calendar backgrounds can be changed quickly by modifying a single Cell
- The Workbook does not use any VBA and comes as a (*.xlsx) File


REQUIRED

Excel 2010 or Excel 2013

CALENDAR 1, GRID (simple Styles or Cell Fill)

Select the "Calendar1" Worksheet. This Calendar has a Grid layout split into 2 halves. The ISO Week numbers and Months are shown at the top of each half with Week Days and Calendar Days forming the Rows. Scroll down a little and press the Spin Button to select the correct Year. From the Options choose what Day of the Week you would like the Calendar to start from; either a Sunday or a Monday. At the same time, choose whether you would like to display Weekend Days, Bank Holiday Days and the start Column of a new Month. Now scroll up a little.

To highlight the Cells simply use one of the inbuilt Styles ie. 'Holiday', 'Training', 'Misc1' or 'Misc2' from the 'Styles' Group on the 'HOME' Tab of the Ribbon. If you want to just use a Colour Fill then you can do so either from the 'FONT' Tab of the Ribbon or by right-clicking in a Cell and choosing the Fill Colour. To remove the Format, you must select the Range and then apply the 'Calendar' Style over the top - your Colour Fill will be cleared.

CALENDAR 2, GRID (as Calendar 1, but also uses Lists)

Select the "Calendar2" Worksheet. This Calendar has a Grid layout split into 2 halves. The ISO Week numbers and Months are shown at the top of each half with Week Days and Calendar Days forming the Rows. Scroll down a little and press the Spin Button to select the correct Year. From the Options choose what Day of the Week you would like the Calendar to start from; either a Sunday or a Monday. At the same time, choose whether you would like to display Weekend Days, Bank Holiday Days and the start Column of a new Month. Now scroll up a little.

Use simple Styles or Cell Fill (as Calendar 1 above):
To highlight the Cells simply use one of the inbuilt Styles ie. 'Holiday', 'Training', 'Misc1' or 'Misc2' from the 'Styles' Group on the 'HOME' Tab of the Ribbon. If you want to just use a Colour Fill then you can do so either from the 'FONT' Tab of the Ribbon or by right-clicking in a Cell and choosing the Fill Colour. To remove the Format, you must select the Range and then apply the 'Calendar' Style over the top - your Colour Fill will be cleared.

Use the Lists to plot Date ranges:
To use the Lists to highlight Date ranges scroll across to the right. In 'List1' type some Dates underneath the 'From' and 'To' Headers ie. '12/04' or '12/04/2016' to '13/04' or '14/04/2016' (without the apostrophe's). As long as the Calendar is shown for the same Year that you enter the Date ranges, the Calendar will automatically detect your adjustments and Conditionally Format the Fill Colour 'Red'. Repeat this for the other Lists. If you don't want to use a List, just remove the contents of the Cells underneath the 'From' and 'To' Headers.

If you need to extend the Lists, you will need to adjust the Defined Name or Named Range to reflect your longer List - then the Calendar will pickup any new Date ranges. Click the 'Name Manager' on the 'Defined Names' Group of the 'FORMULAS' Tab on the Ribbon and scroll down until you find the Name of the List (a) that you are looking for ie. 'Calendar.List1a' on the 'Calendar2' Worksheet. Double-click the Name and click the 'Refers to:' Range Picker on the 'Edit Name' Dialog to extend the Range of Cells to include your new Dates. Remember to repeat this for the second List (b) so for our example, you would need to extend 'Calendar.List1b' also.

CALENDAR 3, STANDARD COLUMN (simple Styles or Cell Fill)

Select the "Calendar3" Worksheet. This Calendar has a Standard layout with 3 Columns each consisting of 4 individual Calendars for each Month. The Months and ISO Week numbers are shown at the top of individual Calendars with the Week Days and Calendar Days forming the Rows. Scroll right a little and press the Spin Button to select the correct Year. From the Options choose what Day of the Week you would like the Calendar to start from; either a Sunday or a Monday. At the same time, choose whether you would like to display Weekend Days and Bank Holiday Days. Now scroll left a little.

To highlight the Cells simply use one of the inbuilt Styles ie. 'Holiday', 'Training', 'Misc1' or 'Misc2' from the 'Styles' Group on the 'HOME' Tab of the Ribbon. If you want to just use a Colour Fill then you can do so either from the 'FONT' Tab of the Ribbon or by right-clicking in a Cell and choosing the Fill Colour. To remove the Format, you must select the Range and then apply the 'Calendar' Style over the top - your Colour Fill will be cleared.

CALENDAR 4, STANDARD COLUMN (as Calendar 3, but also uses Lists)

Select the "Calendar3" Worksheet. This Calendar has a Standard layout with 3 Columns each consisting of 4 individual Calendars for each Month. The Months and ISO Week numbers are shown at the top of individual Calendars with the Week Days and Calendar Days forming the Rows. Scroll right a little and press the Spin Button to select the correct Year. From the Options choose what Day of the Week you would like the Calendar to start from; either a Sunday or a Monday. At the same time, choose whether you would like to display Weekend Days and Bank Holiday Days. Now scroll left a little.

Use simple Styles or Cell Fill (as Calendar 1 above):
To highlight the Cells simply use one of the inbuilt Styles ie. 'Holiday', 'Training', 'Misc1' or 'Misc2' from the 'Styles' Group on the 'HOME' Tab of the Ribbon. If you want to just use a Colour Fill then you can do so either from the 'FONT' Tab of the Ribbon or by right-clicking in a Cell and choosing the Fill Colour. To remove the Format, you must select the Range and then apply the 'Calendar' Style over the top - your Colour Fill will be cleared.

Use the Lists to plot Date ranges:
To use the Lists to highlight Date ranges scroll across to the right. In 'List1' type some Dates underneath the 'From' and 'To' Headers ie. '12/04' or '12/04/2016' to '13/04' or '14/04/2016' (without the apostrophe's). As long as the Calendar is shown for the same Year that you enter the Date ranges, the Calendar will automatically detect your adjustments and Conditionally Format the Fill Colour 'Red'. Repeat this for the other Lists. If you don't want to use a List, just remove the contents of the Cells underneath the 'From' and 'To' Headers.

If you need to extend the Lists, you will need to adjust the Defined Name or Named Range to reflect your longer List - then the Calendar will pickup any new Date ranges. Click the 'Name Manager' on the 'Defined Names' Group of the 'FORMULAS' Tab on the Ribbon and scroll down until you find the Name of the List (a) that you are looking for ie. 'Calendar.List1a' on the 'Calendar2' Worksheet. Double-click the Name and click the 'Refers to:' Range Picker on the 'Edit Name' Dialog to extend the Range of Cells to include your new Dates. Remember to repeat this for the second List (b) so for our example, you would need to extend 'Calendar.List1b' also.

SETUP OPTIONS

Setup Options are shown below each Calendar. The Options are detailed below:

Calendar Year
- use the Spin button to change the Calendar Year to any Year that you want (Please note: lists do not persist when the Year is changed - you need to change the Year for the list Date)

Start of Week (Drop-down Combo)
- use to select either Sunday or Monday for the "Calendar" Worksheet to set the start of the Week

Show Bank Holidays on Calendar (Drop-down Combo)
- use to select whether or not to show the Bank Holiday Days on the "Calendar" Worksheet

Show Weekends on Calendar (Drop-down Combo)
- use to select whether or not to show the Weekend Days on the "Calendar" Worksheet

Highligh Column for the Start of a new Month (Drop-down Combo)
- Calendars 1 & 2 only; use to select whether or not to show the highlight Column for the start of a Calendar Month

The rest of the settings are automatically calculated and should not be adjusted or changed in any way - they are used to calculate and Conditionally Format the UK Bank Holidays / Leap Year in February.

Q&A

Q. The Worksheet is Printing in Blue - can I change the Background Colour of the Office Calendars?
A. Yes, simply modify the Custom 'Background' Style. Click on a Cell anywhere outside of a Calendar in any Worksheet. On the "HOME" Tab of the Ribbon, right-click on the 'Background' Cell Style on the 'Styles' Group and select 'Modify...' to modify the Style. Click 'Format...' and on the 'Fill' Tab of the 'Format Cells' Dialog, choose a new Fill Colour. The Background Colour will update and render all of the 'Calendar'Worksheets with your new Colour.

Q. Can I Copy out one or more of the Calendar Worksheets?
A. Yes, select a single Worksheet or hold down CTRL and select multiple Worksheets. Right-click and select 'Move or Copy...' to bring up the 'Move or Copy' Dialog. Select '(new book) from the Drop-down Combo underneath the 'To book:' Header and tick the 'Create a copy' Checkbox at the bottom of the Dialog. Test the copied Calendar and then Save the new Workbook. Please ensure that you leave the Attribution in situ.

CHANGELOG

16.03.2016 - (Version 1) released