Pivot Table to Show OR room use hour by hour

[Wolfster63]

I have a large spreadsheet that shows the in and out times of our surgical unit. I would like to construct a pivot table that would show if
a operating room is being use during a given time of a day. The goal would be to eventually look at average room use over a given span of days to see when certain rooms are more likely to be unoccupied so as to figure good times and days to let a surgeon have a room regularly scheduled for him.

Here is a basic structure of the spread sheet:

[TABLE="width: 365"]
<tbody>[TR]
[TD="width: 91, bgcolor: transparent"]Room Number

[/TD]
[TD="width: 122, bgcolor: transparent"]Start Date/Time
[/TD]
[TD="width: 115, bgcolor: transparent"]End Date/Time
[/TD]
[TD="width: 66, bgcolor: transparent"]Start Time
[/TD]
[TD="width: 94, bgcolor: transparent"]End Time

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OR1
[/TD]
[TD="bgcolor: transparent"]01/02/18 07:00
[/TD]
[TD="bgcolor: transparent"]01/02/18 10:03
[/TD]
[TD="bgcolor: transparent, align: right"]7:00
[/TD]
[TD="bgcolor: transparent, align: right"]10:03

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OR2
[/TD]
[TD="bgcolor: transparent"]01/02/18 07:09
[/TD]
[TD="bgcolor: transparent"]01/02/18 08:32
[/TD]
[TD="bgcolor: transparent, align: right"]7:09
[/TD]
[TD="bgcolor: transparent, align: right"]8:32
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OR3
[/TD]
[TD="bgcolor: transparent"]01/02/18 07:30
[/TD]
[TD="bgcolor: transparent"]01/02/18 08:21
[/TD]
[TD="bgcolor: transparent, align: right"]7:30
[/TD]
[TD="bgcolor: transparent, align: right"]8:21
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OR4
[/TD]
[TD="bgcolor: transparent"]01/02/18 07:30
[/TD]
[TD="bgcolor: transparent"]01/02/18 10:02
[/TD]
[TD="bgcolor: transparent, align: right"]7:30
[/TD]
[TD="bgcolor: transparent, align: right"]10:02
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OR5
[/TD]
[TD="bgcolor: transparent"]01/02/18 07:32
[/TD]
[TD="bgcolor: transparent"]01/02/18 10:10
[/TD]
[TD="bgcolor: transparent, align: right"]7:32
[/TD]
[TD="bgcolor: transparent, align: right"]10:10
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OR6
[/TD]
[TD="bgcolor: transparent"]01/02/18 07:58
[/TD]
[TD="bgcolor: transparent"]01/02/18 08:36
[/TD]
[TD="bgcolor: transparent, align: right"]7:58
[/TD]
[TD="bgcolor: transparent, align: right"]8:36
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OR7
[/TD]
[TD="bgcolor: transparent"]01/02/18 13:11
[/TD]
[TD="bgcolor: transparent"]01/02/18 18:32
[/TD]
[TD="bgcolor: transparent, align: right"]13:11
[/TD]
[TD="bgcolor: transparent, align: right"]18:32
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OR8
[/TD]
[TD="bgcolor: transparent"]01/02/18 13:32
[/TD]
[TD="bgcolor: transparent"]01/02/18 16:33
[/TD]
[TD="bgcolor: transparent, align: right"]13:32
[/TD]
[TD="bgcolor: transparent, align: right"]16:33
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OR9
[/TD]
[TD="bgcolor: transparent"]01/02/18 17:00
[/TD]
[TD="bgcolor: transparent"]01/02/18 18:13
[/TD]
[TD="bgcolor: transparent, align: right"]17:00
[/TD]
[TD="bgcolor: transparent, align: right"]18:13
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OR1
[/TD]
[TD="bgcolor: transparent"]01/02/18 10:29
[/TD]
[TD="bgcolor: transparent"]01/02/18 10:58
[/TD]
[TD="bgcolor: transparent, align: right"]10:29
[/TD]
[TD="bgcolor: transparent, align: right"]10:58
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OR2
[/TD]
[TD="bgcolor: transparent"]01/02/18 08:37
[/TD]
[TD="bgcolor: transparent"]01/02/18 09:14
[/TD]
[TD="bgcolor: transparent, align: right"]8:37
[/TD]
[TD="bgcolor: transparent, align: right"]9:14
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OR4
[/TD]
[TD="bgcolor: transparent"]01/02/18 12:32
[/TD]
[TD="bgcolor: transparent"]01/02/18 14:17
[/TD]
[TD="bgcolor: transparent, align: right"]12:32
[/TD]
[TD="bgcolor: transparent, align: right"]14:17
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OR5
[/TD]
[TD="bgcolor: transparent"]01/02/18 19:31
[/TD]
[TD="bgcolor: transparent"]01/02/18 20:34
[/TD]
[TD="bgcolor: transparent, align: right"]19:31
[/TD]
[TD="bgcolor: transparent, align: right"]20:34
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OR6
[/TD]
[TD="bgcolor: transparent"]01/02/18 14:22
[/TD]
[TD="bgcolor: transparent"]01/02/18 15:28
[/TD]
[TD="bgcolor: transparent, align: right"]14:22
[/TD]
[TD="bgcolor: transparent, align: right"]15:28
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OR7
[/TD]
[TD="bgcolor: transparent"]01/02/18 19:06
[/TD]
[TD="bgcolor: transparent"]01/02/18 22:39
[/TD]
[TD="bgcolor: transparent, align: right"]19:06
[/TD]
[TD="bgcolor: transparent, align: right"]22:39
[/TD]
[/TR]
</tbody>[/TABLE]

Have tried several permutations, with results,

Any broad ideas or approaches I could try?

Thanks,

Will