Inconsistent CDate () behavior
Regional settings in Windows: MM/DD/YYYY
Now take a look at these two lines and change MONTH:
Sub DateBug()
Debug.Print Format(CDate("11/4/1999"), "MMMM D, YYYY")
Debug.Print Format(CDate("30/4/1999"), "MMMM D, YYYY")
End Sub
OUTPUT:
November 4, 1999
April 30, 1999
Great, huh? What's happening?
On the second line, I want the error , not "guess".
This is mistake? Any idea how to get around this?
The same in Excel 2007, 2010, 2013
This is not a mistake, its "function"
CDate () will interpret the string according to fixed rules. If the date is valid as m / d / y, it will be returned. If the date is invalid as m / d / y, but valid as d / m / y, then it will be returned. If the date is invalid as m / d / y or d / m / y then an error will be returned.
Sub dural()
Dim st As String
st = CStr(CDate("12/4/2012")) & vbCrLf & CStr(CDate("13/4/2012"))
MsgBox st
End Sub
source to share
Not a mistake. VB converts strings to modern literals using the default locale, if possible. What he did the first day. But on the second day it would give an invalid date, so he tried DD / MM / YYYY and it worked. Solution, put dictionary characters instead of strings. Put # signs around both date literatures (ex: "# 4/30/1999 #") to ignore locale and force the MM / DD / YYYY format. '# 30/4/1999 #' would give you the error you want. This will make your code independent of your computer's regional settings, meaning it will work independently. Or use DateTime (1999, 4, 30, 0, 0, 0). See Here ... Date Data Type (Visual Basic)
In Excel, you have to use a literal without single quotes:
Sub Macro1()
Range("D2").Select
ActiveCell.FormulaR1C1 = CDate(#4/30/1999#)
End Sub
source to share