2

In Excel 2010 VBA, I'm testing to make sure my code handles invalid user input correctly. I'm using CDate to validate date inputs. I've found that with the invalid date input "0/5/14", CDate returns the date 5/14/2000.

Is that a CDate bug, or am I missing something? In an Excel worksheet cell, "0/5/14" does not evaluate to a date.

Similarly, Year("0/5/14") in Excel VBA returns 2000, while =Year("0/5/14") in an Excel worksheet cell returns an error.

Windows regional settings are English USA, so month/day/year is standard.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Greg Lovern
  • 958
  • 4
  • 18
  • 36
  • Excel is clearly sees 0 as year 0 from 2000. Can not explain why, but to assist with error handling, perhaps check for the number of digits. If there are less than 6 in the string pass back to the user. – Richard Vivian May 07 '14 at 22:23
  • Probably best to use three separate controls, that way you can validate at each step of the way, rather than trying to parse or interpret bad keystrokes/etc. and trap errors arising from same. – David Zemens May 08 '14 at 02:50
  • I realize `IsDate` is outside of the scope defined by your problem, but it is documented in INCREDIBLE detail here and might offer you better validation: http://stackoverflow.com/questions/4338025/isdate-function-returns-unexpected-results – Dan Wagner May 08 '14 at 12:20

2 Answers2

3

The CDate function (and other string-to-date functions such as DateValue) examines a string representation of a date and attempts to match it to any known date format, considering it to be a valid date unless it cannot be made to match any of the known formats. Since it can be the case that years can be expressed as 1 or more digits, the input string "0/5/14" can be considered to be in year/month/day format, so it returns "14th of May, 2000" in your local date format.

The difference between CDate and DateValue is that CDate can accept a number, while DateValue cannot. Both use the PC's Short Date format first - not that that would matter for someone using en-US settings. Both functions fall back to other date formats if the supplied string doesn't fit on the first attempt.

It is up to you how you handle such situations. It may well be that in your situation, a date in year 2000 would be out-of-range, so you could reject it on that basis. If you want to insist on "mm/dd/yyyy" format, you could write your own parser code.

Monty Wild
  • 3,981
  • 1
  • 21
  • 36
  • 2
    CDate does not attempt to use US formats first - it uses your system settings. Unlike DateValue though, CDate will accept a _number_ representing the value of a valid date. – Rory May 08 '14 at 11:33
  • Thanks, @Rory, you're quite right. I've edited my answer to reflect this. – Monty Wild May 08 '14 at 22:41
1

I believe @Borja Güiles Quintana had the correct answer with basically it's reading it as YY/MM/DD. CDate does not exist as a worksheet function so it does not surprise me that the sheet (as opposed to VBA) interpretation differs (would not be the first time, eg TRIM).

Any year (and which part represents year may be system dependent) is interpreted according to rules (that may be version dependent) but for Excel 2013 and two-digit values these stop at 29 for this century - ie 30 is interpreted as 19 30. More details of that here.

pnuts
  • 58,317
  • 11
  • 87
  • 139