Results 1 to 5 of 5

VBA check to see if time is between 2 values

This is a discussion on VBA check to see if time is between 2 values within the Excel Questions forums, part of the Question Forums category; I am having trouble in VBA . First of all I stink with times! I need to check to see ...

  1. #1
    Board Regular
    Join Date
    Mar 2003
    Posts
    834

    Default VBA check to see if time is between 2 values

    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!

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    7,956

    Default Re: VBA check to see if time is between 2 values

    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
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    Board Regular CalcSux78's Avatar
    Join Date
    Oct 2013
    Location
    STL
    Posts
    917

    Default Re: VBA check to see if time is between 2 values

    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.

    Code:
    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
    Last edited by CalcSux78; Jun 19th, 2017 at 01:21 PM. Reason: formatting
    Need a more specific answer? Use MrExcel HTML Maker to post an example.

    "The quality of life depends on the clarity of the message." - Unknown

    Cunningham's Law: "The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."

  4. #4
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,450

    Default Re: VBA check to see if time is between 2 values

    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.

    Code:
    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
    Sincerely,
    Leith Ross

  5. #5
    Board Regular
    Join Date
    Mar 2003
    Posts
    834

    Default Re: VBA check to see if time is between 2 values

    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!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com