Skip to main content

Calculate Duration Between Two Times of Two Adjacent Days

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.

Times are on same day or adjacent days

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

Set WS = Worksheets("Data")

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.

For i = 2 To 10

Next i

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

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

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

DurationInHours = DateDiff("n", StartTime, EndTime) / 60

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.

If DurationInHours < 0 Then
     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.

Sub CalculateHoursWorked()

     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.

Duration calculated correctly for same and adjacent days

Comments

Post a Comment

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
Post a Comment
Read more

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
Read more

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
Post a Comment
Read more