A better Excel Calendar Creator 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 better and more flexible version of the Calendar Creator with both Sunday and Monday start of week.

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

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

a better excel calendar creator for office watch readers microsoft office 33985 - A better Excel Calendar Creator for Office Watch readers

Change the year in the title cell B2.

DOWNLOAD Calendar Creator with options (any year).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.

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.

a better excel calendar creator for office watch readers microsoft office 33986 - A better Excel Calendar Creator for Office Watch readers

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.

a better excel calendar creator for office watch readers microsoft office 33987 - A better Excel Calendar Creator for Office Watch readers

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.

a better excel calendar creator for office watch readers microsoft office 33988 - A better Excel Calendar Creator for Office Watch readers

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.

More Calendar choices in Microsoft Office

Yearly Planner / Calendar for Office Watch readers

Year Planner calendar, Improvements and Extensions

2020 calendars in Word and the tricks to make them special

2020 Calendars in PowerPoint

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

subs profile e1563205311409 - A better Excel Calendar Creator 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