Master calendar creation in Excel

Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals brought to you by CPA Australia.

Neale Blackwood:

Introduction to building a calendar
Welcome to the Excel Tips podcast. My name is Neale Blackwood and in this episode we're going to look at building a single month calendar in Excel so that you can print it out. It can be useful for things like planning. I actually create my own desk calendar using this technique so that I can make notes on it and things like that. It's also going to demonstrate a lot of functions that you can combine to create the solution. Now, there is a companion video on the in the black website.

Also on in the black website there is the companion file, so you can download the file and you'll get all of the examples that I'm going to talk about. Also, the video goes into detail about how these functions all work together. I thought I would use the podcast to actually just go through and talk about each of the functions that I've used. There are some advanced techniques used in this solution, so when you're watching the video, you might need to watch it a few times or pause and rewind just to get some of the concepts.

It is a good example of converting a complex formula into an easy to use custom function. At the end of the video, you'll see how easy it is to create the calendar just using a custom function, and the custom function creates the headings as well as the numbers for the calendar.

Also on the video there's a bonus conditional format, so the article doesn't go into any detail about the conditional formats that have been applied, but the video does show you that you can add conditional formats for the weekends and also how to create a conditional format for the holidays, so that you can highlight the cells based on whether they're a weekend or a holiday.

Functions used in this tutorial
Okay, so I thought I'd go through the functions that I used and just to talk about how you can use these functions in Excel. So the first function that we need to create the two-dimensional calendar is the sequence function. Now, typically the sequence function's used to create either a column of sequential numbers or a row of sequential numbers.

In this case, the solution required a matrix because the calendar is two-dimensional, we're going to have basically six rows and seven columns of sequential numbers. So the numbers are going from one through to 42, and that creates the framework, if you like, for the calendar. So that way we can display the dates for any month.

So the sequence function allows us to create sequential numbers in a matrix structure, so two-dimensional. So the calendar structure that we were using had the days of the week going across the top. Now in the first example I started with Sunday. In Excel, the default week starts on a Sunday. So the first formula we build will be based on a week that starts on a Sunday. I'll explain how you convert it to the Monday, which funnily enough is really easy as we go through the podcast.

But basically we create a formula that lines up the days in the month against the headings that we've got across the top from Sunday to Saturday. And so the sequence function gives us those 42 numbers that we can use to build the date structure, and then we combine the day function with the weekday function to create the formula that actually lines up the days of a particular month with the days of the week headings across the top. Check out the video to see the exact formula, but I'll discuss how each function works.

How the different functions work
So the day function. The day function in Excel returns the day of the month. So you refer to a particular date, so let's say the 14th of November, and the day function would return 14. So it's just the day number of the month. Now the weekday function works a little bit differently. The weekday function returns a number from one to seven, and that represents the day of the week. So weekday returns a number from one to seven.

The default operation of weekday has Sunday as number one and Saturday as number seven. So that's the standard default use of the weekday function. So that's why in the first example we use a week that starts on a Sunday, so that's the default of the weekday. So we can combine those three functions, sequence, day and weekday to get the correct days lining up with the headings across the top of the calendar.

When we do that, we end up with lots of numbers on the calendar that aren't for the current month. So the example used November 2024, so the first week of that calendar actually had some dates from October and the last week had the days from December. And so we don't want to show those. So what we need to do is to compare the dates that we have with the month that we are reporting on, so in this case, November 24. And so we can use the if function to do that, but when we do that, we are duplicating the same formula, so we need to test is the month of each individual day in the calendar the same as the month of the month we are reporting on?

And so that's a formula I start off with, which is reasonably long and complex, but we can shorten it using the let function. So the if function allows us to compare the month of the day with the month that we're reporting on, and if they're the same, then we can return the date that we want, otherwise we display a blank cell. So the if function allows us to use logic to figure out which days to display.

That formula gets quite long, and so we can use the let function, anytime you're duplicating part of the formula, especially if that part of the formula is very long, we can use the let function to capture a value, or in this case the matrix as a variable. In the example, I use the variable with the letter D, and that captures that whole six by seven matrix that we created.

And so instead of repeating the long formula in the if function, we can just use the variable D. And so the let function can shorten and simplify formulas. And in that if function, we did use the month function. Now the month function returns the month number of a date, and we were just comparing the month number of the dates in the matrix against the month number that we are reporting on, which as I mentioned was November 2024.

Using formulas to create headings
The first formula that was examined in the video, and the article just provided the numbers, so it gave us the numbers of the days in the calendar. We can use those numbers to create the headings. In the initial structure, I had keyed in the days of the week in row two and the formula was entered in row three. The final formula allows us to extract the day of the week from each of the columns from the six by seven matrix.

