Here’s a great Excel yearly planner that we’re delighted to share. It works for 2022 or any other year you like and now highlights Public Holidays in a simple and elegant way.
Office Watch friend David Peel from the UK made this workbook and let us share with our readers. He’s now added an extra so holidays or other special events can be highlighted. Many thanks to David for his work and generous share.
Year Planner uses simple, fairly common Excel features. That means it’s easy to adapt to your own needs. We’ll explain how the workbook is put together so you can make changes.
It’s a single year planner in a single worksheet. Pick a year from the pull-down list at top left (or type in a year) and the days / dates fall into place.
Right-click on the above link then choose ‘Save link as …’ or similar option. The exact wording depends on your browser.
It’s an Excel ‘macro free’ xlsx file with no external data connections. Naturally, we’ve checked it for viruses etc. but you should NEVER take anyone’s word for that <sigh>.
The worksheet should work in all modern Excel’s. Excel 365 for Windows and Mac. Excel app for Android, Excel app for Apple iPad/ iPhone and Excel Online, the browser based version of Excel.
How it works
Year Planner is a clever combination of some Excel date functions and some conditional formatting.
Each day used the Date() function to get the day of the week from the cells in the top row and left column. Date() takes the Year, Month, Day and returns Excel serial number for that day. Excel treats all dates as numbers see The Truth behind Excel Dates.
The cell day name appears via a Custom cell format. Normally a date format would show day, month and year in some order (e.g. dd mm yy ) but in this case the format is ddd returning just the short name for the day.
The look of the cell starts with a cell style: Day Cell Default .
The cell might be changed according to one of the conditional formatting rules.
There are two rules (the third is a special case duplicate):
Uses Weekday() to get a number for the day of the week (The ‘,2’ parameter tells Excel to count the days from Monday = 1 ). If Weekday() is greater than 5 it’s either Saturday (6) or Sunday (7).
The black cells appear when there’s no real date (for example 30th or 31st Feb). Month() returns a number for each month.
Most of the non-existent days have fixed formatting with the ‘No Day’ cell style. This conditional format test for the extra leap day; cell AD3 is 29th Feb.
The third rule is the ‘weekend’ rule applied to the 29th Feb when necessary.
The Year Selector in cell A1 is a pull-down list but any year can be typed in.
That’s done from Data | Data Tools | Data Validation
Source: the selection of years is from a table on the Year List tab.
On the Error Alert tab, choose ‘Information’ . This allows another value to be typed in, optionally with an alert message to warn the user.
David’s 2022 addition is a quick way to highlight Public Holidays or any other special days.
On the Year List tab is a list of holidays.
The first column of dates is the only active part of the list. The labels in the second column are just to explain what each date represents. The list can be in any order and extended for more holidays or other years.
The rest of the Excel magic is in Conditional Formatting.
Match() checks if the cell date is one of the listed holiday dates. If it is, the formatting is applied to the cell.
Of course, edit the Holidays list to suit your location and the Format is also up to you.