Calculate time difference in hours between two dates and times

Question

A macro I made called "Response Time" adds a new column. I want to determine how much time separates the data in two columns.

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

The macro output is accurate when the date is the same, that is, when it is within 24 hours.

However, I want the macro to determine the date difference and determine how many hours are total in the newly added "Response Time" column.

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

The output in column F is inaccurate since columns C and D in the highlighted area have two separate dates with timespans longer than 24 hours.

I need a coding formula that compares the date and time in these two columns and outputs the total number of hours in HH:MM format. 

In the above-highlighted area, the total hrs should show 162 hrs approximately.

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

Answer 1

Simply subtract the two dates to get the total number of hours, then use the right format:

Sub INeedADate()
    [c1] = [b1] - [a1]
    Range("C1").NumberFormat = "[hh]:mm"
End Sub

I hope this helps you.