So in that way, we can automate the headings as well as the dates. So to do that, I use three more functions. The first was the Choose Rows function, and what Choose Rows allows you to do is to select a specific row from a matrix. And so I extracted the first row of the six by seven matrix, and then I used the text function to convert those dates into the day of the week. And you can use that in the text function using the triple DDD, DDD, surrounded by quotation marks, and that will return the abbreviation for the day of the week.

So MON for Monday, and that's the text function. What we did inside the let function, I created a new variable called HDG, standing for heading, and that variable captured those headings. So that meant I had a variable for the headings and then a variable for the numbers.

Now, to combine them one above the other, you can use the VSTACK function. So VSTACK, it's a fairly new function, allows you to basically combine ranges, one on top of the other. And so the VSTACK function had the heading on top followed by the numbers underneath. So that meant the calendar was automated based on a single formula in a single cell.

Changing the calendar to start the week on Monday
So something I mentioned earlier was the Sunday versus Monday start of the week. Now, I must admit, I do prefer using the Monday start of the week rather than the Sunday when I'm producing calendars. You would think adding that would be a difficult process, but in reality, it turns out to be quite straightforward, and it all comes back to that weekday function. So the weekday function has a standard default setting for a Sunday start to the week.

So Sunday was one and Saturday was seven, but there is an option in the weekday function, that's the second argument, and you just basically go comma two in the weekday function. And what that does is that converts the week to Monday equals one and Sunday equals seven. And so that is the only change to the formula you need to make, you change the weekday function to comma two, and that changes the whole formula to work with a Monday start to the week.

So this means we've got two separate formulas. They're very similar, but they are different. One formula starts the week on the Sunday and the other starts the week on the Monday. And so that's where I thought, well, we could automate this whole process using a custom function.

Building a custom function with LAMBDA
Now, custom functions in Excel use the Lambda function. So the Lambda function allows you to basically create arguments that you can define and then use within a calculation. So the arguments are just like normal arguments in an Excel function, and you define what they are. And so the Lambda function, the first part of it is the individual arguments. So I use two, I use one argument as MTH standing for the month and then a second argument for the start.

So the start, if you want a Monday, you use a two. If you want a Sunday, you use a one. And so all you do is you refer to the first of a particular month and then comma, and then then either a one for a Sunday or a two for a Monday. That becomes a custom function when you create a range name that uses the Lambda function to define it. So the Lambda basically interacts with that let function that we had earlier, and we replace all of the cell references with the arguments, and that allows you to create the custom function.

I do recommend checking out the video to see how all this works together, but it does really simplify creating a calendar using a single custom function that can create either a Sunday start or a Monday start.

Applying conditional formatting for weekends
Also on the video, there's a bit of a bonus. I show you how you can apply a conditional format. So I use a grey for the weekends and then a orange for holidays. And so you can build that in to automatically update when you change the month for the particular calendar. Now, I use a bit of a cheat to figure out the weekends because the first row of the formula gives us the name of the day of the week, and luckily Saturday and Sunday both start with an S.

So one way to track weekends is to actually look if that text starts with an S. If it does, it's a weekend. So the formula for the conditional format for the weekends use the left function to identify the columns where the heading started with an S. Again, check out the video to see when you are creating custom functions that are applying to a two-dimensional range. You've got to be really, really careful where you put the dollar signs in the formula, and I explained that in the video.

And then to figure out if one of the dates on the calendar is actually a holiday, I use the count if function, and that was referring to a separate range of holiday dates that I typed in. So again, you can see how that plays out in the video.

Shortcuts covered in the tutorial
A couple of shortcuts that I use that are useful. When you are building these more complex formulas in Excel, it's a good idea to put a line break between the sections of the formula. And to do that, you use the alt key and the enter key. So basically hold the alt key and press enter, and that puts a line break in the formula. Note that any line breaks within the formula bar usually don't affect how the formula works.

Again, when you are showing these formulas that have multiple lines within the formula bar, you need to expand the formula bar. And the keyboard shortcut to do that is hold the control and the shift key down and press the letter U. So control shift U, and that opens up the formula bar. If you press it again, it actually closes it back to a single row. So it's really, really useful when you are developing your formulas.

Recap and summary of what we've learned
So this podcast was talking about creating a calendar in Excel and how you can combine lots of different functions together to provide a solution, and then ultimately how you can convert that into a custom function that makes it really easy for anyone to create the calendar.

So you can put a lot of effort into creating the formula and then simplify it into a single function that provides flexibility to, in this case, create a calendar that starts on a Sunday or a Monday. Hope you found that useful. Thanks for listening.

Garreth Hanley:
Thanks for listening to Excel Tips, a CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel tips and tricks. To find out more about our other podcasts and CPA Australia, check out the show notes for this episode, and we hope you can join us next time for another episode of Excel Tips.