Skip to main content

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.

Employee names and start and end time of their work

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.

Duration between start and end time

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.

DateDiff function returns only whole number part when hour is used as the interval

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.

When start and end times belongs to two adjacent days

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

Comments

Popular posts from this blog

How to Add a Formula to a Cell Using VBA

In this lesson you can learn how to add a formula to a cell using vba. There are several ways to insert formulas to cells automatically. We can use properties like Formula, Value and FormulaR1C1 of the Range object. This post explains five different ways to add formulas to cells. Table of contents How to add formula to cell using VBA Add formula to cell and fill down using VBA Add sum formula to cell using VBA How to add If formula to cell using VBA Add formula to cell with quotes using VBA Add Vlookup formula to cell using VBA We use formulas to calculate various things in Excel. Sometimes you may need to enter the same formula to hundreds or thousands of rows or columns only changing the row numbers or columns. For an example let’s consider this sample Excel sheet. In this Excel sheet I have added a very simple formula to the D2 cell. =B2+C2 So what if we want to add similar formulas for all the rows in column D. So the D3 cell will have the formula

Download a file from url using VBA

Sometimes our Excel VBA applications need to interact with websites. Downloading a file through a URL is a typical example. In this lesson you can learn how to do that using XMLHttpRequest and ADODB.Stream object. XMLHttp is used to request the data from the web server. Once we receive the data from the server, the ADODB.Stream object is used to write that data to a file. You can use this method to download file types such as image files, csv files etc. So let’s start writing our macro to download a file using the Uniform Resource Locator (URL). Let’s name our Sub procedure as “DownloadFileFromURL” Sub DownloadFileFromURL() End Sub First we need to declare a few variables. Dim FileUrl As String Dim objXmlHttpReq As Object Dim objStream As Object Next assign the URL of the file to the FileUrl variables FileUrl = "URL of your file" For an example if your file URL is https://www.example.com/images/chart.jpg then you can assig

Fill a Listbox From an Array

In this lesson you will learn how to fill a Listbox from an array. Listboxes are a very useful control type available in the VBA toolbox. You can use the listboxes for search and view functions of the VBA programs. First let’s fill the listbox using a multidimensional array. After that we can try it with a one dimensional array. This is the sample Excel sheet I’m going to use for this lesson. And here is the listbox we are going to fill with the array. So we have data in three columns and fourteen rows. First, we are going to add this data to an array. After that we can add the array to our listbox in the VBA form. You might wonder why we need to add the data to an array. Because data can be directly added to the listbox from the worksheet using VBA. Yes it is possible. But here our objective is to learn how to add data to a listbox from an array. Because there are situations where we need to add the data which is not available in worksheets to listboxes using VBA. For