Complicated Graphing

[specopsniper]

12/7/2015	12:53:39PM <tbody> </tbody>	100213655
12/7/2015	12:54:37PM	100213135
12/7/2015	12:54:57PM	100213548
12/7/2015	12:54:59PM	100213545
12/8/2015	9:55:37PM	100213598
12/8/2015	9:56:32PM	100213566
12/8/2015	9:56:22PM	100213536
12/8/2015	9:56:12PM	100216565
12/8/2015	9:57:37PM	100213656
12/9/2015	10:53:37PM	100268423
12/9/2015	10:51:17PM	100213507

<tbody>
</tbody>

Above graph is a small portion, the ID numbers on the right correlate showing we have 4 scans per day... when in fact the number will be closer to 1000 over an 8 hour period.

This is what my data table looks like (formatted scanner)

I can create a daily graph to show me the total amount of pods built per day, no problem.

What I want is a second graph that is purely for daily use, preferably line graph

I am trying to figure out how to incorporate it to show the total amount every 30 minutes like a stock exchange graph that populates throughout the day as 30 minutes where the far left of the graph shows say 9AM with 0 ID's, and the far right as 5PM with 1000, and have 30 minute increments showing an increasing total scanned. The scanners will be programmed as above to enter them in real time, I want to be able to refresh the graph an instantly see a report for the day so far.

=SUMPRODUCT(--(B:B=TODAY())) Which gives me total for a daily, I just don't know how to refine this to show 30min interval totals throughout the day into a line graph.

ex: 2:30:00am the total was 213, 3:00:00am the total was 276, 3:30:00am the total was 345.... and have that as a graph

Any help or ideas would be awesome, even if I have to refine my own data points (reprogram scanner to having data in one column to sort)

 

[SpillerBD]

When I copied your above data to Excel, my first problem was that the Time needed to converted to a time value and then combined with the date to a have a single data point to represent the Date and Time.
I then used MROUND to round each value to the nearest half hour point.
Put into a Pivot Table with the MROUNDed Date&Time value as the Column headers. Then Chart the PivotTable.

 

[specopsniper]

03/11/2015,23:54:21,HB04100239944
03/11/2015,23:54:31,HB04100238576
03/11/2015,23:54:34,HB04100242658
03/11/2015,23:54:42,HB04100263479
03/11/2015,23:54:48,HB04100266347
03/11/2015,23:54:56,HB04100263483
03/11/2015,23:55:07,HB04100242037
03/11/2015,23:55:11,HB04100266229

This is how scanner initially enters information before I alter it using Special Paste Formatting... " {DATE,TIMESTAMP,ScannedBarcode} {ENTER} <date, timestamp,="" scanned="" barcode=""><enter>" </enter></date,>making multiple rows, only 1 column. let me play around with MROUND right now to test it.

Of course the barcode doesn't matter for this, it it used elsewhere but since its scanned with it i just place it in and hope that it doesn't get calculated in and just overlook that for now.

 

[specopsniper]

Still need help (I know it wont be quick)...

It is complicated but I converted 1 column into

03/11/2015,20:07:19

<tbody>
</tbody>

using =IF(A2>19,(LEFT(A2,LEN(A2)-14)))

and further into " 42074,0.838414351851852 " Datevalue, timevalue using =DATEVALUE(LEFT(B2,10))&","&(TIMEVALUE(RIGHT(B2,8)))

I need help converting and understanding how column headers work and rounding this number to actually place these into 30min intervals... if anyone can help me from this step

 

[specopsniper]

Current:

I have a formula bar that finally equates from

03/11/2015,20:09:21,HB04100229973 into 42074,0.833333332

03/11/2015,20:07:19,HB04100223271 <tbody> </tbody>

=IF(A2>19,(LEFT(A2,LEN(A2)-14))) <tbody> </tbody>

=DATEVALUE(LEFT(B2,10))&","&(TIMEVALUE(RIGHT(B2,8))) <tbody> </tbody>

=LEFT(C2, 5)&","&MROUND(RIGHT(C2,LEN(C2)-6), 0.0208333333) <tbody> </tbody>

<tbody>
</tbody>

*some of this is redundant and I will remove later and clean up*

uses multiple fields but last formula is "=LEFT(C4, 5)&","&MROUND(RIGHT(C4,LEN(C4)-6), 0.0208333333)"

So my idea for a graph, This gives me the values I need, I needs to figure out how to make a graph that shows... =SUM of COUNT(TRUE FALSE VALUE for DATE 'or left 5, w/e' , less than or equal to TIME VALUE) The goal basically being to show each 30min increment that has already been established, it shows the total count of numbers from the current day and earlier. that means that by the last submission the value will show the =COUNTIF as the time value and everything before that for the current DATE...

This is a lot to ask and I am trying to be as... detailed as possible because I am burnt out today and I will check back later on this and someone is gonna have a eureka moment while reading this..

Anyone replying to this, be detailed and copy-pasta as I might look like i know something about this... I really do not...

 

[SpillerBD]

From your data posted in #3, I used Text to columns with delimiter comma, I got good date and time and just added those together.
Your formula is close, but try =DATEVALUE(LEFT(A2,10))+TIMEVALUE(MID(A2,12,8))
Two Different MROUNDS, where F2 would be the cell where the date and time reference have been added together.
=MROUND(F2,1/48)
or
=MROUND(F2-(1/96),1/48)

Depending on your version of Excel, 2007, 2010 or 2013, I think you can easily get your "SUM of Count" in a Pivot Table and then Pivot Chart. You can skip to Pivot Chart too.
Pivot Tables give you a filter option for dates, but from 2013 there is a TimeLine feature for Pivot Tables that simplifies that in many ways.
Lastly would be a PivotTable feture called Grouping for number or dates/Times that allows you your complete grouping that still totals the figures outside your target range.

 

[SpillerBD]

I was just thinking it might be as easy to have an additional filed in the table that calculates the difference between the Date part and TODAY(). Use that value as a FILTER and set that filter to "0"
I think that would be a lot easier than an Event to update a filter....

 

[specopsniper]

Thank you again SpillerBD, made a very simple =IF(E1=TODAY(),1,0) and filtered to that, now I am just testing and hopefully it all works out.