{VBA} How to locate a date between two dates in the form "dd1-mm1" to "dd2-mm2"

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
Hello world,

I am here again with yet another challenge (from my end) and I need someone to help me out with the best way to get it resolved.

I have a textbox which contains date in the format "dd-mm-yy".

Now I want to set date ranges say "11-06" to "28-06" ( 11th June to 28th June) for example so that if I enter say "20-06-21" (20th June 2021) into my textbox, it should display matched on my MsgBox alert.

I will be using multiple date ranges (maybe 5 or more) and I want to check all those ranges for the match.

If it matches in the first range, display "match 1"
In the second, "match 2" and so on.


I don't know how to do it. I will be glad if someone can pull it up for me.

Thanks in advance.
Kelly
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
try following code....
VBA Code:
Select Case CDate(UserForm1.TextBox1.Text)
    Case "11-06-2021" To "28-06-2021"
        MsgBox "Macth 1"
    Case "30-06-2021" To "10-07- 2021"
        MsgBox "Match 2"
    Case Else
        MsgBox "No Matches Found"
End Select

hth....
 
Upvote 0
try following code....
VBA Code:
Select Case CDate(UserForm1.TextBox1.Text)
    Case "11-06-2021" To "28-06-2021"
        MsgBox "Macth 1"
    Case "30-06-2021" To "10-07- 2021"
        MsgBox "Match 2"
    Case Else
        MsgBox "No Matches Found"
End Select

hth....
I want to match only the day and month.

For example with the range

"11-06-2021" To "28-06-2021",

If textbox1 contains "18-06-02" which is 18th June 2002, I should still get a match.
 
Upvote 0
try the following

Select Case Left(UserForm1.TextBox1.Text, 4)
Case "11-06" To "28-06"
MsgBox "Macth 1"
Case "30-06" To "10-07"
MsgBox "Match 2"
Case Else
MsgBox "No Matches Found"
End Select

hth....
 
Upvote 0
It should.... because the code only takes first four characters of your textbox value and matches that with each case....
1624875406520.png


can you post part of code in question??
Is there any error?
 
Upvote 0
Code:
Sub DateTest()
  Dim UserDate As String 

   UserDate = "26-08-02"

  Select Case Left (UserDate, 4)
       Case "11-06" To "19-07"
               MsgBox "Match 1"
        Case "24-07" To "30-07"
               MsgBox "Match 2"
        Case "18-08" To "21-09"
               MsgBox "Match 3"
        Case Else 
              MsgBox "No Match Found"
  End Select 
End Sub

I think I was using other dates which were not working.

When I tested with your actual setup, it worked.

But some of my tests are not giving me the right results.

This date above for example is showing match 2 instead of match 3
 
Upvote 0
Hi,​
obviously any DD-MM is a lengh of five characters rather than four ‼ :ROFLMAO:
But as comparing text so respecting the Logic the month must be before the day in the Select Case block like MMDD :​
VBA Code:
Sub DateTest()
  Dim UserDate$
      UserDate = "26-08-02"
  Select Case Mid(UserDate, 4, 2) & Left(UserDate, 2)
         Case "0611" To "0719": MsgBox "Match 1"
         Case "0724" To "0730": MsgBox "Match 2"
         Case "0818" To "0921": MsgBox "Match 3"
         Case Else:             MsgBox "No Match Found"
  End Select
End Sub
 
Upvote 0
Solution
Great job @Marc L

It has worked.

My network went down that's why I couldn't reply earlier. Thanks again and have a great time.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top