How to calculate working hours in Excel - Part 3

The first step is to define the variables in which we will be importing the rows with data from the "Calendar" and "Data" sheets

Sub workhours()

Dim Items() As Variant

Dim Calendar() As Variant

Sheets("Data").Select

lstRowI = Cells(Rows.Count, "A").End(xlUp).Row

Items = Range(Cells(1, 1), Cells(lstRowI, 3))

Sheets("Calendar").Select

lstRowC = Cells(Rows.Count, "A").End(xlUp).Row

Calendar = Range(Cells(1, 1), Cells(lstRowC, 8))

Next steps is to identify the date and time in which the code is run. This is achieved by using the Now() function

The Now() function is allocated to the variable called RunR. The date, hour and minutes contained in the RunR variable are allocated to 3 distinct variables 

RunRM = Minute when the code is run

RunRH = Hour when the code is run 

RunRD = Date when the code is run 

Sheets("Data").Select

RunR = Now()

RunRM = Minute(RunR)

RunRH = Hour(RunR)

RunRD = Date

The second step is to run a loop through the 'Calendar' variable with the objective to match the date and time of when the code is run with the same information in the 'Calendar' variable 

Once the date and time have been located 3 variables will be created:

DateRun = containing the matching date in the 'Calendar' variable when code is run 

HourRun = containing matching hour in the 'Calendar' variable when code is run 

MinuteRun = Minutes in which the macro is run. The value is taken from the RunRM variable 

RefRun = is the REFERENCE number corresponding to the date and time in the 'Calendar' variable 

The logic followed by the loop iis the following

The code for the first loop is outlined below

For k = 1 To lstRowC

    If RunRD = Calendar(k, 2) And _

        RunRH = Hour(Calendar(k, 3)) And Calendar(k, 5) = 0 Then

            DateRun = Calendar(k, 2)

            HourRun = Hour(Calendar(k, 3))

            MinuteRun = RunRM

            RefRun = Calendar(k, 4)

            Exit For

    ElseIf Calendar(k, 2) = RunRD And _

    RunRH = Hour(Calendar(k, 3)) And Calendar(k, 5) >= 1 Then

            DateRun = Calendar(k, 2)

            HourRun = Hour(Calendar(k, 3))

            MinuteRun = 0

            RefRun = Calendar(k, 4)

            Exit For

    Else: End If        

Next k

The third step is to perform a nested loop that will be running through the 2 variables 'Data' and 'Calendar' The purpose is to identify for the date and time of each customer request, contained in the 'Data' variable, the corresponding date, time and REFERENCE  number in the 'Calendar' variable

The below flow chart describes the logic of the nested loop

When the date and time in the 'Data' variable are equal to the ones in the 'Calendar' variable the conditions are met 

Once they are met the relevant variables are populated. 

The variables are the following:

DateRequest = Date reported in the 'Calendar' variable

HourRequest = Hour reported in the 'Calendar' variable

RefRequest = REFERENCE number reported in the 'Calendar' variable 

The below picture provides the details of the box circled in violet in the above flowchart

Once the conditions are met and the variables populated, we have all the information needed to calculate the working days and time elapsed between the single customer request in the 'Data' variable and the time in which the macro has been launched.

The difference in hours is reported in the variable HourGap

The difference in minutes is reported in the variable MinuteGap

Both the variables are reported in the sheet 'Data', columns D and E, for each customer request

The code for the nested loop is the following 

For i = 2 To lstRowI

    For k = 1 To lstRowC

        If Items(i, 2) = Calendar(k, 2) And _

        Hour(Items(i, 3)) = Hour(Calendar(k, 3)) And Calendar(k, 5) = 0 Then

            DateRequest = Calendar(k, 2)

            HourRequest = Hour(Calendar(k, 3))

            MinuteRequest = Minute(Items(i, 3))

            RefRequest = Calendar(k, 4)

            Exit For

        ElseIf Calendar(k, 2) = Items(i, 2) And _

        Hour(Items(i, 3)) = Hour(Calendar(k, 3)) And Calendar(k, 5) >= 1 Then

             DateRequest = Calendar(k, 2)

            HourRequest = Hour(Calendar(k, 3))

            MinuteRequest = 0

            RefRequest = Calendar(k, 4)

            Exit For

        Else: End If        

    Next k

    HourGap = ((RefRun - RefRequest))

    Sheets("Data").Cells(i, 4).Value = HourGap

    MinuteGap = ((MinuteRun)) + ((MinuteRequest) * -1)

    Sheets("Data").Cells(i, 5).Value = MinuteGap

Next i

End Sub

After the running the code the sheet 'Data' will feature in columns A to C the original data; column D will contain the difference in working hours ( represented by the difference between the REFERENCE number related to the two dates and hours) and column E will contain the number of minutes to be added to the hours in order to obtain the full elapsed time in working hours and minutes. 

The value in column E can be negative in case the number of minutes at the time when the customer request was received are greater than the ones when running the report 

Refer to the previous post to review the calculation method

Columns F and G contain the formulas to determine the final hours and minutes elapsed

These formulas need to be added manually and copied down the full range of populated rows. 

They can be added to the code