Pivot Table to Show OR room use hour by hour

Wolfster63

New Member
Joined
May 2, 2018
Messages
21
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:

Room Number

Start Date/Time
End Date/Time
Start Time
End Time

OR1
01/02/18 07:00
01/02/18 10:03
7:00
10:03

OR2
01/02/18 07:09
01/02/18 08:32
7:09
8:32
OR3
01/02/18 07:30
01/02/18 08:21
7:30
8:21
OR4
01/02/18 07:30
01/02/18 10:02
7:30
10:02
OR5
01/02/18 07:32
01/02/18 10:10
7:32
10:10
OR6
01/02/18 07:58
01/02/18 08:36
7:58
8:36
OR7
01/02/18 13:11
01/02/18 18:32
13:11
18:32
OR8
01/02/18 13:32
01/02/18 16:33
13:32
16:33
OR9
01/02/18 17:00
01/02/18 18:13
17:00
18:13
OR1
01/02/18 10:29
01/02/18 10:58
10:29
10:58
OR2
01/02/18 08:37
01/02/18 09:14
8:37
9:14
OR4
01/02/18 12:32
01/02/18 14:17
12:32
14:17
OR5
01/02/18 19:31
01/02/18 20:34
19:31
20:34
OR6
01/02/18 14:22
01/02/18 15:28
14:22
15:28
OR7
01/02/18 19:06
01/02/18 22:39
19:06
22:39

<tbody>
</tbody>

Have tried several permutations, with results,

Any broad ideas or approaches I could try?

Thanks,

Will
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Watch MrExcel Video

Forum statistics

Threads
1,129,557
Messages
5,637,039
Members
416,955
Latest member
Gohar hussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top