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, 2022 in Others by Kithuzzz
• 38,000 points
1,512 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, 2022 by narikkadan
• 63,640 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,300 points
2,369 views
+1 vote
1 answer

Between cyber security and CCNA profession which one is best in terms of time to become an expert and salary payment

CCNA professional is more inclined towards the ...READ MORE

answered Dec 18, 2019 in Others by Pri
2,032 views
0 votes
2 answers

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

main is entry point and,   runApp() is ...READ MORE

answered Apr 26, 2022 in Others by anonymous
9,373 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, 2022 in Others by Soham
• 9,710 points
423 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,740 points
1,178 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
3,633 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, 2022 in Others by gaurav
• 23,260 points
879 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, 2022 in Others by Edureka
• 13,690 points
1,033 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, 2022 in Others by narikkadan
• 63,640 points
861 views
0 votes
1 answer

Differentiate between Tokyo and Seoul Time zone

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

answered Feb 11, 2022 in Others by narikkadan
• 63,640 points
544 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