CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Manu
Manu

Reputation: 3

How to arrange column data associated with dates into calendar grids (ie. 6x7 array blocks)?

I have this spreadsheet with dates and values in Data!E2:E, i want to transpose all the values in the column E to the calendar in Calendar!B5:H10. I need a formula to transpose all the values from column to rows, but every time it gets to the border of the month it jumps a row.

First day of the week is Sunday, everyday i plan to complete Data!E2:E cells with 'Yes' or 'No' and they should show up in the calendar with green or grey cells. So i can keep track of which things i did.

I have been doing this manually but it takes too long, i also tried inserting the function =TRANSPOSE(Data!E2:E366) in the calendar and modifying the number of the row, its saves a little time but its tedious.

Calendar

Data

Upvotes: 0

Views: 169

Answers (2)

player0
player0

Reputation: 1

delete everything you got and use this in B5. then copy B5 and paste to J5, R5, Z5, B13, J13, etc.

=INDEX(IFNA(VLOOKUP(VLOOKUP(SEQUENCE(6, 7), 
 {SEQUENCE(DAY(EOMONTH(B4&"/2022", ))+WEEKDAY(B4&"/2022", 1), 1, ), 
 {IFERROR(ROW(INDIRECT("1:"&WEEKDAY(B4&"/2022", 1)))/0); 
 SEQUENCE(DAY(EOMONTH(B4&"/2022", )), 1, B4&"/2022")}}, 2, ), Data!$A:$E, 5, )))

enter image description here

demo sheet

https://stackoverflow.com/a/67755777/5632629

Upvotes: 1

Argyll
Argyll

Reputation: 9875

In B5, put

=arrayformula(if((row(B5:H10)-row(B4)-1)*7+column(B5:H10)-column(B4)+1>=weekday(date(2022,month(B4&1),1)),
                  if((row(B5:H10)-row(B4)-1)*7+column(B5:H10)-column(B4)+1-weekday(date(2022,month(B4&1),1))<eomonth(date(2022,month(B4&1),1),0)-date(2022,month(B4&1),1)+1,
                      vlookup(date(2022,month(B4&1),(row(B5:H10)-row(B4)-1)*7+column(B5:H10)-column(B4)+1-weekday(date(2022,month(B4&1),1))+1),Data!$A$2:$E,5,false),
                      ),
                  ))

Spread (ie. copy & paste cell outside formula editor) the formula to the cells directly below your month names, e.g. J5, R5.

Remove placeholder formulae in your sheet and reset existing text formatting to visualize the results.

You can adapt the above to a single formula by adding a check on whether the corresponding cell for month name for a given cell is empty. It is also possible to combined the 2 if() conditions if you so desire. In addition, I recommend storing the year number of your calendar in a cell and then referring to that cell, instead of hard-coding 2022.

Upvotes: 0

Related Questions