A One-Page Calendar
Yesterday, I was inspired by a simple, but clever idea for a one-page calendar that I read about in Ethan Siegel's column in Big Think (see One Page Calendar)
It's not the sort of calendar that you write your dental appointment on, but it is great for answering questions like - which day of the week does my birthday fall on this year? or when do the clocks go forward in the UK (ie which is the last Sunday in March)?
What intrigued me, in particular, was whether an automated version of this could be built in Excel, just using Excel formulae and no VBA software. It turned out that it can, and it struck me as an excellent opportunity for a quick Excel tutorial, so here's how I did it, step by step.
How the Calendar Works
The one-page calendar is a simple table that ties together dates, months, and days of the week in a very easy-to-understand and intuitive fashion.
So let's say that you're planning a surprise for someone's birthday on Aug 6th and need to know what day of the week that is. Find the column for August, and the row for the 6th, and where they cross is a Sunday, so that's ideal for a day out.
Want to know the date of the 2nd Tuesday in February? Well, find the column with February, glance down the column to find Tuesday, and look across to see all the dates that fall on a Tuesday. The second entry is the 14th.
Planning a June wedding? Here are all the Saturdays...
If you're feeling adventurous, you might even try answering the question - which months have 5 Saturdays?
OK, I'm sure you get the idea. If you want more examples and information, please see Ethan's original article.
So why am I talking about this? It's because it's a fun and interesting question to think about how to build one of these with Excel, and particularly one that updates itself each year. Here's how I did that.
Days of the Month and Days of the Week
The Days of the Month and the Days of the Week tables don't change from year to year. If you wanted, you can just type them in (as numbers) and skip this section. But I'm going to use this opportunity to show how the SEQUENCE and WRAPCOLS functions can take care of this.
Let's start with the days of the month. Here I've used the SEQUENCE function to generate a list of numbers from 1 to 31, and then WRAPCOLS to present these as a series of columns, 7 cells high, padded with blanks when we run out of numbers. This formula is used just once, in cell B14.
Now you're probably more used to the idea of a formula returning a single value, but that isn't always true. In this case, the SEQUENCE function generates 31 values, and they spill over to adjacent cells.
SEQUENCE and WRAPCOLS are relatively recent functions, so if you're not yet using the latest version of Excel on a PC, these might not be available. But that's OK, you can simply type out these 31 values by hand. As you see, I've also added a little formatting, centered the numbers, coloured the background, and I've added some borders to make this look nicer.
The next step is to create the days of the week. For reasons that will become clear later, we'll represent these as the numbers 1 to 7.
Here I've used 7 SEQUENCE formulae in cells G14:G20 each generating 7 numbers along each row, starting from 1,2,3 etc. For convenience, I've used the value in column B as the starting value for each sequence.
To convert these all to the range 1..7 we need to use the MOD formula, as in...
=MOD(SEQUENCE(1,7,B14-1,1),7)+1
MOD(<number>,7) tells the function to only use the 7 numbers 0..6 and then wrap around when <number> is bigger than this. This is (a bit) like a clock only uses 12 numbers, making 13:00 the same as 1 o'clock for example. However we want 1..7 (not 0..6) when enumerating the days of the week, so we need to add 1 to whatever MOD returns, and start our sequences 1 value earlier to compensate.
Here's what that produces...
So how do you get this to display days of the week? By using custom number formatting.
First, select G14:M20 as the range of cells you want to format, go to the "Number Format" dropdown, and click on "More Number Formats..." at the bottom
In the dialog box, select Custom format, and type "ddd" (without the quotes) into the box, which tells Excel to format those numbers 1..7 as "Sun","Mon","Tue" etc. Click OK to apply this format.
 |
 |
And here's the result.
The Months of the Year
Everything we've built so far is static. It doesn't change from year to year. The more challenging part is the Months of the Year display, which changes every year, so we have to work out how to do that.
But let's first start with the year display. First I merged cells C10:E12, typed in =YEAR(TODAY()) to produce a display of the current year, applied a large 26pt, bold font size, and named this cell for easy future reference by typing "Current_Year" in the box as shown. Nice and easy.
So what's the secret of the Months of the Year display? Well (for example) May appears in the second column because the 1st of May occurs on Monday, the second day of the week. And Mar appears in the 2nd row of the 4th column, because it's the second month in the year that starts on a Wednesday. Performing this only using formulae is going to be a little complicated, so let's take this one step at a time.
First, we figure out which day of the week each month starts on. We can do this with a single formula written into cell B3. We use SEQUENCE(1,12) to generate the numbers 1..12, we turn this into twelve first-of-the-month dates for this year using DATE(<year>,<month>,1). Note that for <year> we reference the named cell Current_Year (or you could just write C10), <month> uses the sequence we generated, and 1 gives us the first of the month in each case. Finally, we use WEEKDAY to return the number of the day of the week those twelve first-of-the-month dates occur on.
So in the example, Jan 2023 begins on the 1st day of the week (Sun), Feb and March begin on the 4th day of the week (Wed) and so on.
And to make this look more readable, we format these numbers as days, which is what they represent of course. No change of formula, they're still just numbers 1..7, they just appear as days of the week now.
Now here's the most complicated bit. We're trying to figure out where the months need to be placed over the Days of the Week table.
So for example, above the Saturday entry (Cell M14) we want to know which months start on a Saturday. We could do this manually, by scanning across our "1st weekday" table, looking for Saturdays. We can see 2 examples: in the 4th column (ie April) and 7th column (ie July), and so we'd write 4 and 7 into that column of our table.
But how can we do that with a formula? Well, it can be done with the formula shown below.
=IFERROR(MATCH(G$14,OFFSET($B$3#,0,G4),0)+G4,"")
Here's how it works.
$B$3# means the spill range of values produced by the formula in B3, ie the range B3:M3
OFFSET(<range>,<rows>,<cols>) displaces a range by the specified number of rows and cols.
So OFFSET($B$3#,0,G4) means the range that's G4 places to the right of the range defined by $B$3#. At the moment G4 is blank (essentially 0), so has no effect.
MATCH(G$14,<range>) then looks in this range for the day of week we want, and returns how far along the first match is. So in cell G5, this formula looks along the range for a Sunday, and finds one in the first position, returning a 1.
So why use the OFFSET function? Well if we copy this formula in G5, down to the cell below (ie G6), we can repeat the search for months beginning with a Sunday, but we want to skip the one we just found. So by searching in a range shifted one cell to the right (ie C3:N3), we can avoid finding the first Sunday on the list. That's the point of OFFSET($B$3#,0,G5).
What MATCH then returns is the answer that the next Sunday is 9 months later than the first, so we then need to add the first month found (in this case January (month 1)) to get back that the second occurrence is in month 10 (October). This is why the result is MATCH(....)+G5.
And finally, if MATCH doesn't succeed, it throws up an error message, so we use IFERROR() to trap this, and substitute a blank. That's it!
Copy the formula to fill every cell in G5:M7, and you have a list of month numbers aligned over the correct starting days, which is always correct for the current year, and automatically updates itself.
The final step is simpler. We just want to convert these numbers to readable month names. There are several ways to do this, but this formula in G10...
=IF(ISNUMBER(G5),DATE(Current_Year,G5,1),"")
...tests to see if there's a number in the top table, and if so, creates a date out of it. And then (just as we did to display days as numbers) we use number formatting on the result, this time using a custom number format of "mmm".
And that's it. Here's the result.
You can hide rows 1..7 if you wish, and/or you can select the area you want to print (A8:N21) and use Print Layout / Print Area / Set Print Area, to ensure that only those cells appear on the print-out.
And we're done.
