Determine total duration in hours, minutes and seconds

[MartinLiss]

I have a worksheet where there are From and To times like 4:37:04 PM and 4:39:20 PM in several rows. The times are based on Now() with a NumberFormat of "h:mm:ss am/pm". The To time is triggered by a timer that runs every second and in the Worksheet_Change event for the worksheet I easily determine the duration when the To time changes with this code.

dteDiff = Cells(Target.Row, "G") - Cells(Target.Row, "F")
lngHours = CLng(Hour(dteDiff))
lngMinutes = CLng(Minute(dteDiff))
lngSeconds = CLng(Second(dteDiff))

And using those values the Duration comes out as 2 min 16 sec using this code

Cells(Target.Row, "H") = lngMinutes & " min " & lngSeconds & " sec"

I would also like to produce a total duration for all the rows. How would I do that in some efficent way given that the code is running every second?

[Zvoni]

1) why are you using Now if you only want Time (hint hint)?
2) Date/Time are doubles. Just subtract one from the other and format to your liking
3) instead of splitting elapsed time into hh mm ss use a single variable. Then you just add up and format to your liking

[MartinLiss]

Thank you for your suggestions, but the devil is in the details; if, say, I have the total of seconds in all the rows, how can I correctly format them the way I show above?

[Zvoni]

5585937[/URL]]Thank you for your suggestions, but the devil is in the details; if, say, I have the total of seconds in all the rows, how can I correctly format them the way I show above?

If i was unclear in point 3: use a single floating point variable (Double), and format just for output.
Just add up the floating point values as any other arithmetic operation, and format the output. If you still need separate hh, mm and ss you can still use Hour, Minute and Second Functions

EDIT: declare a Variable as Static Double inside your change-event
Aircode

Private Sub Worksheet_Change(Target As Range)
Static dElapsed As Double
  dteDiff = ToRange - FromRange
  ˋDisplay minutes and seconds  for Target.Row
  dElapsed = dElapsed + dteDiff
  Range(„A1“) = Format(dElapsed, „hh:mm:ss“)
End Sub