Google Sheets: Formatting a list of timestamped values to a weekly view-CodePudding

I'd like to transform a growing list of timestamps and values to a calendar-style weekly display in Sheets, with only one unique value per day and time of day not important.

I have tried to do this using an INDEX-MATCH for both: the week number and the day of the week, but I'm having trouble debugging it, due to the blank value of cells for future timestamps resolving to "Sat" when using the text() function.

=IFERROR(INDEX(ValuesRange,MATCH(Week,WEEKNUM(TimestampsRange),0),MATCH(Day,text(TimestampsRange,"ddd"),0),),)

Would appreciate advice for any more expedient formula.

Summary: I want to go from:

 ---------------------------- ---- 
|                            |    |
 ---------------------------- ---- 
| August 23, 2022 17:40:11   | 4  |
| August 29, 2022 11:20:30   | 8  |
| September 4, 2022 15:55:17 | 10 |
 ---------------------------- ----

To :

 ----- ----- ------ ----- ----- ----- 
|     | Mon | Tues | Wed | ... | Sun |
 ----- ----- ------ ----- ----- ----- 
| 34  |     | 4    |     |     |     |
| 35  | 8   |      |     |     | 10  |
| ... |     |      |     |     |     |
 ----- ----- ------ ----- ----- ----- ```

CodePudding user response：

Paste this formula and drag down.

=ArrayFormula(SUBSTITUTE(
              SPLIT(REGEXREPLACE(REGEXEXTRACT(REPT("F,",
              WEEKDAY(A2,3) 1), "(. ).\z"), ".\z",TEXT(B2,"@")),","),"F",""))