Excel VBA Closest Time of the Job and status in another cell

[bobbyexcel]

Could someone please help me on my below issue..


I want to mention the Final status of each job in Column G based on the below condition of the Job Status..

1. Job should run between 8PM to next Day 3PM.
2. The closest to 3PM would be the Final status of the Job
3. Final Status should apply to all Jobs in that Particular "Start Date".

I've the below code but it checks individual rows but not the the jobs with same name..
Dim lastrow As Integer
Dim timeRng As Range
Set timeRng = sws.Range("L2:L" & lastrow)

lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

TimeSer = TimeSerial(16, 30, 0) ' constant 04:30:00PM later used to create min and max datetime values

For Each Cell In timeRng

If Cell.Value < TimeSer Then
Range("G" & Cell.Row) = Range("B" & Cell.Row)
End If

Next Cell

 

[bobbyexcel]

My bad.. Let me explain this again..

To get the Final Status (Column H) of each Job then the below scenarios should be qualified.. :
Req. 1. All Jobs End time should be StartDay +1 Day before 3PM (if start date is 5th April then end date&Time should be 6thApr 3PM)

Req. 2. IF the Req. 1 qualifies then the Final Status (Column H) of that Job would be Status of that Job i.e., From Column B.

Req. 3.
A. For the Not Qualified cells (or the blank cells mentioned above), it would get the status from Qualified jobs (if the JobName and StartDate matches)
B. If it has 2 or more Qualified Rows, then the Final Status (Column G) would be the Status (column B) from Nearest End Date&Time. (Eg: for rows 6,7,8, we have 2 qualified rows but the nearest end date&time is row 8 then Final Status of Column G Row 6,7,8 would be "Success")

Hope this explanation is clear ?? Please confirm and help me on this.

 

[bobbyexcel]

My bad.. Let me explain this again..

To get the Final Status (Column H) of each Job then the below scenarios should be qualified.. :
Req. 1. All Jobs End time should be StartDay +1 Day before 3PM (if start date is 5th April then end date&Time should be 6thApr 3PM)
View attachment 65903

Req. 2. IF the Req. 1 qualifies then the Final Status (Column H) of that Job would be Status of that Job i.e., From Column B.

View attachment 65906


Req. 3.
A. For the Not Qualified cells (or the blank cells mentioned above), it would get the status from Qualified jobs (if the JobName and StartDate matches)
B. If it has 2 or more Qualified Rows, then the Final Status (Column G) would be the Status (column B) from Nearest End Date&Time. (Eg: for rows 6,7,8, we have 2 qualified rows but the nearest end date&time is row 8 then Final Status of Column G Row 6,7,8 would be "Success")

View attachment 65907

Hope this explanation is clear ?? Please confirm and help me on this.

Please consider this..
3PM would be the End Time for all the Jobs..
Let's say, If the Job Starts at 8PM then it would complete by next day 3PM.. if not then we should not consider that as qualified.
Let's say, If the Job Starts at 1AM then it would complete by the same day 3PM.. if not then we should not consider that as qualified.

 

[bobbyexcel]

I've modified the script now.. So now it will check my Date & Time requirements.. I just added one condition but I still need your help at the final stage.. here is the code..

Sub FinalStatus()

     Dim T_Start, T_Stop, Shift_Start, Shift_Stop, Result    'your 4 timestamps
     Set dict = CreateObject("scripting.dictionary")
     Set lo = Sheets("Jobs").ListObjects("TBL_Jobs")     'table with your data
     arr = lo.DataBodyRange.Value2     'read that table to an array
     ReDim Result(1 To UBound(arr), 1 To 1)

     '1st ROUND : find last status at the end of the shift
    For i = 1 To UBound(arr)     'loop through data
          T_Start = arr(i, 3) + arr(i, 4)     'timestamp end of job
          T_Stop = arr(i, 5) + arr(i, 6)     'timestamp end of job
          mykey = arr(i, 1) & Format(arr(i, 3), "\|dd-mmm-yy")     'job name & start date
       
        If arr(i, 3) = arr(i, 5) Then
       
            If T_Stop <= arr(i, 3) + TimeSerial(15, 0, 0) Then     'job must end before next day 3PM
               If Not dict.exists(mykey) Then
                    dict(mykey) = Array(T_Stop, arr(i, 2))
               Else
                    If dict(mykey)(0) < T_Stop Then dict(mykey) = Array(T_Stop, arr(i, 2))     '---> for that job and that startdate, the last endmoment & status
               End If
            Else
               Result(i, 1) = "not within the shift"
          End If
  
                 
          Else
       
            If T_Stop <= arr(i, 3) + 1 + TimeSerial(15, 0, 0) Then     'job must end before next day 3PM
                 If Not dict.exists(mykey) Then
                      dict(mykey) = Array(T_Stop, arr(i, 2))
                 Else
                      If dict(mykey)(0) < T_Stop Then dict(mykey) = Array(T_Stop, arr(i, 2))     '---> for that job and that startdate, the last endmoment & status
                 End If
            Else
                 Result(i, 1) = "not within the shift"
            End If
  
  
        End If
  
    Next

     '2nd ROUND : add status corresponding with status "end of shift"
     For i = 1 To UBound(arr)     'loop through data
          If Len(Result(i, 1)) = 0 Then     'no blocking conditions
               mykey = arr(i, 1) & Format(arr(i, 3), "\|dd-mmm-yy")    'key within dictionary
               Result(i, 1) = dict(mykey)(1)     'last known status
          End If
     Next

     lo.ListColumns("Final Status").DataBodyRange.Value = Result     'write array to listobject

End Sub

Result:

But your code is not working for 3 rows data (which is Row 6,7,8). Final status would be the nearest Status value to 3 PM (that would be row 6 which is Failed) because we are not checking the date&time nearest to 3PM. Please help me on this.

And then it needs to change the "not within the shift" clause to the Job Status that was qualified already (again nearest to 3PM), the Final status of the same Job within the same Start Date. Like the below screenshot..

 

[bobbyexcel]

Any help on this code ??

 

[bobbyexcel]

Getting this error .. could you please help..