Skip to content

A better Excel Calendar Creator for Office Watch readers

Here’s a better and more flexible version of the Calendar Creator with both Sunday and Monday start of week.

Office Watch reader, David Peel, has made a better, more compatible Calendar Creator.  We added some different versions to suit other calendar formats.

We’ve told you about The Mystery of Excel’s Calendar Creator template which doesn’t always work as it should.

Change the year in the long title cell B2 – Excel figures out the rest.

This download is available EXCLUSIVELY to Office Watch subscribers. The link to this and other special downloads is in each issue of Office Watch or Office for Mere Mortals

Vertical Months

The original calendar creator has the months listed down then across, see above.

Horizontal Months

The variation on the Horiz. months tab has the months listed across then down.

Horizontal (left to right) is the most common calendar format so it’s strange that the Microsoft version is different.

Monday start of week

Microsoft’s Calendar Creator is locked into a Sunday start of week.  We’ve made a version with Monday start instead.

That change is fairly easy.  The formula for each day of the first week looks like this (with Sunday start of week):

=IF(WEEKDAY($B2,1)<=COLUMN(B4)-1,DATE(Year,MONTH($B$2),COLUMN(B4)-WEEKDAY($B2,1)),"")

Changing to Monday start only needs a change to the Weekday() function. Weekday() returns a number for the day of the week with the second parameter setting which day is number 1) – 1 means Sunday = 1   2 means Monday = 1.

=IF(WEEKDAY($B2,2)<=COLUMN(B4)-1,DATE(Year,MONTH($B$2),COLUMN(B4)-WEEKDAY($B2,2)),"")

And change the day headings, obviously <g>.

These formulas are only necessary for the first week of each month (e.g. row 4 in the image above). The rest of each month is simply ‘+1’ from the previous day.

Changing the Look

All the parts of the calendar have cell styles.  Changing the colors etc of the calendar can be quickly done by changing the styles.

Day – the individual days

Day title – the ‘Mo’ ‘Tu’ headings.

Month title – the heading for each month

Year title – the year number at the top of the sheet.

Public Holidays – any days-off can be marked with this style.  We’ve tagged Christmas Day.

If you wish, add a custom style for weekend days.

How it works

Thanks to Office Watcher, Ron S., there’s now a “How it works” tab with a more detailed explanation of how the workbook works. For those of you interested but don’t have the time to dig around the sheet (who does?).

More Calendar choices in Microsoft Office

Yearly Planner / Calendar for Office Watch readers

Year Planner calendar, Improvements and Extensions
2023 calendars in Word and the tricks to make them special

Outlook Print Calendar Options -a Daily/Weekly/Monthly plan on paper

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

About this author

Office-Watch.com

Office Watch is the independent source of Microsoft Office news, tips and help since 1996. Don't miss our famous free newsletter.