Calculating a Run Time from values

**Rick_Stanich** · 03-20-2010, 10: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, 12: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

Please Login or Register  to view this content.

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

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

**shg** · 03-20-2010, 01: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, 09: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

Please Login or Register  to view this content.

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