# Subtracting time in milliseconds - Excel 2010

This is a discussion on Subtracting time in milliseconds - Excel 2010 within the Excel Questions forums, part of the Question Forums category; Hello all, first time poster. My challenge is discovering the difference in time between two rows of data that is ...

1. ## Subtracting time in milliseconds - Excel 2010

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

2. ## Re: Subtracting time in milliseconds - Excel 2010

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 ?

3. ## Re: Subtracting time in milliseconds - Excel 2010

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.

4. ## Re: Subtracting time in milliseconds - Excel 2010

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 ?

5. ## Re: Subtracting time in milliseconds - Excel 2010

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.270|098,1
2012 02 17 11:19:27.270|099,MCTO05234CA0~999999999999
2012 02 17 11:19:27.285|099,3

6. ## Re: Subtracting time in milliseconds - Excel 2010

OK so just to be super clear, what does a single cell contain ?

7. ## Re: Subtracting time in milliseconds - Excel 2010

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

2012 02 17 11:19:22.864|095,999999999999~xxxxxxxxx300

8. ## Re: Subtracting time in milliseconds - Excel 2010

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

9. ## Re: Subtracting time in milliseconds - Excel 2010

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

10. ## Re: Subtracting time in milliseconds - Excel 2010

Originally Posted by wireless73
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:

Sheet1

 A B 1 11:18:59.550 2 11:18:59.566 0.016

 Cell Formula B2 =IF(TEXT(A2-A1,"[s].000")+0>5,"Max Reached",TEXT(A2-A1,"[s].000")+0)

Excel tables to the web >> Excel Jeanie HTML 4

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•