At a glance
When you think of calendars you think of Microsoft Outlook.
Excel formulas and formats can also create a flexible month calendar. The final output is shown in Figure 1.
This could be useful to print out and use for planning.
Cell A1 uses the Center Across Selection format (see Figure 2) which centres the date in cell A1 across the seven columns.
Cell A1 displays the full month name and year. Figure 3 shows the applied format.
Row 2 has the abbreviated days of the week entered as text. The standard week in Excel begins on a Sunday. A Monday start to the week is handled at the end of the article.
Creating the Calendar
There is a single formula that creates the day numbers that line up with the headings in row 2. The formula is in cell A3. The formula is a dynamic array and spills down and across.
Spilling means a range is populated with values, but the formula is only in the top left cell of the spill range. Spilling is a new concept and has been covered in numerous previous articles.
The best way to understand the formula is to see how it was developed from scratch.
SEQUENCE function
The SEQUENCE function creates a 6-row x 7 column grid for the calendar. Six-weekly lines are required to display the month if a 31-day month starts on a Friday or a Saturday.
The formulas that follow are in cell A3.
The SEQUENCE function and its output is shown in Figure 4.
This SEQUENCE function creates a list of sequential numbers from 1 to 42 using six rows and seven columns.
The WEEKDAY function can be used to make sure the days of the month line up with the day headings in row 2.
WEEKDAY and DAY functions
The WEEKDAY function returns a number from 1 to 7 representing the day of the week of a date. Sunday is 1 and Saturday is 7.
The DAY function returns the day of the month from 1 to 31 depending on the month. The formula that returns the correct day number for each column is.
=DAY(A1+SEQUENCE(6,7)-WEEKDAY(A1))
This is shown in Figure 5.
The date in cell A1 is the first of November 2024. This date has the sequential numbers added to it to create 42 dates. The 42 dates are then reduced by the day of the week number.
The 1 November 2024 is a Friday. Its WEEKDAY number is 6. This calculation correctly lines up the dates created with the headings.
The DAY function extracts and displays the day of the month for those 42 dates.
The days of the previous and subsequent months are also displayed on the calendar. To identify the other month’s days, the date in cell A1 needs to be compared with the month in each of the 42 cells. A blank cell can be displayed for the other months.
LET function
This comparison would normally double the length of the formula. The LET function enables shorter formulas.
Without the LET function the formula is:
=IF(MONTH(A1)=MONTH(A1+SEQUENCE(6,7)-WEEKDAY(A1)),
DAY(A1+SEQUENCE(6,7)-WEEKDAY(A1)),"")
The LET function shortens this to:
=LET(d,A1+SEQUENCE(6,7)-WEEKDAY(A1),IF(MONTH(A1)=MONTH(d),DAY(d),""))
The LET function enables the use of variables. The variable d captures the 42 dates. The functions MONTH and DAY functions then use d instead of the long calculation. Figure 6 shows the formula in use.
The Formula Bar in Figure 6 has line breaks inserted to help separate the formula into sections and make it easier to follow. To insert a line break in the Formula Bar press Alt + Enter. The line breaks don’t affect how the formula works.
Standalone formula
Currently the formula creates the day numbers for the calendar but requires the weekday headings to be entered manually. The formula can incorporate these headings.
The following formula in cell A2 combines the headings and the day numbers.
=LET(d,A1+SEQUENCE(6,7)-WEEKDAY(A1),
hdg,TEXT(CHOOSEROWS(d,1),"ddd"),
VSTACK(hdg,IF(MONTH(A1)=MONTH(d),DAY(d),"")))
The hdg variable captures the result of the TEXT function. The CHOOSEROWS function extracts the first row of the date listing. The TEXT function displays that first row as text entries using the three-letter abbreviation for the day.
The VSTACK function combines the hdg variable with the days beneath to create a 7 x 7 grid.
See the output in Figure 7.
Start on a Monday
Surprisingly, the change to a Monday start is not difficult. The WEEKDAY function defaults to a Sunday start but can work with a Monday start.
Changing WEEKDAY(A1) to WEEKDAY(A1,2) in the formula will change the whole calendar to work with a Monday start – see Figure 8.
There are two separate formulas to handle a Sunday or a Monday start. We can create a custom function to handle both and simplify the whole process.
Custom Function
The LAMBDA function allows the creation of custom functions and has been covered in previous articles.
The LAMBDA function that tests the custom function formula is:
=LAMBDA(mth,start,
LET(d,mth+SEQUENCE(6,7)-WEEKDAY(mth,start),
hdg,TEXT(CHOOSEROWS(d,1),"ddd"),
VSTACK(hdg,IF(MONTH(mth)=MONTH(d),DAY(d),""))))(A1,2)
The value from A1 is passed to the LAMBDA first argument mth. The number 2 is passed to the second argument start. These arguments are then used within the LET function.
A custom function called fnCALENDAR can be created using the LAMBDA function in its Refers to: formula, see Figure 9.
The custom function with a Monday start is shown in Figure 10.
An example with a Sunday start is shown in Figure 11.
The companion file has all these examples. You can copy the sheet from the companion file to another file to have the fnCALENDAR function available in the other file.
This example has demonstrated several advanced Excel techniques. Custom functions can capture complex formulas and simplify them for general use.
The companion video and Excel file will go into more detail to demonstrate these techniques.
