Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: VBA check to see if time is between 2 values

  1. #1
    Board Regular
    Join Date
    Mar 2003
    Posts
    840
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    8,128
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    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
    969
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,466
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    840
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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!

User Tag List

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
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com