Time Aggregate in [h]:mm:ss format in a loop & julian date

[Guest]

Hi Everyone

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

 

[NickHK]

Not sure on your logic, as you have 3 codes, but only 2 time differences.
So does the code apply from the upper or lower date/time ?
Also, as you times are in ascending, you need to switch them in the
difference calculation.

NickHK

 

[Guest]

Thanks for your time NicK I'll try to explain more....

I tried to water down the code and have made several mistakes apologies...

I should have started the for loop at 0.... as the "split" creates a 0 based
array....

and I got my code array/ time array the wrong way round
MyCodeArray = Split(ws.Cells(X, 2).Value, " ")
Mytimearray = Split(ws.Cells(X, 1).Value, " ")

there are usually alot more codes and the timestamp is the time the job went
into a stage...hence code 2's start time is also code 1's finish time. also
the final code code 4 is the "job completed" code so I shouldn't really be
trying to perform a date time calculation on that code as you rightly point
out there is no finish time for that code.

Funnily enough in correcting my watered down code I've actually found the
solution...after I did the calculation in my old code I was trying to change
the result into [h]:mm:ss format and then preform another calculation... the
solutionwas to leave the result of the time calculation in the decimal format
and just add to that the result of the next date calculation once I'd
finished adding to the result I could then output the total to a cell
formated [h]:mm:ss

Again thanks for your time

Not sure on your logic, as you have 3 codes, but only 2 time differences.
So does the code apply from the upper or lower date/time ?
Also, as you times are in ascending, you need to switch them in the
difference calculation.

NickHK

Hi Everyone

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

 

[NickHK]

Not that I did much, but sometimes you just need to take a step back for a
moment.

NickHK

Thanks for your time NicK I'll try to explain more....

I tried to water down the code and have made several mistakes apologies...

I should have started the for loop at 0.... as the "split" creates a 0 based
array....

and I got my code array/ time array the wrong way round
MyCodeArray = Split(ws.Cells(X, 2).Value, " ")
Mytimearray = Split(ws.Cells(X, 1).Value, " ")

there are usually alot more codes and the timestamp is the time the job went
into a stage...hence code 2's start time is also code 1's finish time. also
the final code code 4 is the "job completed" code so I shouldn't really be
trying to perform a date time calculation on that code as you rightly point
out there is no finish time for that code.

Funnily enough in correcting my watered down code I've actually found the
solution...after I did the calculation in my old code I was trying to change
the result into [h]:mm:ss format and then preform another calculation... the
solutionwas to leave the result of the time calculation in the decimal format
and just add to that the result of the next date calculation once I'd
finished adding to the result I could then output the total to a cell
formated [h]:mm:ss

Again thanks for your time

Not sure on your logic, as you have 3 codes, but only 2 time differences.
So does the code apply from the upper or lower date/time ?
Also, as you times are in ascending, you need to switch them in the
difference calculation.

NickHK

Hi Everyone

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