A few years back I posted a series of three posts about developing a formula to create a month calendar in Excel. I thought I would revisit that and convert that long formula into a custom function using LAMBDA.

You can check out the previous three posts with the formulas at the links below.

Using SEQUENCE with Dates in Excel Part 1

Using SEQUENCE with Dates in Excel Part 2

Using SEQUENCE with Dates in Excel Part 3

I won’t explain the long formula again. I will focus on explaining how to convert it into a custom function. An example file containing the custom function can be downloaded from the button at the bottom of the post.

Step 1

Convert the long formula into a slightly shorter formula using the LET function.

The LET function allows you to use variables within your functions. It doesn’t perform any calculations itself, it just allows you to define variables and then use those variables within your calculation.

Let’s review the existing function and figure out where we need to use variables.

The first thing to notice is that we use the same SEQUENCE and WEEKDAY functions twice. We can use a variable to capture the functions’ results and then refer to that variable.

Also note that we have used the reference to B1 four times. Again we can use a variable to capture that.

The other value that may change is the number 2 used in the WEEKDAY function. The two defines a Monday start to the week. When we build our custom function, we want the ability to choose between a Sunday and a Monday start to the week. We will also use a variable to capture the number 2.

I have used colour coding to help identify the parts of the LET function and how it relates back to the original formula.

The variables are explained below.

f is the cell that has the first of the month for the calendar. f stands for first.

t is for the number 2, which is used to change the start day of the week in the WEEKDAY function. t stands for type.

s captures the SEQUENCE output. s stands for SEQUENCE.

w captures the output of the WEEKDAY function, which uses variables f and t. w stands for WEEKDAY.

Now that the LET function works we can convert it into a custom function using the LAMBDA function.

Step 2

Create the LAMBDA function.

The LAMBDA function also allows the use of variables. It has the added ability to accept inputs like cells and ranges and allocates them to variables. We can pass the LAMBDA variables to the LET variables and complete the conversion.

LAMBDA has a special syntax that allows you to test the function before creating the custom function. It uses a separate set of parentheses (brackets) on the end to be the inputs to test the function.

Here is the LAMBDA version.

The (B1,2) on the end are the inputs to the LAMBDA function.

The first variable accepts B1 as an input and the type variable accepts 2.

The LAMBDA first variable is then passed to the f variable in the LET function. The LAMBDA type variable is passed to the t variable. The LET then does the calculation.

The LAMBDA and LET functions work well together.

Step 3

Create the custom function with a range name.

Copy the LAMBDA function without the extra brackets on the end – as below.

=LAMBDA(first,type,LET(f,first,t,type,s,SEQUENCE(6,7),w,WEEKDAY(f,t),IFERROR(IF(MONTH(f+s-w)=MONTH(f),DAY(f+s-w),""),"")))

In the Formulas tab click on the Define Name icon in the Defined Names section.

In the Name box at the top enter fnCALENDAR. I prefix all my custom functions with fn to differentiate them from standard Excel functions. Also when you type fn in the Formula Bar all the custom functions will be listed.

In the Refers to box paste in the LAMBDA function you copied earlier.

In the Comments box enter the following

=fnCALENDAR(first,type)

first = date of the first of the month

type = 1=Sunday start,2 = Monday

Use Ctrl + Enter to insert a line break. Use the Enter key on the far right of the keyboard

The Comments section explains the function and will display when using Intellisense in the Formula Bar – see image below.

You can now use the custom function in a cell.

This simplifies the formula and you can re-use the function in other files.