Filling a calendar using Arrayformula or LOOKUP

Question

I've made a calendar sheet and would like to fill it using an Arrayformula or some kind of Lookup. The problem is, the code in each cell is different, do I need it all to be the same code or is it possible to do an Arrayformula that does a different formula for each line?

I spent ages getting the calendar code working but would now like to simplify the code and I'm not sure what my next step should be:

https://docs.google.com/spreadsheets/d/1u_J7bmOFyDlYXhcL5dW3CHFJ1esySAKK_yPc6nFTdLA/edit?usp=sharing

Any advice would be much appreciated.

Answer 1

I've added a new sheet in your file called 'Aresvik'.

The green cells have new formula.

Cell B3 can be =date(B1,1,1)

Then each successive month can be =eomonth(B3,0)+1, =eomonth(J3,0)+1 etc.

The date formula in cell B5 is:

=arrayformula(iferror(vlookup(sequence(7,7,1),{array_constrain(sequence(40,1),day(eomonth(B3,0))+weekday(B3,3),1),query({flatten(split(rept(",",day(eomonth(B3,0))-1),",",0,0));sequence(day(eomonth(B3,0)),1,1)},"offset "&day(eomonth(B3,0))-weekday(B3,3)&" ",0)},2,false),))

It can be copied to each other cell below Mo, so B5 will change to J5, R5, Z5 etc.

Notes

The concept revolves around using the SEQUENCE function to generate a grid of numbers, 6 rows, 7 columns:

sequence(6,7)

which looks like this:

 1  2  3  4  5  6  7
 8  9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31 32 33 34 35
36 37 38 39 40 41 42

Then using these numbers in a VLOOKUP to get a corresponding date for the calendar. If the first of the month falls on a Thursday (April 2021), the vlookup range needs 3 gaps at the top of the list of dates. player0 has a more elegant solution than my original query using offset, so I've incorporated it below. Cell Z3 is the date 1/4/2021:

=arrayformula(
iferror(
vlookup(sequence(6,7),
   {sequence(day(eomonth(Z3,0))+weekday(Z3,2),1,0),
   {iferror(sequence(weekday(Z3,2),1)/0,);sequence(day(eomonth(Z3,0)),1,Z3)}},
2,false)
,))

The first column in the vlookup range is:

sequence(day(eomonth(Z3,0))+weekday(Z3,2),1,0)

which is an array of numbers from 0, corresponding with the number of days in the month plus the number of gaps before the 1st day.

The second column in the vlookup range is:

{iferror(sequence(weekday(Z3,2),1)/0,);sequence(day(eomonth(Z3,0)),1,Z3)}},

It is an array of 2 columns in this format: {x;y}, where y sits below x because of the ;.

These are the gaps: iferror(sequence(weekday(Z3,2),1)/0,), followed by the date numbers: sequence(day(eomonth(Z3,0)),1,Z3)

(Example below is April 2021):

0   
1   
2   
3   
4   
5   
6   44317
7   44318
8   44319
9   44320
10  44321
11  44322
12  44323
13  44324
14  44325
15  44326
16  44327
17  44328
18  44329
19  44330
20  44331
21  44332
22  44333
23  44334
24  44335
25  44336
26  44337
27  44338
28  44339
29  44340
30  44341
31  44342
32  44343
33  44344
34  44345
35  44346
36  44347

The vlookup takes each number in the initial sequence (6x7 layout), and brings back the corresponding date from col2 in the range, based on a match in col1.

When the first day of the month is a Monday, iferror(sequence(weekday(BB1,2),1)/0,) generates a gap in col2 of the vlookup range. This is why col1 in the vlookup range has to start with 0.

I've updated the sheet at https://docs.google.com/spreadsheets/d/1u_J7bmOFyDlYXhcL5dW3CHFJ1esySAKK_yPc6nFTdLA/edit#gid=68642071

Values on the calendar are dates so the formatting has to be d.

If you want numbers, then use:

=arrayformula(
iferror(
vlookup(sequence(6,7),
   {sequence(day(eomonth(Z3,0))+weekday(Z3,2),1,0),
   {iferror(sequence(weekday(Z3,2),1)/0,);sequence(day(eomonth(Z3,0)),1)}},
2,false)
,))