Year Planner calendar, Improvements and Extensions


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

The Year Planner Excel workbook download is just the start.  Here are many suggestions for changes to improve or customize the download.

We have tips for changing the headings, overall look, change financial year, highlight public holidays, change the look of a day in the week, change weekend shading or day label.  We’ll even show how to change the day of week labels to another language including Klingon!

Some of these ideas come from our readers while others are answers to readers questions.

See Yearly Planner / Calendar for Office Watch readers

Headings

Many readers had very different opinions about the column headings (top row) or month labels (left column). They all suggested different changes depending on their personal taste.

Here’s some ideas you can use:

Change row height

Change the height of the top row by dragging.

year planner calendar improvements and extensions microsoft excel 33897 - Year Planner calendar, Improvements and Extensions

If you’d like more space between the numbers and the January row, change the vertical alignment to Top. That will leave space below the digits.  Or use Bottom align for a closer fit.

It looks screwy to us but Trevor J. from Wisconsin likes the Home | Alignment | Angle Counterclockwise look to angle the day numbers.

year planner calendar improvements and extensions microsoft excel 33898 - Year Planner calendar, Improvements and Extensions

Another approach from Ron S. is to add a spacing row and column.  Then it’s easy to adjust the spacing or reformat the ‘blank’ row/column.

year planner calendar improvements and extensions microsoft excel 33899 - Year Planner calendar, Improvements and Extensions

Wider columns

Wider columns let you fit in more text, make a bigger planner or have larger day labels.

Before changing the column widths get the current width.  Select a single column, right-click, choose Column Width and make a note of the value.

Now select all day columns (B to AF), right-click then Column Width. Most likely the value will be blank, that’s why we suggested getting the single column width first.  Enter a larger value for width.

Change look

We added cell styles to make it easy to change the overall look. There are four cell styles:

year planner calendar improvements and extensions microsoft excel 33900 - Year Planner calendar, Improvements and Extensions

Day Cell Default – the formatting of each day before conditional formatting kicks in.

Day Number – the top row 1

Month Name – the left column A

The last, all black, style is ‘No Day’ for the end-of-month cells. The formatting hides the name.

Change any of the styles to quickly change the overall look.

The weekend shading and formatting for 29th Feb (Cell AD3) is in Conditional Formatting.

year planner calendar improvements and extensions microsoft excel 33901 - Year Planner calendar, Improvements and Extensions

An annoyance in Excel is that conditional formatting has to be done manually.  It’s not possible to apply a cell style name to a conditional format.

Format as Table

The entire Year Planner can be converted into an Excel Table.  Some readers like this, we’re not so sure there’s any great advantage.

Select the entire planner and choose Home | Format as Table.

Most likely you’ll want to turn off the Filter Buttons which will clutter up the top row

Banded Rows have no effect because there are cell styles and conditional formatting overlaying the table formatting.

year planner calendar improvements and extensions microsoft excel 33902 - Year Planner calendar, Improvements and Extensions

Public Holidays and other special events

Any public or personal holidays are individually formatted either with a cell style or manual formatting.

We added cell styles for Public Holidays and Personal / Vacations.

year planner calendar improvements and extensions microsoft excel 33904 - Year Planner calendar, Improvements and Extensions

Unfortunately, the cell formatting only applies to weekdays.  Conditional formatting for weekend days  overrides other formatting (in other words, conditional formatting is applied last).  This is Easter 2020 with 10-13 April all using the Public Holidays style but only Good Friday and Easter Monday show that style.

year planner calendar improvements and extensions microsoft excel 33905 - Year Planner calendar, Improvements and Extensions

The workaround would be a more complicated conditional formatting formula.  The formula would have to test the cell fill color and, sadly, that’s not possible with in-built functions like Cell().  Custom VBA or a legacy function would be necessary to get the fill property.

Fixed date holidays can be manually formatted because the date cell remains the same for any year.

year planner calendar improvements and extensions microsoft excel 33913 - Year Planner calendar, Improvements and Extensions

