IsDate

[AlexanderBB]

Struck a problem in my macro... I have a string Variable (VarType=8). It's value is, e.g. 04 51 and IsDate tells me True.
It's not a real Date, it's a Time Duration (4 mins 51 secs). Excel may interpret it as a Date, but I don't want it to unless it's 'dd mmm yyyy' Format.

The variable is picked up from a text box, so I assume Vartype will always identify it as a string ?
I tried to have it fail (as a date) with CDate but that didn't work.

How best might I resolve this ? Perhaps just by LEN, but is there's a better way ?
Maybe IsDate can distinguish between a Time and Day-Month-Year somehow?

 

[lrobbo314]

The following UDF will return true if your string is in the 'dd mmm yyyy' format.

[/COLOR]Function xIsDate(s As String) As Boolean
Dim RX As Object: Set RX = CreateObject("VBScript.RegExp")
Dim Pat As String: Pat = "\d{2}\s(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\s\d{4}"


With RX
    .Global = True
    .MultiLine = True
    .ignorecase = True
    .Pattern = Pat
    xIsDate = .test(s)
End With


End Function


[COLOR=#333333]

 

[Rick Rothstein]

The following UDF will return true if your string is in the 'dd mmm yyyy' format.

Function xIsDate(s As String) As Boolean
Dim RX As Object: Set RX = CreateObject("VBScript.RegExp")
Dim Pat As String: Pat = "\d{2}\s(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\s\d{4}"

With RX
    .Global = True
    .MultiLine = True
    .ignorecase = True
    .Pattern = Pat
    xIsDate = .test(s)
End With

End Function

Here is another way to write your function...

[table="width: 500"]
[tr]
	[td]Function xIsDate(S As String) As Boolean
  xIsDate = (S Like "## ??? ####") And (InStr("|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec|", "|" & Mid(S, 4, 3) & "|") > 0)
End Function[/td]
[/tr]
[/table]

 

[AlexanderBB]

Thanks guys. Yes confirming the date is as expected is worth doing. I guess there's no way for Isdate to not interpret minutes and seconds as a date.

 

[Fazza]

how is this?

Function ItsADate(ByVal SomeThing As Variant) As Boolean


     ItsADate = False
     SomeThing = Trim$(SomeThing)
     If Len(SomeThing) > 0 Then
         If IsDate(SomeThing) Then
             ItsADate = CDate(SomeThing) > #1/1/2000# 'to stop times returning as dates??
         End If
     End If


End Function

 

[Fazza]

In the code I posted that trim is in the wrong place. And not needed anyway? Please delete that line entirely. Hence

Function ItsADate(ByVal SomeThing As Variant) As Boolean


     ItsADate = False
     If Len(SomeThing) > 0 Then
         If IsDate(SomeThing) Then
             ItsADate = CDate(SomeThing) > #1/1/2000# 'to stop times returning as dates??
         End If
     End If


End Function

 

[AlexanderBB]

Hi Fazza, thanks - just about to try that out as I struck a snag with xIsDate. The problem was the value
passed might contain a date, not be just a date.
With your function does SomeThing have to be a Variant (it's the contents of a Text box),
and must the Date be > 2000 ? I guess I can try /change that and perhaps use CVar but just
wanted to ask.

 

[AlexanderBB]

I dunno Fazza, as is "10 Jan 1999" returns False, but changing the Year (say to 1936)
makes things like "04 51" return True

 

[Fazza]

I dunno Fazza, as is "10 Jan 1999" returns False, but changing the Year (say to 1936)
makes things like "04 51" return True

It was a guess on my part that dates from year 2000 would be OK for you.

Do you know the earliest dates that could be in the data? Or the range of dates? Or range of times? Or something that distinguishes them.

If you have to work with dates from 19xx (that could overlap time entries), is there some other criteria - like string length - that can be used to distinguish time & date inputs? Then the function code can be changed to suit.

cheers

PS. If the entry might contain data other than a date, can you split the data into two separate entries. (On the user form?). So a date/time entry in one text box & the other data in another text box?

 

[AlexanderBB]

The data comes from Access via an ADODB connection.
I ended up using If IsDate(frmCompare.aData) = True And Len(frmCompare.aData) > 6 Then
on the theory that my times are len(5) and anything more should be (a date).Hopefully it won't fall over under some unforeseen circumstances.
It was worth trying the other suggestions though, so thanks to everyone.