Calculate time difference in hours between two dates and times

0 votes

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
Wrong Output in column named Response Time

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
Nov 3 in Others by Kithuzzz
• 20,660 points
111 views

1 answer to this question.

0 votes

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

enter image description here

I hope this helps you. 

answered Nov 4 by narikkadan
• 37,660 points

Related Questions In Others

+1 vote
1 answer

what is the difference between error and stderr in Node.js?

Error is an object created by Node.js to handle ...READ MORE

answered Jul 4, 2019 in Others by sunshine
• 1,280 points
1,310 views
+1 vote
1 answer
0 votes
1 answer

Difference between "main()" and "runApp()" functions in Flutter.

Hi@akhtar, The main() function came from Java-like languages so ...READ MORE

answered Jul 20, 2020 in Others by MD
• 95,380 points
5,748 views
0 votes
1 answer

What's the difference between struct and class in .NET?

In .NET, there are two categories of ...READ MORE

answered Feb 8 in Others by Soham
• 9,670 points
56 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
454 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,228 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
71 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
210 views
0 votes
1 answer

Determine if calculation between 2 date time values is < 72 Hours in excel

Actually, Excel is quite accommodating in this ...READ MORE

answered Nov 12 in Others by narikkadan
• 37,660 points
44 views
0 votes
1 answer

Differentiate between Tokyo and Seoul Time zone

if DST and Offset are the only ...READ MORE

answered Feb 11 in Others by narikkadan
• 37,660 points
99 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP