Yearly Planner / Calendar for Office Watch readers

, , , , , , , ,

Here’s a great Excel yearly planner that we’re delighted to share. It works for 2023 or any other year you like and 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.

Year Planner

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.

Download Year Planner with Public Holiday.xlsx (a mere 17kb)

  • 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>. Always check all incoming files (Windows Defender will do it automatically).
  • The worksheet works in all modern Excel’s. 
    • Excel 365 for Windows and Mac.
    • Excel 2021/2019/2016 for Windows/Mac.
    • Excel app for Apple iPad/ iPhone
    • Excel app for Android,
    • 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 .

Conditional Formatting

The cell might be changed according to one of the conditional formatting rules.

There are two rules (the third is a special case duplicate):

Weekends

=WEEKDAY(DATE($A$1,MONTH($A2),B$1),2)>5

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

No Date

The black cells appear when there’s no real date (for example 30th or 31st Feb).  Month() returns a number for each month.

=MONTH(AD3)>2

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.

Year Selector

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.

Public Holidays

David’s addition is a quick way to highlight Public Holidays or any other special days.

On the Year List tab is a list of holidays, we’ve added some for 2023 but you’re welcome to add some more.

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.

Make your own Yearly calendars and more in Word
Easy 2023 calendars in Microsoft Word
2023 Calendars in your PowerPoint slides

Office Watch has extensive help to make Calendars in Word, Excel, PowerPoint and Outlook.