G

#### Guest

I'm trying to calculate an aggregate time for stages in a process.

I have a start and stop time in julian date format in an array that I have

split from a text string in a cell.

ie A1 ="39196.85 39199.285 39199.353"

VBA

mytimearray = split("39196.85 39199.285 39199.353", " ")

I then want to calculate the difference in hours:minutes:seconds

ie I want 2 days 10 hours 26minutes 24seconds represented as 58:26:24

This code is in a loop so If i find the same process code I then want to be

able to add another time to it, ie calculate another time eg 39199.353 -

39199.285 = 01:38:55 and add it to 58:26:24

to get 60:05:19

Code so far

set ws = thisworkbook.worksheets("sheet1")

X =1: U=1:V=1

While ws.Cells(X,1 ).Value <> ""

MyCodeArray = Split(ws.Cells(X, 1).Value, " ")

MyTimeArray = Split(ws.Cells(X, 2).Value, " ")

'A1 ="39196.85 39199.285 39199.353"

'B1 = "1 2 4"

For Y = 1 To UBound(MyCodeArray)

Select Case MyCodeArray(Y)

Case Is = "1", "2"

' This is where I have lots of problems

T_ime1 = T_ime1 + (MyTimeArray(Y) - Mytimearray(Y+1))

Case Is = "3", "4"

T_ime2 = T_ime2 + (MyTimeArray(Y) - Mytimearray(Y+1))

Case else

End Select

next Y

'Then output the agragate time to the sheet

If Time1 > "" Then ws.Cells(U, 4).Value = Time1: U = U + 1: Time1 = 0

If Time2 > "" Then ws.Cells(V, 6).Value = Time2: V = V + 1: Time2 = 0

X=X+1

Wend

.....I then go on to sort the aggregate times to get the medain

...... any help would be unbelivably appreciated...

I've tried formatting T_ime as "[h]:mm:ss" but get problems when adding

another time. I'm not sure what type of variable to declare t_ime1 so have

declared it as a variant

Thanks Anthony