Calculate Time Difference Using VBA

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 WS As Worksheet
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.

Set WS = Worksheets("Sheet1")

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

For i = 2 To 10

Next i

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

StartTime = WS.Range("B" & i).Value
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.

DateDiff("n", StartTime, EndTime)

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.

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

Below is the complete macro.

Sub CalculateHoursWorked()

     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.

WS.Range("D" & i).Value = DateDiff("h", StartTime, EndTime)

Instead of

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

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.