Timeline help!

[Sunshack]

Hi!

I have a table containing:

• Order picklist creation time (format HHMMSS, forexample: 123109)
• Order picklist confirmed time (format HHMMSS, forexample: 132954)
If the time is less than 10:00:00, the time only has 5 numbers: Forexample if the confirmed date is 09:54:31, it will say 95431 in the table.

My goal is to make a timeline/graph that shows how many unconfirmed/open order picklists that exists at all times during the day. So a y-axis with the number of picklists, and the x-axis is the timeline/hours during the day.
If it makes it any easier, I could group it by 15 or 30 minute intervals.

I really appreciate all the help I can get!

[SimonLock]

I have attached a solution.

The first step is to convert your timestamps to a usable number, which I have done using the Len(), Left() and Mid() functions.

Then I have added a rolling record of open/close instances, which maintains a count of open picklists.

The final problem is that Excel doesn't do step charts, so I have had to format the data in such a way that the chart shows a step change rather than diagonal lines between points, hence the 2 points for each number.

Hope this helps.

[queuesef]

Copy this formula in Cell G2, then copy down.

Please Login or Register  to view this content.

OR alternatively, you can create a helper column in column F

Please Login or Register  to view this content.

~ which will put the data in a Uniform format of 6 characters as TEXT, then you can pull the Time pieces (Hour, Minute, Seconds) from that column

[Sunshack]

Thank you so much for your time and effort!

I have to admit that these formulas are way above my user level, and I need to do some research on my own to fully comprehend exactly whats happening in the solution you made
But it sure looks like exactly the results I was looking for, so I thank you again!!