It is common in reporting files, budgeting and forecasting files to create a list of months going across the page. The custom function in this post can automate the process.
We will start with a formula that performs the calculation.
Based on a starting month number, a year, and a number of months we can create a list of dates going across the page. In the image below you can see the formula being used.
The yellow cells are for input.
This formula uses the SEQUENCE function to create a horizontal sequential list of numbers.
The DATE function then uses that sequential list to create the month dates. It creates a date using the first of the month. You need to format the cells to display correctly.
The formula spills across to create all the months.
When creating a custom function, the typical development sequence is.
- Create a LET function and get it working correctly.
- Create a LAMBDA function based on the LET function and test it.
- Create the custom function using a range name and the LAMBDA function.
The LAMBDA version of the function is shown in cell D3 in the image below.
The LAMBDA function uses three variables start, yr, and months to capture the three inputs Those variable names are then transferred into the LET function to enable the calculation.
The formula below is then copied and pasted into the Refers to box when creating the custom function using the formula. See image below.
=LAMBDA(start,yr,months,LET(s,SEQUENCE(1,months),DATE(yr,start+s-1,1)))
You can see the custom function used in cell D5 in the image below.
In all cases you will need to format the range to see the months correctly.
Please note: I reserve the right to delete comments that are offensive or off-topic.