Adding time from a list of starts and stops to come up with a single start/stop

[kgartin]

I have start times listed in column A and corresponding stop times in column B

I need a macro to calculate all the elapsed time and create a new stop time adding all of the elapsed time together.

So if:

A1 = 12:00 PM
B1 = 1:15 PM

A2 = 1:30 PM
B2 = 2:15 PM

A3 = 10:00 PM
B3 = 1:30 AM

Total elapsed time = 5.5 hours

Need code to give original start time (12:00 PM from A1) but come up with new stop time of 5:30 PM based on the elapsed time(s). I'll be assigning the result to a variable.

How do I calculate this properly? It sounds simple but for some reason it's twisting me up...

 

[kgartin]

Batting 0 lately...



anyone?

 

[Marcelo Branco]

Maybe...

Sub aTest()
    Dim LR As Long
    Dim dbStart As Double, dbElapsed As Variant, dbStop As Double
        
    dbStart = Range("A1")
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    dbElapsed = Evaluate("=SUMPRODUCT(MOD(B1:B" & LR & "-A1:A" & LR & ",1))")
    dbStop = dbStart + dbElapsed
    MsgBox Format(dbStop, "hh:mm AM/PM")
End Sub

Hope this helps

M.

 

[kgartin]

PERFECT! Thanks so much!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.