After years of lurking this board and finding everything I need and then some I am finally stumped.
My simplified issue is that I have historical order data detailing a unique order number, the date/time the order was created, and the date/time the order was closed (shipped). I'm attempting to use this data to determine where available orders spike throughout the day, in an attempt to correlate this data to the scheduling of labor, and hopefully identify if bringing in an early shift actually reduces the number of available orders later in the day.
I have already been able to chart strictly on the time an order was received (morning biased trend), and individually on when an order was closed (evening biased trend) however this makes it difficult to determine exactly how many orders are available at any hour, as some orders may take days to complete. For the life of me I can't figure out how to get the order below to register as a count of 1 for every hour it is open without making 24 helper colums for each hour of the day.
Basic fields being used are below:
[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD]Order Number
[/TD]
[TD]Time In
[/TD]
[TD]Time Out
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]01/05/2013 08:53
[/TD]
[TD]01/05/2013 17:53
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
In the simplified version it may appear that the open and closed should be clear enough, but in the more complex real-life version I'm attempting to use the logic to break down exactly which parts of the order fulfillment process have bottlenecks throughout the day. (Ex. at noon, Repair has 100 orders, Programming has 50, shipping has 100; so if Programming could assist repair they may be able to overcome lulls throughout the day). Each of 10 departments have In and Out times, so you can see that 240 helper columns could quickly get out of control.
Thoughts that have crossed my mind:
I'm hoping that someone can make me look like a complete fool by pointing me towards a simple function of Excel that escapes me. I am currently using Excel 2010 in an XLSB format due to the massive size of the data (just under 1 million records) but I'm willing to look at other options. Any thoughts?
My simplified issue is that I have historical order data detailing a unique order number, the date/time the order was created, and the date/time the order was closed (shipped). I'm attempting to use this data to determine where available orders spike throughout the day, in an attempt to correlate this data to the scheduling of labor, and hopefully identify if bringing in an early shift actually reduces the number of available orders later in the day.
I have already been able to chart strictly on the time an order was received (morning biased trend), and individually on when an order was closed (evening biased trend) however this makes it difficult to determine exactly how many orders are available at any hour, as some orders may take days to complete. For the life of me I can't figure out how to get the order below to register as a count of 1 for every hour it is open without making 24 helper colums for each hour of the day.
Basic fields being used are below:
[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD]Order Number
[/TD]
[TD]Time In
[/TD]
[TD]Time Out
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]01/05/2013 08:53
[/TD]
[TD]01/05/2013 17:53
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
In the simplified version it may appear that the open and closed should be clear enough, but in the more complex real-life version I'm attempting to use the logic to break down exactly which parts of the order fulfillment process have bottlenecks throughout the day. (Ex. at noon, Repair has 100 orders, Programming has 50, shipping has 100; so if Programming could assist repair they may be able to overcome lulls throughout the day). Each of 10 departments have In and Out times, so you can see that 240 helper columns could quickly get out of control.
Thoughts that have crossed my mind:
- Use a MOD function to extract the times from Date/Time, then use a formula identify if 1pm,2pm, 3pm etc. is between start and end times, but how could I get this to reflect on a pivot chart for each hour that an order is active?
- Curl up in a ball under my desk.
I'm hoping that someone can make me look like a complete fool by pointing me towards a simple function of Excel that escapes me. I am currently using Excel 2010 in an XLSB format due to the massive size of the data (just under 1 million records) but I'm willing to look at other options. Any thoughts?
