[RESOLVED] [EXCEL] Find then subtract

[anhn]

The use of DateDiff() is complicated in this case.

Option Explicit

Private Sub CommandButton1_Click()
    Dim Status  As String
    Dim dStart  As Date
    Dim Elapsed As Date
    Dim r       As Long
    
    Status = Cells(2, 2)
    dStart = Cells(2, 3)
    r = 3
    Do While Cells(r, 2) <> ""
        If Cells(r, 2) <> Status Then
            Elapsed = Cells(r, 3) - dStart
            Cells(r, 4) = Int(Elapsed) '-- extract days as the integer part of Elapsed
                                       '-- cannot use Day(Elapsed), it's wrong
            Cells(r, 5) = Hour(Elapsed)
            Cells(r, 6) = Minute(Elapsed)
            Cells(r, 7) = Second(Elapsed)
            Status = Cells(r, 2)
            dStart = Cells(r, 3)
        Else
            Cells(r, 4).Resize(, 4).ClearContents
        End If
        r = r + 1
    Loop
End Sub