CODEWITHSUNDEEP
excelvbadatetype-conversion
Nacorid
Nacorid

Reputation: 793

Unexpected CDate() behavior

While going through some old code without error handling I stumbled upon unexpected CDate() function behavior.

Sub Test()
    Dim userinput as Variant
    userinput = Application.Inputbox("Enter a date")
    'userinput will be a Variant/String with the inputs below
    Debug.Print CDate(userinput)
End Sub

Input: "27/8", "27/08", "27-8", "27-08", "27.8", "27.08"
Output: 27.08.2019 ' for all of the above

Input: "27.8.", "27.08."
Output: 04.10.1900, 31.05.1907

I was either expecting Error 13: Type-mismatch, or 27.08.1900 or 27.08.2019 as output.
What is happening with the latter two inputs? I can't wrap my head around it.

Additional input: "26.8." -> output: 24.09.1900
Input: "26.08." -> output: 20.02.1907

Regional setting is German (Germany) (Deutsch (Deutschland))
Date format is DD.MM.YYYY

Edit:
The complete userinput code looks like this: 

Sub Test()
    Dim userinput As Variant
    Dim cancelBool As Boolean
    Do While Not ((IsDate(userinput) And Not userinput = vbNullString) Or cancelBool)
        userinput = Application.InputBox("Enter a date")
        If userinput = False Then cancelBool = True
        'the following line was inspired by Plutian
        If Not IsDate(userinput) And IsNumeric(userinput) Then userinput = userinput & Year(Now())
    Loop
    If Not cancelBool Then Debug.Print CDate(userinput)
End Sub

Upvotes: 1

Views: 134

Answers (2)

CLR
CLR

Reputation: 12279

This doesn't appear to be a CDate behaviour problem, just a text-to-number conversion in general problem.

I have no citation, but from observation: When attempting to convert text to a numeric value, Excel will check to see if the text is an obvious date. If it's not, it will then strip out any thousand's separator - also local currency symbols, and other things, no doubt - to reduce the text to a number where possible.

So, on my English locale set up:

"27.8" ("27,8" on yours) is a recognisable decimal value = 27 days and 8/10ths past 31/12/1899 = 26/01/1900 19:12:00

"27,8" ("27.8" on yours) is a not recognisable decimal value, nor is it a recognisable date so it becomes "278" as it strips out the 000 separators (commas on my set up, periods on yours) 278 days past 31/12/1899 = 27/09/1900

As pointed out by @Nacorid however, CDATE treats this a little differently (to standard conversion) and attempts to resolve this to a date - being 27 Aug (current year).

"27.8." ("27,8," on yours) throws an error, as is a not a recognisable date and due to the two decimal pointers an Error is produced.

"27,8," ("27.8." on yours) is not a recognisable date, and Excel assumes the 000 separators need removing so converts this to 278 =278 days past 31/12/1899 = 04/10/1900

So, the TL;DR is that "27.8." - while acceptable in German as a date - is not acceptable to Excel and you'll need to trap these and add an assumed year or similar to get around it.

Alternatively, consider adding a calendar pop-up form that forces the user to provide day, month and year.

Upvotes: 1

Plutian
Plutian

Reputation: 2309

Your issue here is not with the cdate function as it is giving the expected behaviour when the input is formatted as a string caused by the German custom of writing a date without the year as dd.mm.. The issue is how to handle this input and still get the expected result, this can be achieved with the following code:

    If IsNumeric(userinput) Then
    Else
    userinput = CDate(userinput & Year(Now()))
    End If

Which forcefully inserts the current year in the user input when the variant is not recognised as numeric, which is caused by ending on a .. This works since dates in excel are always stored as a numeric value. Adding the year to the output converts it back to a numeric value which cdate can handle, since excel will now recognize the preferred separator as indeed a separator and handles it as a date as expected.

To me this would be a preferred alternative to forcing the user to amend their input. However wouldn't work if the required date is not in the current year, and might cause issues around new years. Alternatively you could replace the year snippet with a plain "0" or any year of your choice.

Upvotes: 1

Related Questions