String fails IsDate() but converts to date anyway

[toliphint]

The string "aug11" is not a valid Excel date format according to IsDate() because the function returns "False". However, when the test code segment below is executed, Excel thinks it is a date despite returning False, because "aug11" is being converted to a date of "11-Aug" represented by "8/11/2021" (see value in Cell A1). Cell A1 should contain the string "aug11".

How can this be avoided? I need to test a variety of date formats to properly use them or not in other code. According to IsDate(), "aug11" should be a string but Excel does not treat it that way.

Help please.

Sub DateTest()
Dim sDate As String, bTest As Boolean

Range("A1").Value = vbNullString
sDate = "aug11"
bTest = IsDate(sDate)
MsgBox "String 'aug11' is a valid date format:    " & bTest
Range("A1").Value = sDate

End Sub

 

[JoeMo]

I can reproduce your observations if A1 is formatted as General prior to making any entries in it. Alternatively, try formatting A1 as Text prior to making any entries. When I do that, I observe that aug11 is not converted to a date by your code.

 

[Marc L]

How can this be avoided?

Sub Demo1()
    Dim S$
        S$ = "aug11"
        [A1].Value2 = IIf(IsDate(S), "", "'") & S
End Sub

 

[Marc L]

Or even maybe :​

Sub Demo1r()
    Dim S$
        S = "aug11"
        [A1].Value2 = S
End Sub

 

[toliphint]

JoeMo and Mark L, thanks for your responses.
JoeMo: Yes, your solution works, but what I notice is that a new worksheet defaults with all cells of format "General". Then when the DateTest() sub is executed, Excel takes actions that are inconsistent:
1. First, IsDate() says "aug11" is NOT a recognized Excel date format which appears correct, but
2. It then proceeds to format the Cell into which "aug11" is placed, from "General" to "Custom d-mmm", indicating it's a date, when it just said it was NOT.
This looks like a significant bug to me, and I don't want to be going behind Excel and reformatting. Which of the above Excel actions do I believe and take action upon? I opt for neither.

Mark L: Last post is perfect solution. Being a VBA newbie (formerly Assembly and C), did not know of IIf(). So learned a lot.

Thanks to both.

 

[JoeMo]

JoeMo and Mark L, thanks for your responses.
JoeMo: Yes, your solution works, but what I notice is that a new worksheet defaults with all cells of format "General". Then when the DateTest() sub is executed, Excel takes actions that are inconsistent:
1. First, IsDate() says "aug11" is NOT a recognized Excel date format which appears correct, but
2. It then proceeds to format the Cell into which "aug11" is placed, from "General" to "Custom d-mmm", indicating it's a date, when it just said it was NOT.
This looks like a significant bug to me, and I don't want to be going behind Excel and reformatting. Which of the above Excel actions do I believe and take action upon? I opt for neither.

Mark L: Last post is perfect solution. Being a VBA newbie (formerly Assembly and C), did not know of IIf(). So learned a lot.

Thanks to both.

Glad you got a solution. I don't agree that your code reveals a significant bug. IsDate is a VBA function for which there is no Excel worksheet function equivalent. So, my suggestion was to format the cells you want to use for your "dates" as text so the last line of your code: Range("A1").Value = sDate doesn't show the text as a date. VBA doesn't see the entry as a date but Excel does and converts it.

 

[toliphint]

Wellllll JoeMo after much testing, you sir are correct and I owe you an apology! Yours is the solution that truly works in all circumstances. Regardless of the machinations one undergoes after prepending a " ' ", once Excel determines "aug11" or something similar is a date despite what IsDate() says, the value placed in a cell is forever a date or a number depending upon formatting other than Text.

I have changed the marked solution to yours.

 

[JoeMo]

Wellllll JoeMo after much testing, you sir are correct and I owe you an apology! Yours is the solution that truly works in all circumstances. Regardless of the machinations one undergoes after prepending a " ' ", once Excel determines "aug11" or something similar is a date despite what IsDate() says, the value placed in a cell is forever a date or a number depending upon formatting other than Text.

I have changed the marked solution to yours.

Thanks for the reply - happy that the solution I proposed works for you.