Pivot Table Help

[Wolfster63]

I have a spreadsheet that calculates the time a surgical case takes on a particular shift (Thanks [MENTION]shg[/MENTION]!). I am attempting to create a Pivot table that could analyze how much of the available room time is taken in a particular room or group of rooms during a given time period

I inserted a calculated field that would render a percent of the room time used. Each room has a finite number of minutes that can be used on a particular day during a particular shift.

Here is a sample spreadsheet to demonstrate the spreadsheet structure:

Doctor	OR	Date	Month	Year	Qtr	Count	DOW	Week	Shift1	Shift 2	Shift 3	Avail1	Avail2	Avail3
Kutt	1	01/02/2018	Jan	2018	1	1	Tue	1	81	0	0	510	240	300
Slash	2	01/02/2018	Jan	2018	1	1	Tue	1	88	0	0	510	240	300
Maim	3	01/02/2018	Jan	2018	1	1	Tue	1	106	0	0	510	240	300
Slicer	1	02/04/2018	Feb	2018	1	1	Sun	6	75	21	0	510	240	300
Slash	2	02/04/2018	Feb	2018	1	1	Sun	6	72	111	0	510	240	300
Maim	2	02/05/2018	Feb	2018	1	1	Mon	6	103	0	0	510	240	300
Kutt	3	02/05/2018	Feb	2018	1	1	Mon	6	94	0	0	510	240	300
Kildare	3	02/05/2018	Feb	2018	1	1	Mon	6	79	0	0	510	240	300
Slash	1	03/07/2018	Feb	2018	1	1	Mon	6	163	0	0	510	240	300
Maim	1	03/07/2018	Feb	2018	1	1	Mon	6	58	0	0	510	240	300
Slicer	2	03/07/2018	Mar	2018	1	1	Wed	10	0	14	0	510	240	300
Slash	2	03/07/2018	Mar	2018	1	1	Wed	10	160	0	58	510	240	300
Kutt	3	03/07/2018	Mar	2018	1	1	Wed	10	143	37	12	510	240	300
Slash	3	03/07/2018	Mar	2018	1	1	Wed	10	113	8	0	510	240	300
Maim	3	03/07/2018	Mar	2018	1	1	Wed	10	65	0	222	510	240	300

<tbody>
</tbody>

Here is a basic Pivot Table Set to the date of March 7:

Month	(All)
Week	(All)
DOW	Mon
Date	(Multiple Items)
Doctor	(All)
Row Labels	Sum of Shift1	Sum of Avail1	Sum of Utilization%
1	221	1020	22%
Grand Total	221	1020	22%

<tbody>
</tbody>

My problem is that only 510 minutes are available on this day. So the precentage show be higher (43%) but the available time is added up per entry. I want the table to show only a total available for the day (510 minutes), but also if the time frame is a whole week of dates the total minutes would be (7x510 or 3570 minutes for each OR room selected). I need the denominator to correspond to the days available in the choice of filters and not cumulative.

Further, it would be nice to be able to select the shift as well as one of the parameters.

Any ideas on how to go about this? I thought a more complex calculated field would help, but I don't even know where to begin...

Thanks for any assistance/advice offered.

Will

 

[Wolfster63]

Thought I'd repost this as the formatting did not come through correctly...apologies.




One thing I did try was to use MAX and MIN on the date fields to determine number of shifts being looked at and therefore get an accurate value:

=DAYS360(MIN(C3:C17),MAX(C3:C17))

It works on the spreadsheet, but not on the calculated field. I keep getting a Divided by 0 error.

+++++++++++

I have a spreadsheet that calculates the time a surgical case takes on a particular shift (Thanks @shg;!). I am attempting to create a Pivot table that could analyze how much of the available room time is taken in a particular room or group of rooms during a given time period

I inserted a calculated field that would render a percent of the room time used. Each room has a finite number of minutes that can be used on a particular day during a particular shift.

Here is a sample spreadsheet to demonstrate the spreadsheet structure:

Doctor	OR	SDate	Edate	Month	Year	Qtr	Count	DOW	Week	Shift1	Shift 2	Shift 3	Avail1	Avail2	Avail3
Kutt	1	01/02/2018	01/02/2018	Jan	2018	1	1	Tue	1	81	0	0	510	240	300
Slash	2	01/02/2018	01/02/2018	Jan	2018	1	1	Tue	1	88	0	0	510	240	300
Maim	3	01/02/2018	01/02/2018	Jan	2018	1	1	Tue	1	106	0	0	510	240	300
Slicer	1	02/04/2018	02/04/2018	Feb	2018	1	1	Sun	6	75	21	0	510	240	300
Slash	2	02/04/2018	02/04/2018	Feb	2018	1	1	Sun	6	72	111	0	510	240	300
Maim	2	02/05/2018	02/05/2018	Feb	2018	1	1	Mon	6	103	0	0	510	240	300
Kutt	3	02/05/2018	02/05/2018	Feb	2018	1	1	Mon	6	94	0	0	510	240	300
Kildare	3	02/05/2018	02/05/2018	Feb	2018	1	1	Mon	6	79	0	0	510	240	300
Slash	1	03/07/2018	03/07/2018	Feb	2018	1	1	Mon	6	163	0	0	510	240	300
Maim	1	03/07/2018	03/07/2018	Feb	2018	1	1	Mon	6	58	0	0	510	240	300
Slicer	2	03/07/2018	03/07/2018	Mar	2018	1	1	Wed	10	0	14	0	510	240	300
Slash	2	03/07/2018	03/07/2018	Mar	2018	1	1	Wed	10	160	0	58	510	240	300
Kutt	3	03/07/2018	03/07/2018	Mar	2018	1	1	Wed	10	143	37	12	510	240	300
Slash	3	03/07/2018	03/07/2018	Mar	2018	1	1	Wed	10	113	8	0	510	240	300
Maim	3	03/07/2018	03/07/2018	Mar	2018	1	1	Wed	10	65	0	222	510	240	300

<tbody>
</tbody>

Here is the Pivot table structure:

Month	(All)
Week	(All)
DOW	(All)
Doctor	(All)
SDate	(Multiple Items)
Edate	(All)
OR	Time Used	Sum of Avail1	Sum of Utilization%
1	221	1020	22%
2	160	1020	16%
3	321	1530	21%
Grand Total	702	3570	20%

<tbody>
</tbody>

My problem is that only 510 minutes are available for OR 3 on this day. So the precentage show be higher (43%) but the available time is added up per entry. I want the table to show only a total available for the day (510 minutes), but also if the time frame is a whole week of dates the total minutes would be (7x510 or 3570 minutes for each OR room selected). I need the denominator to correspond to the days available in the choice of filters and not cumulative.

Further, it would be nice to be able to select the shift as well as one of the parameters.

Any ideas on how to go about this? I thought a more complex calculated field would help, but I don't even know where to begin...

Thanks for any assistance/advice offered.

Will