VBA check to see if time is between 2 values

[Stephen_IV]

I am having trouble in VBA. First of all I stink with times! I need to check to see if time falls between 2 values, 7:30 AM and 3:30 PM. Can someone please guide me on this one! Thanks in advance!

 

[mole999]

times are always less then 1. If its over one then those are days, in essence 0.31 to 0.65, you can get more detailed than that

 

[CalcSux78]

Working with time, I usually use the MOD function within the worksheet to separate the date from the time. In VBA, you can create a custom function to replicate said function.

Sub TestTime()
Dim dDate As Date
Dim dTime As Date


dDate = Now
dTime = XLMod(dDate, 1)
Select Case dTime
    Case TimeValue("7:30:00") To TimeValue("15:30:00")
        MsgBox True
    Case Else
        MsgBox False
End Select


End Sub


Function XLMod(a, b)
    ' This replicates the Excel MOD function
    XLMod = a - b * Int(a / b)
End Function

 

[Leith Ross]

Hello Stephen,

VBA has several functions for dealing with Dates and Times. As mentioned, the time portion of a date is always a fraction expressed in seconds since midnight.

Here is a sample macro using TimeValue to extract the time portion of a Date or a time string. You can then directly compare the times.

Sub TimeTest()


    Dim D1 As Date
    Dim T1 As Date
    Dim T2 As Date
    Dim x  As Variant
    
        D1 = TimeValue("6/19/2017 6:00AM")
        T1 = TimeValue("7:30 AM")
        T2 = TimeValue("3:30 PM")
        
        x = (D1 >= T1 And D1 <= T2)
         
End Sub

 

[Stephen_IV]

Thanks to all. Sorry it took me a few days to get back but based on your posts I was able to figure it out. Thanks again!