Google Sheets – Pivot Table Summary by Day

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-datesgoogle-sheets-query

I have a sheet with 2500 dates & times of when customers have placed their order.

I want to do a heatmap or a bar graph to show which days of the week are the busiest.
I rounded the exact times using mround to round up or down to the nearest whole hour.

But when I pivot on the data, for example, The 'Sunday 10:00 AM' at the beginning of the column I guess is not the same as the 'Sunday 10:00 AM' for the following week, and so on.

I tried converting the date to_text. Which worked, but then the pivot table couldn't be sorted in chronological order.

Best Answer

instead of your formula in B column and the whole pivot table, use:

=ARRAY_CONSTRAIN(ARRAYFORMULA(REGEXREPLACE(QUERY(IF(LEN(A2:A), 
 {WEEKDAY(A2:A), TEXT(A2:A, "dddd am/pm hh:00 am/pm")}, ), 
 "select Col2,count(Col2),Col1 
  where Col2 is not null 
  group by Col2,Col1 
  order by Col1
  label count(Col2)''", 0)&"AM ", "PM |AM ", "")), 999^99, 2)

0

0

0

Related Topic