How to create an auto-updating Calendar Sheet in Excel

This lesson as it is excerpted form the book: Learn Excel 365 Expert Skills with The Smart Method: Fourth Edition: updated for the Jul 2020 Semi-Annual version 2002. Lesson 4-16: Create a perpetual calendar using the SEQUENCE function

This lesson assumes that you already have reasonable Excel skills

While absolute beginners should be able to follow this lesson you will not completely understand how the calendar works without a good basic knowledge of Excel. Our Essential Skills and Expert Skills tutorials take students on a journey from absolute beginner to Excel guru in a series of simple two-page lessons.

This lesson will not work with legacy Excel versions (2019 and earlier)

If you pay for your copy of Office by subscription you are using Excel 365 – the most powerful Excel version available.

The July 2020 Excel 365 semi-annual update introduced the concept of Dynamic Arrays along with several new dynamic array functions (including the SEQUENCE function used in this lesson). This has made creating the calendar you will build in this lesson massively simpler than it used to be.

---

Open the Calendar-1 sample file.

This file contains a very simple empty user interface for a perpetual calendar. If you had the Expert Skills book (see sidebar) you would have created this file during the previous lesson. There are two drop-down lists that enable the user to select a year and a month.

The drop down lists are enabled using simple list validations and dynamic array formulas (list validations and dynamic array formulas are covered in depth in the Expert Skills book).

Add a SEQUENCE function to cell B8 to populate the calendar with date serial numbers that relate to the chosen Year and Month.

The Function Arguments dialog appears:

Clearly you need to populate five rows and seven columns with dates to complete the calendar.

You now need to consider the starting value.

The concept of date serial numbers is covered in depth in the Essential Skills book: Lesson 4-2: Understand Date Serial Numbers. In this lesson students learn that every date in Excel is represented by a serial number.

The Start argument (the number that needs to be returned to cell B8) needs to be the serial number for the first day in the month defined in the Year and Month drop-down lists (in cells C4 and C5).

In the Expert Skills book: Lesson 3 11: Use the DATE function to offset days, months and years, students learn to use the DATE function. The DATE function accepts a Year, Month and Day argument (all of which must be numbers) and returns a date serial number.

It should be clear that the function:

=DATE(C4, Data!B3, 1)

… will return the correct date serial number for cell B8 (the number that starts the sequence).

The Function Arguments dialog should now look like this:

You can see that the first date serial number returned (in this example) is 44013. That’s because I had selected Jul for the month and 2020 for the year. 44013 is the date serial number for 1st July 2020.

The calendar is filled with date serial numbers:

Because the calendar only needs to show day numbers it is necessary to format the date serial numbers to display as days.

Custom formatting is covered in depth in the Essential Skills book: Lesson 4-4: Create custom number formats.

Day numbers are now shown in the body of the calendar.

Add the correct days of the week to cells B7:H7.

This function takes the date serial numbers found in cells B8:H8 and formats them as three-letter day names.

The TEXT function is covered in depth in the Expert Skills book: Lesson 3-19: Use the TEXT function to format numerical values as strings.

Even though you’ve now got a fully functional calendar you might want to make it look a little more traditional by greying out any dates that belong to the following month. That’s easy to do if you understand Formula-driven conditional formatting.

Formula-driven conditional formats are covered in depth in the Essential Skills book: Lesson 4-19: Create a formula driven conditional format.

This works because each date in the calendar is compared with the first date in cell B8. Note that $B$8 is an absolute reference and B8 is a relative reference. The dates that belong to the following month will have a different month to those in the other cells. This returns TRUE causing them to be re-formatted (and I chose the to format the font color light gray).

Absolute and relative cell references are covered in depth in the Essential Skills book: Lesson 3-13 Understand absolute and relative cell references.

The calendar now has the dates that belong to the following month grayed out:

Save your work as Calendar-2

If you’d like to examine the finished work here’s the workbook:

You can go a lot further with calendars. In our book: Excel Construction Kit #1: Calendar and Year Planner students create the year planner shown below that includes phases of the moon, public holidays, recurring events and other advanced features. This construction kit is available for both Excel 2016 and Excel 2019 versions and teaches some very advanced date-related techniques. As the legacy versions Excel 2016 and Excel 2019 do not support dynamic arrays and the SEQUENCE function, a different approach is taken in this book to create the calendar.

Download our free book to learn Excel basic skills

In the sidebar you’ll find links to instantly download our free Excel Basic Skills e-books.

Don’t be fooled by the title, some of the skills taught in this 100-page e-book are a mystery even to seasoned Excel professionals. For example, you’ll learn how to model linear and exponential series and how to use multiple-example flash fill to solve many common business problems. Most students can complete it in five hours of less.

And the e-book really is completely and absolutely free (you don’t need to sign up or provide an e-mail – just download it) and it is available for all versions of Excel in common use.