none
VBA code to cross check the current time with given two time(AM/PM)-Start time and End time. Basically need to find the agent's names who comes in shift based on given shift time in the list RRS feed

  • Question

  • Public Function InRange(startTime As Date, endTime As Date) As Boolean
    
        Dim dtNow As Date
    
        dtNow = Now
    
        Select Case TimeValue(dtNow)
            Case Is > TimeValue(startTime)
                If TimeValue(endTime) > TimeValue(dtNow) Then
                    InRange = True
                End If
            Case Is > TimeValue(startTime)
                If TimeValue(endTime) > TimeValue(dtNow) Then
                    InRange = True
                Else
                    InRange = False
                End If
            Case Is < TimeValue(startTime) And (TimeValue(endTime) > TimeValue(dtNow))
                    InRange = True
            Case Else
                InRange = False
        End Select
    
    End Function

    start = Format(cll.Value, "h:mm")
     end = Format(cll.Offset(0, 1).Value, "h:mm")

     Bool_result = InRange(start, end)

    cll is refer to the excel cell value. the excel value are pick based on each agent. The above code however works partially. could you please help to get the correct vba to get the agents name based on current time which come between start time and end time.

         
    Name Start End
    Agent1 7:00 AM 4:00 PM
    Agent2 WO WO
    Agent3 WO WO
    Agent4 3:00 AM 12:00 PM
    Agent5 8:00 AM 5:00 PM
    Agent6 12:00 AM 9:00 AM
    Agent7 8:00 AM 5:00 PM
    Agent8 5:00 AM 2:00 PM
    Agent9 8:00 AM 5:00 PM
    Agent10 10:00 PM 7:00 AM
    Agent11 5:00 PM 2:00 AM
    Agent12 3:00 PM 12:00 AM
    Agent13 10:00 AM 7:00 PM
    Agent14 6:00 PM 3:00 AM
    Agent15 WO WO
    Agent16 4:00 AM 1:00 PM
    Agent17 WO WO
    Agent18 12:00 PM 9:00 PM
    Agent19 9:00 AM 6:00 PM
    Agent20 2:00 PM 11:00 PM
    Agent21 6:00 AM 3:00 PM
    Agent22 WO WO
    Agent23 3:00 AM 12:00 PM
                  
    Tuesday, September 10, 2019 2:45 PM

Answers

  • Check if the next function works in various cases:

    Function InRange(ByVal startTime As Date, ByVal endTime As Date) As Boolean
     
        Dim n As Date: n = TimeValue(Now)
        Dim s As Date: s = TimeValue(startTime)
        Dim e As Date: e = TimeValue(endTime)
        
        If s < e Then
            InRange = n > s And n < e
        Else
            InRange = n > s Or n < e
        End If
     
    End Function


    • Edited by Viorel_MVP Wednesday, September 11, 2019 8:15 AM
    • Marked as answer by usapnew Monday, September 30, 2019 6:47 AM
    Wednesday, September 11, 2019 8:14 AM

All replies

  • could someone please help to resolve this.The above code is not working properly at evening time as it is considering the AM/PM time less than the current time.
    Wednesday, September 11, 2019 3:33 AM
  • Try this:

    startDate = CDate(cll.Value)

    endDate = CDate(cll.Offset(0, 1).Value)

    Bool_result = InRange(startDate, endDate)

    . . .

    Function InRange(ByVal startTime As Date, ByVal endTime As Date) As Boolean

        Dim dtNow As Date

        dtNow = Now

        InRange = TimeValue(dtNow) > TimeValue(startTime) And TimeValue(dtNow) < TimeValue(endTime)

    End Function

     

    In order to skip bad data (“wo”), you can use something like this:

    On Error GoTo Skip

    startDate = CDate(cll.Value)

    . . .

    Skip:

    . . .

    where Skip is a label.

    Wednesday, September 11, 2019 6:15 AM
  • The code above running partially correct. I am running the code at my local(system) time at 3.30PM.

    for Starttime(3:00 PM) to endTime(12:00 AM) it is showing False where it has to be True.

    and for Starttime(6:00 PM) to endTime(3:00 AM) it is showing True where it has to be False.

    do the logic need to be changed?


    • Edited by usapnew Wednesday, September 11, 2019 7:59 AM
    Wednesday, September 11, 2019 7:51 AM
  • Check if the next function works in various cases:

    Function InRange(ByVal startTime As Date, ByVal endTime As Date) As Boolean
     
        Dim n As Date: n = TimeValue(Now)
        Dim s As Date: s = TimeValue(startTime)
        Dim e As Date: e = TimeValue(endTime)
        
        If s < e Then
            InRange = n > s And n < e
        Else
            InRange = n > s Or n < e
        End If
     
    End Function


    • Edited by Viorel_MVP Wednesday, September 11, 2019 8:15 AM
    • Marked as answer by usapnew Monday, September 30, 2019 6:47 AM
    Wednesday, September 11, 2019 8:14 AM
  • Hi Viorel,

    Thank you so much.

    Monday, September 30, 2019 6:49 AM