Sometimes we need to calculate time differences in VBA applications. It is often required when we develop solutions related to employment. Here is an example.

This sheet contains names of employees and the start and end time of their work. Then we need to calculate the hours worked for each employee. So in this lesson you can learn how to calculate the duration of two times using a macro.

First we need to declare a few variables.

Dim StartTime As Date

Dim EndTime As Date

Dim i As Integer

If the name of the sheet is Sheet1, we can assign that sheet to the WS variable like this.

In this example we have only nine names. We can use a For Next loop to loop through each row.

Next i

Now in each iteration assign the start and end time to variables StartTime and EndTime respectively.

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

Once the start and end times are assigned, use the VBA function called “DateDiff” to calculate the time difference.

This function has five arguments. But the last two are optional and not needed for this type of calculation. First argument is the interval. We can give the interval as year, month, day, hour, minute etc. In here we used “n” and it refers to the minutes. So we will get the duration in minutes. Then we can divide the result by 60 to get the values in hours. So the final code should look like this.

Below is the complete macro.

Dim WS As Worksheet

Dim StartTime As Date

Dim EndTime As Date

Dim i As Integer

Set WS = Worksheets("Sheet1")

For i = 2 To 10

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

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

WS.Range("D" & i).Value = DateDiff("n", StartTime, EndTime) / 60

Next i

End Sub

This is the result of the above code.

You may think why I didn’t use the hour as interval to directly calculate the number of hours like below.

Instead of

But then the problem is DateDiff function returns only the whole number part disregarding the decimal part. So if we use “h” as the period then we will get below result which is incorrect.

And here is the next challenge. What if some employees start work in the evening or night and end it in the next day. Then our program generates incorrect results like this.

So in my next post I will show you how to calculate the time difference of two adjacent days like that.