Yearly Planner / Calendar for Office Watch readers


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Here’s a great Excel yearly planner that we’re delighted to share. It’s works for 2020 or any other year you like.

Office Watch friend David Peel from the UK made this workbook and let us share with our readers.

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.

yearly calendar for office watch readers microsoft excel 33832 - Yearly Planner / Calendar for Office Watch readers

Download Year Planner.xlsx

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.

yearly calendar for office watch readers microsoft excel 33833 - Yearly Planner / Calendar for Office Watch readers

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.

yearly calendar for office watch readers microsoft excel 33834 - Yearly Planner / Calendar for Office Watch readers

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.

yearly calendar for office watch readers microsoft excel 33835 - Yearly Planner / Calendar for Office Watch readers

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.

yearly calendar for office watch readers microsoft excel 33836 - Yearly Planner / Calendar for Office Watch readers

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.

yearly calendar for office watch readers microsoft excel 33840 - Yearly Planner / Calendar for Office Watch readers

That’s done from Data | Data Tools | Data Validation

yearly calendar for office watch readers microsoft excel 33841 - Yearly Planner / Calendar for Office Watch readers

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.

subs profile e1563205311409 - Yearly Planner / Calendar for Office Watch readers
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address