Calculate Duration Between Two Times of Two Adjacent Days

In the last post we learnt how to calculate the duration between two times of the same day. Today let’s look at how to calculate duration between two times of two adjacent days. Let’s consider this sample data. This is a start and end time of work for some employees.

If you look at the start and end times of the work, you will notice that some employees have ended the work on the same day as started and others have ended the work on the next day. So if we use the code of our previous post it will only give correct results for cases where employees started and ended the work on the same day. Now let’s look at how to develop a solution which suits both of these types.

As usual, first we need to declare a few variables.

Dim WS As Worksheet
Dim StartTime As Date
Dim EndTime As Date
Dim i As Integer
Dim DurationInHours As Double

Let’s assume we have the data in the sheet called “Data”. Then we can assign that sheet to the WS variable as follows.

Set WS = Worksheets("Data")

Next we need a For Next statement to iterate through each row. As we have employee names from 2nd row to 10th row we can write the For Next statement like this.

For i = 2 To 10

Next i

Now when the program loops through each row we can assign values for StartTime and EndTime variables.

StartTime = WS.Range("B" & i).Value
EndTime = WS.Range("C" & i).Value

Then duration can be calculated using the DateDiff function as follows.

DurationInHours = DateDiff("n", StartTime, EndTime) / 60

But now the problem is that the DateDiff function calculates the time difference assuming both times belong to the same day. If the end time is on the next day, then the program returns a negative value. Therefore we can use the sign of the returned value to identify whether end time is on the same day or not. And once we detect an end time which is on the next day, we can correct the returned value easily by adding 24 to it. Here is the reason for adding exactly 24. Even when the end time is on the next day, DateDiff calculates the value assuming the end time is on the morning of the same day. So we can easily fix it by finding the duration in the opposite direction by adding 24 to the returned result.

Also if ending time is on the same day, then we can use the value returned from the DateDiff function as it is.

If DurationInHours < 0 Then
     DurationInHours = DurationInHours + 24
End If
WS.Range("D" & i).Value = DurationInHours

You can see that we have altered the DurationInHours variable only if it is negative. So here is the full code.

Sub CalculateHoursWorked()

     Dim WS As Worksheet
     Dim StartTime As Date
     Dim EndTime As Date
     Dim i As Integer
     Dim DurationInHours As Double
     Set WS = Worksheets("Data")
     For i = 2 To 10
          StartTime = WS.Range("B" & i).Value
          EndTime = WS.Range("C" & i).Value
          DurationInHours = DateDiff("n", StartTime, EndTime) / 60
          If DurationInHours < 0 Then
               DurationInHours = DurationInHours + 24
          End If
          WS.Range("D" & i).Value = DurationInHours
     Next i

End Sub

Now the program calculates the duration correctly for both types.