Select Case using date ranges

[wolf.stalker]

hi all. i am stuck.

i am reading in a value from sheet1.range("A1") and that value is "12/22/2008" (text...not number formatted).

my case statement is similar to this.

select case sheet1.range("A1")

case "12/01/2008" to "1/1/2009"
' do stuff

end select

when this runs, it will always skip my ranged date even though it falls in it. is this because we are comparing the input string to the range'd string?

i am just trying to understand why i am not able to use my case statement with a date range. any advice would be helpful. my next approach is going to be to maybe try converting to julian calander?

anyone have a good way of doing this?

[Bob Phillips]

Because it isn't dates as you said. If it isn't numbers, it won't do a number comparison.

[Paul_Hossler]

I put ="12/25/2008" in A1

and

[vba]
Sub test()
Select Case Range("A1").Value

Case #1/1/2007# To #12/31/2007#
MsgBox 2007
Case #1/1/2008# To #12/31/2008#
MsgBox 2008
Case #1/1/2009# To #12/31/2009#
MsgBox 2009

End Select

End Sub

[/vba]

Seems to work as expected (the # ...# defines them as dates)

Paul

[MaximS]

you my try this:

[VBA]
Sub date_comparsion()
Dim val, val1 As Date
val = DateSerial(Right(Range("A1"), 4), Mid(Range("A1"), 4, 2), Left(Range("A1"), 2))
val1 = DateSerial(Right(Range("A2"), 4), Mid(Range("A2"), 4, 2), Left(Range("A2"), 2))
If val > val1 Then
Range("B1").Value = "It's older"
Else
Range("B1").Value = "It's not older"
End If
End Sub
[/VBA]

[Cyberdude]

I'm not sure if this would help, but it struck me that oft times with text items I have to add a leading blank to the values, i.e., change #1/1/2007# to #b1/b1/2007# where the red "b" is my way of showing where a blank should go.
Just a thought.

[wolf.stalker]

thanks all for your help....