VBA Excel Coding

[Ali Abbas]

Firs I will be grateful if you can help me. I need your help in a VBA code. I need to find differences in dates and times. I want to find the date and time differences of columns C and D and the answer to be in column E. I want the rows fixable in a loop. The code does the calculations to find the differences in all rows. And I am attaching an example in an excel file to make it clear. Thanks

 

[SkipVought]

Hi,

Why not just use a simple spreadsheet formula? No VBA required!

[tt]E2: =ABS(C2-D2)*24[/tt]

If your table is a Structured Table, your formula will automatically propagate to each new row

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

I'm having trouble being completely and utterly retired. But this is a softball.

Actually, after downloading your workbook, it seems that there are several problems with your table:

1) You have a COMMA following MOST of the Dates values in Date Time.
2) You mix your Date Formats between DMY and MDY.
3) You seem to want Hours:Minutes rather than Hours.


If you want Hours:Minutes ,as 0, 2.45 implies since the difference in Hours is 2.75 hours that the time difference would be 2:45 (ie 2 hours 45 minutes).
So your formula should be...
[tt]
E2: = Abs(C2 - D2)
[/tt]
...and column E should have a Number Format of [h]:mm.

If you insist on a VBA solution, I would proceed with the following assumptions:

1) Your table starts in A1.
2) Your table is the only data on Sheet1. There is no data to the right of or below your table.

This code, when run, calculates hours:minutes duration for each row. I would rename E1 Difference [h]:mm or Duration [h]:mm since concatenating " Hours" to each numeric result, a) needlessly bloats your sheet with redundant data, b) renders the hours:minutes duration unusable to direct calculation and c) is not representative of the actual data.

Sub CalculateHoursDuration()
   Dim r As Range, lLastRow As Long

   With Sheet1
      lLastRow = .UsedRange.Rows.Count
      For Each r In .Range(.Cells(2, 5), .Cells(lLastRow, 5))
          r.NumberFormat = "[h]:mm"
          r.Value = Abs(.Cells(r.Row, 3).Value - .Cells(r.Row, 4).Value)
      Next
   End With
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein