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 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.

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.

Next i

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

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

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

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.

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.

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.