# Excel-VBA Solutions

Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

## Pages

### 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)

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.

Name

Email *

Message *