Heatmap Between Start and End Times

Question

I have a list of event space rentals and the time and date when they occur (e.g. Start: Mar 1 - 9 am, End: Mar 1 - 10 am). I'm trying to create a "heatmap" in Excel/Google Sheets so I can find out how often an event is booked at each half-hour historically.

I'm trying to answer the question, "how often is the space rented from 9 am - 9:30 am on Sundays." But extend that to every half-hour block across every day of the week. Then I will use some conditional formatting to create the heatmap by doing a gradient.

            S   M   T   W   T   F   S                       
9:00 AM     X   X   X   X   X   X   X
9:30 AM     X   X   X   X   X   X   X
10:00 AM    X   X   X   X   X   X   X
10:30 AM    X   X   X   X   X   X   X

In the example above, I would like to have a row for each half hour (e.g. 9 am, 9:30 am, 10 am, etc.) and a column with the day of the week (e.g. Sunday, Monday, Tuesday, etc.). Inside the table, I would like to find a formula to replace the Xs to count the number of times that space is rented for an event at that time and day of the week. If it's rented twice from 9 am to 9:30 am on Sunday, then the X would be a 2.

I tried the following formula but it's not quite calculating things correctly.

=COUNTIFS($N:$N,">="&$A2,$O:$O,"<"&$A3,$P:$P,"="&B$1)

In this example/formula above, the table with the raw data looks like this:

Column N      Column O
Start Time    End Time
6:00:00 PM    10:00:00 PM
11:30:00 AM   1:30 PM
8:00:00 PM    8:30 PM
5:30:00 PM    6:00 PM
6:00:00 PM    8:00 PM

A2 = 9:00 AM
A3 = 9:30 AM
B1 = Sunday

player0 player0 · Accepted Answer · 2019-04-16T20:14:56

paste in F1 cell, drag to the right and then drag down:

=COUNTA(IFERROR(FILTER($A$2:$A, $E2 >= $B$2:$B, 
                                $E2 <= $C$2:$C, 
                                F$1  = TEXT($A$2:$A, "ddd"))))

Heatmap Between Start and End Times

1 Answers