+ Reply to Thread
Results 1 to 3 of 3

Using Variables to Check if a Date is Between 2 Dates Not Working

Hybrid View

  1. #1
    Registered User
    Join Date
    07-31-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    30

    Using Variables to Check if a Date is Between 2 Dates Not Working

    Hi,
    Trying to check if a date is between 2 dates. The code below shows the various attempts to make this work. I can't get it to work when I use variables for the dates. Worked hours in trying to figure this out.
    Karen

    Sub testDateCheck()
    
    Dim dateOpen As Date
    Dim dateClose As Date
    Dim tDate As Date
    
    dateOpen = #7/1/2021#
    dateClose = #9/30/2021#
    tDate = #8/22/2021#
    
    MsgBox "dateOpen is " & dateOpen
    MsgBox "dateCLose is " & dateClose
    MsgBox "tDate is " & tDate
        
    'This works - results in open
                If #8/22/2021# >= #7/1/2021# And #8/22/2021# <= #9/30/2021# Then
                    MsgBox "Open"
                Else
                    MsgBox "Close"
                End If
        
    'This doesn't work - results in close
                If " & tDate & " >= " & openDate & " And " & tDate & " <= " & closeDate & " Then
                    MsgBox "Budget Open variable"
                Else
                    MsgBox "budget closed variable - try 1"
                End If
    
    'This doesn't work - results in close
                If tDate >= openDate And tDate <= closeDate Then
                    MsgBox "Budget Open variable"
                Else
                    MsgBox "budget closed variable - try 2"
                End If
                
    'This doesn't work - results in close
                If "#" & tDate & "#" >= "#" & openDate & "#" And "#" & tDate & "#" <= "#" & closeDate & "#" Then
                    MsgBox "Budget Open variable"
                Else
                    MsgBox "budget closed variable - try 3"
                End If
                
    End Sub

  2. #2
    Valued Forum Contributor
    Join Date
    07-13-2021
    Location
    California, USA
    MS-Off Ver
    2010
    Posts
    513

    Re: Using Variables to Check if a Date is Between 2 Dates Not Working

    Enter the statement Option Explicit at the top of your macro. Then VBA will show your "misstake".

    The only variable comparison that we might expect to work is:
                If tDate >= openDate And tDate <= closeDate Then
                    MsgBox "Budget Open variable"
                Else
                    MsgBox "budget closed variable - try 2"
                End If
    It fails because openDate and closeDate are empty -- because they are misspelled.

    The correct variable names are dateOpen and dateClose

    -----

    FYI, the other "variable" comparisons fail because expressions of the form " & tDate & " are strings.

    Not only are the "variables" tDate etc __not__ substituted in the strings, but also "&" and "#" are simply characters, not the "operators" that you intended.

    PS.... After correcting the variable spellings, "try 3" __seems__ to work. But it is doing a __string__ comparison, not a date comparison. It will not work as intended with certain well-chosen dates.
    Last edited by curiouscat408; 08-22-2021 at 10:28 AM.

  3. #3
    Registered User
    Join Date
    07-31-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Using Variables to Check if a Date is Between 2 Dates Not Working

    OMG! I'm embarrassed that I missed the spelling issue. Looking at code way too long and way too many days in a row. Fixed the spelling and it works great! Thanks for the additional information.
    Karen

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Working out dates between invoice date and payment date
    By NLidddell in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-10-2021, 12:28 AM
  2. Replies: 2
    Last Post: 07-19-2018, 11:58 AM
  3. VBA Loop check if date is between two dates
    By cvelle89 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-10-2015, 06:34 AM
  4. Check col of dates.....each date is the first of the month
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-25-2011, 10:06 AM
  5. VBA Loop to check if a date is between two dates
    By jmb77 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2010, 11:47 AM
  6. Check two dates against todays date?
    By oleisbest in forum Excel General
    Replies: 12
    Last Post: 04-22-2010, 07:46 AM
  7. Check if date is between 2 dates
    By Shane.H in forum Excel General
    Replies: 5
    Last Post: 04-07-2009, 12:14 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1