Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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. #1
    New Member
    Join Date
    Mar 2012
    Posts
    6

    Default 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. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    7,084

    Default 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 ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    New Member
    Join Date
    Mar 2012
    Posts
    6

    Default 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. #4
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    7,084

    Default 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 ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  5. #5
    New Member
    Join Date
    Mar 2012
    Posts
    6

    Default 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.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

  6. #6
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    7,084

    Default Re: Subtracting time in milliseconds - Excel 2010

    OK so just to be super clear, what does a single cell contain ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  7. #7
    New Member
    Join Date
    Mar 2012
    Posts
    6

    Default 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. #8
    Board Regular diddi's Avatar
    Join Date
    May 2004
    Location
    Shepparton, Australia
    Posts
    2,430

    Default 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
    The more you learn, the more you realise how little you know. Excel 2003 and 2010 / Win7
    Code:
    Sub HintForGoodResults( )
        If yourcode Is indented Then
            "it is easier to for everyone to understand" AND "others are more likely to give assistance"
        Else
            "errors are much harder to find" OR "others dont bother"
        End If
    End Sub

  9. #9
    New Member
    Join Date
    Mar 2012
    Posts
    6

    Default 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. #10
    Board Regular FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    919

    Default Re: Subtracting time in milliseconds - Excel 2010

    Quote Originally Posted by wireless73 View Post
    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

    AB
    111:18:59.550
    211:18:59.5660.016

    Spreadsheet Formulas
    CellFormula
    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 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com