Calculate time difference in hours between two dates and times

Question

I created a macro that adds new column Named "Response Time". I want to calculate the time difference between values in two columns.

I gave a simple formula,
D2(column name) - C2(column name) = Total time in HH:MM format

When the date is the same i.e. timeframe within 24 hrs the macro output is correct.

However, I want the macro to find the date difference and calculate the total hours in newly added column "Response Time".

For example
Column C (MM/DD/YYYY hh:mm) 09/02/2020 23:00
Column D (MM/DD/YYYY hh:mm) 09/03/2020 22:00
Output in a newly added column named (Response Time) should be 25:00 hrs as the difference is 1 day 1hrs so total 25 hrs.

Excel Screen wrong output

In the highlighted area, column C and D have two different dates where the timespan is more than 24 hrs and output in column F is incorrect.

I want a formula in coding that compares these two columns' date time and gives the total hrs in HH:MM format.
In the above highlighted area total hrs should show 162 hrs approxmately.

Sub response6()

    'Find and Substract (_recvd - _actual)
    
    'Full In Gate at Ocean Terminal (CY or Port)_actual
    'Full In Gate at Ocean Terminal (CY or Port)_recvd
    
    Dim lastR As Long, cl As Range, col1 As Long
    
    With ActiveWorkbook.Worksheets("Main")
        For Each cl In .Range("1:1")
            If cl.Value = "Full In Gate at Ocean Terminal (CY or Port)_recvd" Then
                cl.Offset(0, 1).EntireColumn.Insert shift:=xlRight
                cl.Offset(0, 1) = "Response Time"
                cl.Copy
                cl.Offset(0, 1).PasteSpecial Paste:=xlPasteFormats
                Application.CutCopyMode = False
                Exit For ' exit the loop after finding the column
            End If
        Next cl
                 
        With ActiveWorkbook.Worksheets("Main")
        col1 = .Cells.Find(What:="Full In Gate at Ocean Terminal (CY or Port)_actual", _
          After:=.Range("A1"), LookIn:=xlValues, _
          lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, _
          MatchCase:=False, SearchFormat:=False).Column
                            
        If col1 = 0 Then MsgBox "The column header could not be found...": Exit Sub
    
            lastR = .Cells(Rows.Count, cl.Column).End(xlUp).Row 'last row
            'put formula (at once):
            .Range(cl.Offset(1, 1), .Cells(lastR, cl.Offset(1, 1).Column)).Formula = _
              "=" & cl.Offset(1, 0).Address(0, 0) & .Cells(2, col1).Address(0, 0) & "/" & "60"
               
            Dim d1 As DateTime = "2/13/2018 1:50:00 PM"
            Dim d2 As DateTime = "2/20/2018 1:50:00 PM"
               
            cl.Offset(, 1).EntireColumn.NumberFormat = "hh:mm"
          
        End With
             
    End With
End Sub

Pᴇʜ Pᴇʜ · Accepted Answer · 2020-12-17T15:15:22

2

votes

Formula in column D: =C:C-B:B
Number format in column D: [h]:mm

That's all you need.

Calculate time difference in hours between two dates and times

3 Answers