{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,020
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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

fadee2

Active Member
Joined
Nov 7, 2020
Messages
367
Office Version
  1. 2019
Platform
  1. Windows
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....
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,020
Office Version
  1. 2016
Platform
  1. Windows
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.
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
367
Office Version
  1. 2019
Platform
  1. Windows
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....
 

kelly mort

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

ADVERTISEMENT

It didn't work either
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
367
Office Version
  1. 2019
Platform
  1. Windows
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?
 

kelly mort

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

ADVERTISEMENT

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
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,238
Office Version
  1. 2010
Platform
  1. Windows
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
 
Solution

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,020
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,062
Messages
5,704,057
Members
421,325
Latest member
tapete86

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
Top