Subtracting time in milliseconds - Excel 2010

wireless73

New Member
Joined
Mar 2, 2012
Messages
6
Hello all, first time poster. My challenge is discovering the difference in time between two rows of data that is imported with milliseconds in the time slot. In my example below I would like to see how I can subtract the time listed in line 4 from line 3 (11:18:59.566 - 11:18:59.550). When I try now I get either 1.85185E-07 or 00:00.0 or 0.000000 depending how the cell is formatted. Any help or ideas would be appreciated. Thanks

2012 02 17 11:18:59.050|081,999999999999~999999999999
2012 02 17 11:18:59.550|082,MCTO05222CZ0~999999999999
2012 02 17 11:18:59.566|082,1

2012 02 17 11:19:00.144|083,MCTO05246CK0~999999999999
2012 02 17 11:19:00.160|083,3
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hi, welcome to the board.

Just to be clear, what should the result be ?
Should it be 6 ?
And what if the input data was, say, 11:18:59.566 - 11:17:59.550 - what would you want the result to be then ?
 
Upvote 0
I would like the result to be .016 (.566-.550)

Good question on your example, thanks. Anything longer than 5 seconds (i.e. 11:18:06.566 - 11:17:59.550) can reply back "NULL" or "MAX reached" etc... something that would just tell me, hey this took longer than 5 seconds.
 
Upvote 0
Sorry, I should have said 16, not 6, I didn't read your post properly.

Is ALL of this data . . .
2012 02 17 11:18:59.050|081,999999999999~999999999999
in a single cell ?

If yes, then we've got another task, to isolate the time data. Which can probably be done . . .

Also, do we need to work out which rows to subtract from which ?

Are we just subtracting every row from the one below ?
 
Upvote 0
I have already imported the data in excel and did text to columns, the time is in its own cell. More raw data is below. Essentially I need to weed out the rows that do not have a unique identifier to the one below it. So, for example, the line/rows in blue will be deleted as will all other rows that do not have an equal unique identifier (shown in red). I just highlighted the first few, not the whole example but I believe you will get the idea.

Unfortunately the unique numbers in red start to repeat after 999 so that poses an added step as well.

The answer, I would like at least, to be .016 in the example versus a single digit just so I can understand what my averages are and the like. I only need to subtract the differences of the time cells that have matching unique id's. For example line three below from line 2. Then subtract line five from line four etc..... I have about 40K rows of data per workday and five work days to work through. Also I am leaving for the day but will check back on this post in the next few hours. Thank you in advance.

2012 02 17 11:18:59.050|081,999999999999~999999999999
2012 02 17 11:18:59.550|082,MCTO05222CZ0~999999999999
2012 02 17 11:18:59.566|082,1
2012 02 17 11:19:00.144|083,MCTO05246CK0~999999999999
2012 02 17 11:19:00.160|083,3
2012 02 17 11:19:01.379|No new data in last ten attempts
2012 02 17 11:19:02.754|No new data in last ten attempts
2012 02 17 11:19:04.160|No new data in last ten attempts
2012 02 17 11:19:05.535|084,MCTO05225CK0~999999999999
2012 02 17 11:19:05.551|084,3
2012 02 17 11:19:06.144|085,999999999999~999999999999
2012 02 17 11:19:06.754|086,MCTO05133BJ0~999999999999
2012 02 17 11:19:06.769|086,10
2012 02 17 11:19:08.051|No new data in last ten attempts
2012 02 17 11:19:09.269|087,999999999999~xxxxxxxxx300
2012 02 17 11:19:09.285|087,3
2012 02 17 11:19:10.551|No new data in last ten attempts
2012 02 17 11:19:11.879|No new data in last ten attempts
2012 02 17 11:19:13.254|No new data in last ten attempts
2012 02 17 11:19:14.145|088,MCTO05117AV0~999999999999
2012 02 17 11:19:14.160|088,10
2012 02 17 11:19:15.363|No new data in last ten attempts
2012 02 17 11:19:16.254|089,999999999999~xxxxxxxxx300
2012 02 17 11:19:16.285|089,3
2012 02 17 11:19:17.160|090,MCTO05225AR0~999999999999
2012 02 17 11:19:17.191|090,1
2012 02 17 11:19:17.863|091,MCTO05144BF0~999999999999
2012 02 17 11:19:17.942|091,10
2012 02 17 11:19:19.145|No new data in last ten attempts
2012 02 17 11:19:19.363|092,MCTO05336BX0~999999999999
2012 02 17 11:19:19.457|092,1
2012 02 17 11:19:20.645|No new data in last ten attempts
2012 02 17 11:19:20.754|093,MCTO05226BL0~999999999999
2012 02 17 11:19:20.770|093,1
2012 02 17 11:19:21.770|094,MCTO05226AM0~999999999999
2012 02 17 11:19:21.785|094,10
2012 02 17 11:19:22.864|095,999999999999~xxxxxxxxx300
2012 02 17 11:19:22.957|095,3
2012 02 17 11:19:23.395|096,999999999999~999999999999
2012 02 17 11:19:24.660|No new data in last ten attempts
2012 02 17 11:19:25.145|097,MCTO05125AH0~999999999999
2012 02 17 11:19:25.160|097,10
2012 02 17 11:19:26.254|098,MCTO05235AD0~999999999999
2012 02 17 11:19:26.270|098,1
2012 02 17 11:19:27.270|099,MCTO05234CA0~999999999999
2012 02 17 11:19:27.285|099,3
 
Upvote 0
One cell is date, like in red.
Next is the time, the blue one,
then the unique in purple,
the "9999's or the MCTO or that single digit" in green
and lastly either the second orange 99999's or the xxx300

I have only taken out the rows that read "No new data in last ten attempts". I can easily start it over if that helps in solving the math and weeding out the uniques that don't have a matching data row underneath them.

2012 02 17
11:18:59.050|081,999999999999~999999999999
2012 02 17 11:18:59.550|
082,MCTO05222CZ0~999999999999
2012 02 17 11:18:59.566|
082,1
<snip>
2012 02 17 11:19:22.864|095,999999999999~xxxxxxxxx300
 
Upvote 0
i am going to assume that the data feed is rapid (ie a maximum of a few seconds between reading) and also i assume that the time component is always in the format:
hh.mm.ss.xxx (ie 1.23.34.543 would be 01.23.34.543)

using strings firstly: this will find the difference between 2 rows (eg row 2 and 1 in "A")
so this might go in column "C", and fill down

Code:
=value(mid(A2,18,6) - value(mid(A1,18,6))

there is a problem with this which occurs at transitions from :59 to :00
but if this is on the right track, we can do impropvements or use vba
 
Upvote 0
Thanks for the attempt however I just got #VALUE! when I put in the formula. The string data is in format hh:mm:ss.xxx

If it is easier I don't mind rounding up to the tenth the last three digits if that helps.

hh:mm:ss.xxx
 
Upvote 0
I would like the result to be .016 (.566-.550)

Good question on your example, thanks. Anything longer than 5 seconds (i.e. 11:18:06.566 - 11:17:59.550) can reply back "NULL" or "MAX reached" etc... something that would just tell me, hey this took longer than 5 seconds.

Maybe this will work for you:

Excel Workbook
AB
111:18:59.550
211:18:59.5660.016
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top