# Pivot Table to Show OR room use hour by hour

#### Wolfster63

##### New Member
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

Replies
5
Views
63
Replies
17
Views
957
Replies
8
Views
117
Replies
13
Views
172
Replies
10
Views
423

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.

### Which adblocker are you using?

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

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