Count of Value between Hours

[TitansGO]

I hope someone can help me out with a pivot table problem that I've yet tofigure out.

I have a table setup that counts the number of instances that a trailerdelivers on an hourly basis. E.g., we have 7 deliveries at 08:00 AM. Most ofour deliveries are given an hour to complete the unloading process, but we havea select few that are given two hours. Below is an example of how therelevant data is inputted into the sheet.


[TABLE="width: 574, align: left"]
<tbody>[TR]
[TD="width: 62"]Trailer
[/TD]
[TD="width: 68"]Version
[/TD]
[TD="width: 70"]Delivery
Freq
[/TD]
[TD="width: 70"]Delivery Days
[/TD]
[TD="width: 64"]Carrier
SCAC
Code
[/TD]
[TD="width: 92"]Commodity /
Supplier
[/TD]
[TD="width: 85"]Dock / Delivery Times
[/TD]
[TD="width: 117"]Unload/Reload
[/TD]
[TD="width: 70"]Delivery
Window
Start
[/TD]
[TD="width: 70"]Delivery
Window
End
[/TD]
[/TR]
[TR]
[TD]ZZZZ
[/TD]
[TD]1
[/TD]
[TD]5
[/TD]
[TD]MTWRF
[/TD]
[TD]ZZZZ
[/TD]
[TD="width: 92"]ZZZZ
[/TD]
[TD]132: 0:00-1:00
[/TD]
[TD="width: 117"]Unload / Reload
[/TD]
[TD="width: 70"]0:00
[/TD]
[TD="width: 70"]1:00
[/TD]
[/TR]
</tbody>[/TABLE]


The pivot table is essentially counting the number of trailers at the timedefined by delivery window start. I.e., DeliveryWindowStart as the row andcount of trailer as the values. The end result is like this...



[TABLE="width: 134"]
<tbody>[TR]
[TD="width: 96"]Window
[/TD]
[TD="width: 83"]Deliveries
[/TD]
[/TR]
[TR]
[TD]12 AM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1 AM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]2 AM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3 AM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]4 AM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5 AM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]6 AM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7 AM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]8 AM
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9 AM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]10 AM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11 AM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]12 PM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1 PM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]2 PM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3 PM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]4 PM
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5 PM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]6 PM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7 PM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]8 PM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9 PM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]10 PM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11 PM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD]21
[/TD]
[/TR]
</tbody>[/TABLE]





The exact problem is when the deliverywindow end isn’t an hour after the start. If they take 2 hours to work a trailer,then it wouldn’t actually depart until 2:00 AM using the example data inputabove. Below is how I would input it into the sheet.

[TABLE="width: 574, align: left"]
<tbody>[TR]
[TD="width: 62"]Trailer
[/TD]
[TD="width: 68"]Version
[/TD]
[TD="width: 70"]Delivery
Freq
[/TD]
[TD="width: 70"]Delivery Days
[/TD]
[TD="width: 64"]Carrier
SCAC
Code
[/TD]
[TD="width: 92"]Commodity /
Supplier
[/TD]
[TD="width: 85"]Dock / Delivery Times
[/TD]
[TD="width: 117"]Unload/Reload
[/TD]
[TD="width: 70"]Delivery
Window
Start
[/TD]
[TD="width: 70"]Delivery
Window
End
[/TD]
[/TR]
[TR]
[TD]ZZZZ
[/TD]
[TD]1
[/TD]
[TD]5
[/TD]
[TD]MTWRF
[/TD]
[TD]ZZZZ
[/TD]
[TD="width: 92"]ZZZZ
[/TD]
[TD]132: 0:00-2:00
[/TD]
[TD="width: 117"]Unload / Reload
[/TD]
[TD="width: 70"]0:00
[/TD]
[TD="width: 70"]2:00
[/TD]
[/TR]
</tbody>[/TABLE]