Christmas Day will always be cell Z13 (unless you’ve added more rows/columns).

4th July  cell E8

Australia Day  AA2

Highlight a day of the week

We were asked how to highlight a certain day (Tuesday) because it’s garbage day.

Add a conditional format like this:

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

Change the last digit to which day of the week you like. Monday = 1, Tuesday = 2 etc.

year planner calendar improvements and extensions microsoft excel 33906 - Year Planner calendar, Improvements and Extensions

We’ve dug into the Format options Fill | Fill Effects to demonstrate another way to decorate a cell with a gradient option.

Block out past dates

The first Conditional Formatting rule can block out any dates before today with a simple formula.

=B2<Today()    — B2 is the 1st January

year planner calendar improvements and extensions microsoft excel 33935 - Year Planner calendar, Improvements and Extensions

The rule applies to all day cells e.g.  =$B$2:$AF$13

Choose whatever formatting you like but make sure this rule is first, before the weekend and other rules.

Hide Year List

The pull-down list of years is sourced from the Year List tab/worksheet.  For the sake of tidiness, you can hide it by right-clicking on the tab then Hide.

year planner calendar improvements and extensions microsoft excel 33907 - Year Planner calendar, Improvements and Extensions

Or, bypass the need for the tab at all.  An alternative is just typing a comma separated list of the years in the Data Validation for cell A1.

year planner calendar improvements and extensions microsoft excel 33908 - Year Planner calendar, Improvements and Extensions

Better highlighting row and column

If you’re using the Year Planner in Excel (i.e. not printed) you might prefer different highlighting of the selected row or column.  See Excel tricks to highlight selected row, column, heading and more

Change ‘weekend’ cells

There are two ways to change the conditional formatting that shades day cells as ‘weekend’.

year planner calendar improvements and extensions microsoft excel 33909 - Year Planner calendar, Improvements and Extensions

The current formula is …

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

Weekday() returns a number for the day of the week.  Saturday is 6, Sunday is 7.

The last two characters  ‘>5’ is how Excel ‘knows’ it’s a weekend day.  Change that part of the formula to alter the days shaded as weekend.

To shade just Sunday use:

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

or

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

Weekday() options

The alternative is changing the way Weekday() works.  In the conditional formula, look for the  , 2  parameter.  That controls how Weekday() numbers the days.

year planner calendar improvements and extensions microsoft excel 33910 - Year Planner calendar, Improvements and Extensions

Change the setting to   ,1  as in

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

will shade Friday and Saturday.

year planner calendar improvements and extensions microsoft excel 33911 - Year Planner calendar, Improvements and Extensions

Day of Week label

Using a custom cell format ‘ddd’ to get the day of the week is the most elegant method but there are other options that are more explicit with options.

Instead of a Custom cell format, you can apply the formatting within a formula with the Text() function. Text() converts a value into another format using the same formatting codes that Custom formatting does.

=TEXT(DATE($A$1,MONTH($A2),B$1),"ddd")

That’s really another way of doing the same thing.  Some people might prefer it because the formatting of the days is more obvious in the formula.

Another language

Maybe you’d prefer to show the days in another language from Excel’s default.  That’s possible using a combination of Weekday() and Choose()

=CHOOSE(WEEKDAY(DATE($A$1,MONTH($A2),B$1)),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

year planner calendar improvements and extensions microsoft excel 33912 - Year Planner calendar, Improvements and Extensions

Now you can change each of the day labels like this for French, Chinese and Klingon.

=CHOOSE(WEEKDAY(DATE($A$1,MONTH($A2),B$1)),"Dim","Lun","Mar","Mer","Jeu","Ven","Sam")

=CHOOSE(WEEKDAY(DATE($A$1,MONTH($A2),B$1))," 星期天","星期一","星期二","星期三","星期四","星期五","星期六")

=CHOOSE(WEEKDAY(DATE($A$1,MONTH($A2),B$1)),"wa","DaS","pov","ghIt","logh","buq","ghIn")
subs profile e1563205311409 - Year Planner calendar, Improvements and Extensions
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