Wolfster63
New Member
- Joined
- May 2, 2018
- Messages
- 24
Thought I'd repost this as the formatting did not come through correctly in the original post...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 in the Pivot Table. 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:[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl72"]Doctor
[/TD]
[TD="class: cms_table_xl72, width: 128, bgcolor: #8DB4E2"]OR
[/TD]
[TD="class: cms_table_xl73, width: 128, bgcolor: #8DB4E2"]SDate
[/TD]
[TD="class: cms_table_xl72, width: 128, bgcolor: #8DB4E2"]Edate
[/TD]
[TD="class: cms_table_xl72, width: 75, bgcolor: #8DB4E2"]Month
[/TD]
[TD="class: cms_table_xl72, width: 75, bgcolor: #8DB4E2"]Year
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Qtr
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Count
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]DOW
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Week
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Shift1
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Shift 2
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Shift 3
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Avail1
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Avail2
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Avail3
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Kutt
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Jan
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Tue
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]81
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Jan
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Tue
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]88
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Maim
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Jan
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Tue
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]106
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slicer
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/04/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/04/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Sun
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]75
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]21
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/04/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/04/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Sun
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]72
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]111
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Maim
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]103
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Kutt
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]94
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Kildare
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]79
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]163
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Maim
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]58
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slicer
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]10
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]14
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]10
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]160
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]58
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Kutt
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]10
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]143
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]37
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]12
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]10
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]113
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]8
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Maim
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]10
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]65
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]222
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
</tbody>[/TABLE]
Here is the Pivot table structure:
[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl65"]Month
[/TD]
[TD="class: cms_table_xl65, width: 125"](All)
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 131, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]Week
[/TD]
[TD="class: cms_table_xl65"](All)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]DOW
[/TD]
[TD="class: cms_table_xl65"](All)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]Doctor
[/TD]
[TD="class: cms_table_xl65"](All)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]SDate
[/TD]
[TD="class: cms_table_xl65"](Multiple Items)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]Edate
[/TD]
[TD="class: cms_table_xl65"](All)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]OR
[/TD]
[TD="class: cms_table_xl65"]Time Used
[/TD]
[TD="class: cms_table_xl65"]Sum of Avail1
[/TD]
[TD="class: cms_table_xl65"]Sum of Utilization%
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]221
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]1020
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent, align: right"]22%
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]160
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]1020
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent, align: right"]16%
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]321
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]1530
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent, align: right"]21%
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67"]Grand Total
[/TD]
[TD="class: cms_table_xl65, align: right"]702
[/TD]
[TD="class: cms_table_xl65, align: right"]3570
[/TD]
[TD="class: cms_table_xl68, align: right"]20%
[/TD]
[/TR]
</tbody>[/TABLE]
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?
Thanks for any assistance/advice offered.
Will
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 in the Pivot Table. 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:[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl72"]Doctor
[/TD]
[TD="class: cms_table_xl72, width: 128, bgcolor: #8DB4E2"]OR
[/TD]
[TD="class: cms_table_xl73, width: 128, bgcolor: #8DB4E2"]SDate
[/TD]
[TD="class: cms_table_xl72, width: 128, bgcolor: #8DB4E2"]Edate
[/TD]
[TD="class: cms_table_xl72, width: 75, bgcolor: #8DB4E2"]Month
[/TD]
[TD="class: cms_table_xl72, width: 75, bgcolor: #8DB4E2"]Year
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Qtr
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Count
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]DOW
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Week
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Shift1
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Shift 2
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Shift 3
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Avail1
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Avail2
[/TD]
[TD="class: cms_table_xl72, width: 64, bgcolor: #8DB4E2"]Avail3
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Kutt
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Jan
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Tue
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]81
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Jan
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Tue
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]88
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Maim
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]01/02/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Jan
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Tue
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]106
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slicer
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/04/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/04/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Sun
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]75
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]21
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/04/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/04/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Sun
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]72
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]111
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Maim
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]103
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Kutt
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]94
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Kildare
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]02/05/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]79
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]163
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Maim
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Feb
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mon
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]6
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]58
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slicer
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]10
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]14
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]10
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]160
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]58
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Kutt
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]10
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]143
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]37
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]12
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Slash
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]10
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]113
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]8
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, bgcolor: transparent"]Maim
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]03/07/2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Mar
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]2018
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]Wed
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent"]10
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]65
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_xl70, bgcolor: transparent"]222
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]510
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]240
[/TD]
[TD="class: cms_table_xl71, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
</tbody>[/TABLE]
Here is the Pivot table structure:
[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl65"]Month
[/TD]
[TD="class: cms_table_xl65, width: 125"](All)
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 131, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]Week
[/TD]
[TD="class: cms_table_xl65"](All)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]DOW
[/TD]
[TD="class: cms_table_xl65"](All)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]Doctor
[/TD]
[TD="class: cms_table_xl65"](All)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]SDate
[/TD]
[TD="class: cms_table_xl65"](Multiple Items)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]Edate
[/TD]
[TD="class: cms_table_xl65"](All)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]OR
[/TD]
[TD="class: cms_table_xl65"]Time Used
[/TD]
[TD="class: cms_table_xl65"]Sum of Avail1
[/TD]
[TD="class: cms_table_xl65"]Sum of Utilization%
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, bgcolor: transparent"]1
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]221
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]1020
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent, align: right"]22%
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]160
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]1020
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent, align: right"]16%
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66, bgcolor: transparent"]3
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]321
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]1530
[/TD]
[TD="class: cms_table_xl68, bgcolor: transparent, align: right"]21%
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67"]Grand Total
[/TD]
[TD="class: cms_table_xl65, align: right"]702
[/TD]
[TD="class: cms_table_xl65, align: right"]3570
[/TD]
[TD="class: cms_table_xl68, align: right"]20%
[/TD]
[/TR]
</tbody>[/TABLE]
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?
Thanks for any assistance/advice offered.
Will