Calculating a Run Time from values

**Rick_Stanich** · 03-20-2010, 11:58 AM

I have only found info on "time stamps" in excel, I am trying to take a series of time values and add them to find the "Run Time" of an application.
Example out put value Ignore anything in brackets():

Time
12:43:01:297 (Start)
Time
13:30:14:852 (Pause)
13:30:20:770 (Restart)
Time
13:31:44:611 (Pause)
13:32:38:698 (Restart)
Time
14:15:13:722 (End)

Total Run Time = ?
All of the data is in column A, the word "Time" is a word used to "find" the time strings with in another file, it can be ignored.

I am only concerned with hh:mm:ss the remaining value can be ignored.

Any hints, tips or examples are welcome.

**shg** · 03-20-2010, 01:41 PM

If you convert the times to something Excel recognizes, and the parenthetic stuff is is a separate column, then

=SUMIF(B1:B10, {"(End)","(Pause)"}, A1:A10) - SUMIF(B1:B10, {"(Start)","(Restart)"}, A1:A10) returns 01:32:12

Code:

       -----A------ ----B---- -----C------ -----------------------------------------------D------------------------------------------------
   1                            Elapsed:                                                                                                   
   2                          01:32:12.425 C2: =SUMIF(B4:B13, {"(End)","(Pause)"}, C4:C13) - SUMIF(B4:B13, {"(Start)","(Restart)"}, C4:C13)
   3       Text       Event       Time                                                                                                     
   4   Time                                C4 and down: =IF(OR(LEFT(A4)={"0","1"}), LEFT(A4, 8) + RIGHT(A4, 3)/86400000, 0)                
   5   12:43:01:297 (Start)   12:43:01.297                                                                                                 
   6   Time                                                                                                                                
   7   13:30:14:852 (Pause)   13:30:14.852                                                                                                 
   8   13:30:20:770 (Restart) 13:30:20.770                                                                                                 
   9   Time                                                                                                                                
  10   13:31:44:611 (Pause)   13:31:44.611                                                                                                 
  11   13:32:38:698 (Restart) 13:32:38.698                                                                                                 
  12   Time                                                                                                                                
  13   14:15:13:722 (End)     14:15:13.722

**Rick_Stanich** · 03-20-2010, 02:14 PM

Thnak you, I will continue with your suggestion and example on Monday.

**shg** · 03-20-2010, 02:21 PM

I used text to columns to separate the time string from the event type, and a formula to convert the time string to an Excel time. The elapsed time formula in the example is different from the formula in the post (it uses col C for the times).

Holler back if you have problems.

**Rick_Stanich** · 03-22-2010, 10:37 AM

Formulas! Argh!
Something is lost in translation. LOL

I attached a couple files, Column A is added, Column B is as imported.
The number of rows will vary.
Sheet1 is my data, ignore sheet 2, sheet3 is where I tried to duplicate your formula scenerio.

Any hints, tips or examples are welcome.

With one of your formulas

Code:

=IF(OR(LEFT(A26)={"0","1"}), LEFT(A26, 8) + RIGHT(A26, 3)/86400000, 0)

I get #VALUE on rows 26 and 29?
Edit: Figured that out, your code seems to want specific character length such as:
14:13:28:000
rather than
14:13:28:00