That means there’s actually a 2^ndtrailer being worked alongside the trailer that delivers at 1:00 AM. What Iwant my pivot table to show is below. How do I manipulate the pivot table tohelp me get there?

[TABLE="width: 134"]
<tbody>[TR]
[TD="width: 96"]Window
[/TD]
[TD="width: 83"]Deliveries
[/TD]
[/TR]
[TR]
[TD]12 AM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1 AM
[/TD]
[TD="bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD]2 AM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3 AM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]4 AM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5 AM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]6 AM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7 AM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]8 AM
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9 AM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]10 AM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11 AM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]12 PM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1 PM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]2 PM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3 PM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]4 PM
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5 PM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]6 PM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7 PM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]8 PM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9 PM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]10 PM
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11 PM
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD]21

[/TD]
[/TR]
</tbody>[/TABLE]

 

[mrshl9898]

Hi,

Where are the 1's coming from? If a truck arrives at 01:00 and leaves at 02:00, should that show up as 1 or 2?

I can't imagine a Pivot solution, outside of adding columns for each time and a 1 or 0 if within that window.

Doesn't look like a pivot is needed though. I'd list all the times and use a COUNTIFS, something like this, depending on what you need.


Starting in P2:

[TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]00:00:00[/TD]
[/TR]
[TR]
[TD="align: right"]00:01:00[/TD]
[/TR]
[TR]
[TD="align: right"]02:00:00[/TD]
[/TR]
[TR]
[TD="align: right"]03:00:00[/TD]
[/TR]
[TR]
[TD="align: right"]04:00:00

[/TD]
[/TR]
</tbody>[/TABLE]
Then Q2 =COUNTIFS(I:I,">"&P2,J:J,"<="&P2)

(this scenario would count the unit by departure time or in the process of loading for more than 1 hour units.)

 

[TitansGO]

Hi,

Where are the 1's coming from? If a truck arrives at 01:00 and leaves at 02:00, should that show up as 1 or 2?

I can't imagine a Pivot solution, outside of adding columns for each time and a 1 or 0 if within that window.

Doesn't look like a pivot is needed though. I'd list all the times and use a COUNTIFS, something like this, depending on what you need.


Starting in P2:

[TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]00:00:00[/TD]
[/TR]
[TR]
[TD="align: right"]00:01:00[/TD]
[/TR]
[TR]
[TD="align: right"]02:00:00[/TD]
[/TR]
[TR]
[TD="align: right"]03:00:00[/TD]
[/TR]
[TR]
[TD="align: right"]04:00:00
[/TD]
[/TR]
</tbody>[/TABLE]
Then Q2 =COUNTIFS(I:I,">"&P2,J:J,"<="&P2)

(this scenario would count the unit by departure time or in the process of loading for more than 1 hour units.)

TO answer your question for a truck from 0100 - 0200... that would be 1 truck at 0100. It would only count towards a 0200 if said truck had a loading time of 0100 - 0300. I.e., the count is based on the hour of the arrival time. Also, I'm not sure what it is your formula counts, but it doesn't seem to work for me.

I'd like to keep using the pivot table simply because of the ease of use for the end user. I created a dashboard built around that pivot table and would hate to scrap it if I know there's some way to incorporate what I'm looking for into said pivot.

Is there a way to perhaps work through this issue in Power Pivot? Kind of lost on how to proceed here.

 

[mrshl9898]

To go along the must be a pivot route, i'd add columns 00:00, 01:00 etc, starting in K1.

Then use: =IF(AND($I2<=K$1,OR($J2=K$1+(1/24),$J2=K$1+(2/24))),1,0)

You'd then need to add all those columns to the pivot as values, then drag values from columns to rows to flip it.

 

[mrshl9898]

Slight adjustment to deal with midnight and 2 hour unloads

=IF(AND(OR(K$1=(23/24),K$1=0),$I2=(23/24)),1,IF(AND($I2 < =K$1,OR($J2=K$1+(1/24),$J2=K$1+(2/24))),1,0))