| CDate Function |
Named Arguments
No
Syntax
CDate(expression)
expression
Use: Required
Data Type: String or Numeric
Any valid date expression.
Return Value
expression converted into a Date data type.
Description
Converts expression to a Date data type. The format of expression—the order of day, month, and year—is determined by the locale setting of your computer. To be certain of a date being recognized correctly by CDate, the month, day, and year elements of expression must be in the same sequence as your computer's regional settings; otherwise the CDate function has no idea that 4 is supposed to be the 4th of the month, not the month of April.
CDate also converts numbers to a date. The precise behavior of the function, however, depends on the value of expression :
If expression is less than or equal to 23 and includes a fractional component less than 60, the integer is interpreted as the number of hours since midnight, and the fraction is interpreted as the number of seconds.
In all other cases, the integer portion of expression is converted to a date that interprets the integer as the number of days before (in the case of negative numbers) or after December 31, 1899, and its fractional part is converted to the time of day, with every .01 representing 864 seconds (14 minutes 24 seconds) after midnight.
Rules at a Glance
CDate accepts both numerical date expressions and string literals. You can pass month names into CDate in either complete or abbreviated form; for example, "31 Dec 1997" is correctly recognized.
You can use any of the date delimiters specified in your computer's regional settings; for most systems, this includes ",", "/", "-", and " ".
The oldest date that can be handled by the Date data type is 01/01/100, which in VBA terms equates to the number –657434. Therefore, if you try to convert a number of greater magnitude than –657434 with CDate, an error ("Type mismatch") is generated.
The furthest date into the future that can be handled by the Date data type is 31/12/9999, which in VBA terms equates to the number 2958465. Therefore, if you try to convert a number higher than 2958465 with CDate, an error ("Type mismatch") is generated.
A "Type mismatch" error is generated if the values supplied in expresssion are invalid. However, CDate tries to treat a month value greater than 12 as a day value.
Programming Tips and Gotchas
Use the IsDate function to determine if expression can be converted to a date or time.
A common error is to pass an uninitialized variable to CDate, in which case 31 December 1899 is returned.
A modicum of intelligence has been built into the CDate function. It can determine the day and month from a string regardless of their position, but only where the day number is larger than 12, which automatically distinguishes it from the number of the month. For example, if the string "30/12/97" were passed into the CDate function on a system expecting a date format of mm/dd/yy, CDate sees that 30 is obviously too large for a month number and treats it as the day. It's patently impossible for CDate to second guess what you mean by "12/5/97"—is it the 12th of May, or 5th of December? In this situation, CDate relies on the regional settings of the computer to distinguish between day and month. This can also lead to problems, as you may have increased a month value to more than 12 inadvertently in an earlier routine, thereby forcing CDate to treat it as the day value. If your real day value is 12 or less, no error is generated, and a valid, albeit incorrect, date is returned.
If you pass a two-digit year into CDate, how does it know which century you are referring to? Is "10/20/97" 20 October 1997 or 20 October 2097? The answer is that two-year digits less than 30 are treated as being in the 21st Century (i.e., 29 = 2029), and two-year digits of 30 and over are treated as being in the 20th Century (i.e., 30 = 1930).
Don't follow a day number with "st", "nd", "rd", or "th", since this generates a type mismatch error.
If you don't specify a year, the CDate function uses the year from the current date on your computer.
A CVDate function is also provided for compatibility with earlier versions of Visual Basic. The syntax of the CVDate function is identical to the CDate function. However, CVDate returns a Variant whose subtype is Date instead of an actual Date type. Since there is now an intrinsic Date type, there is no further need for CVDate.
The Date data type is basically a Double data type. You can therefore return the underlying date number (i.e., the number of days after or before 31 December 1899) by converting the date variable to a double. For example:
Dim dtDate as Date Dim dblDate as Double Dim sDate as string SDate = "31/12/97" DtDate = Cdate(sDate) DblDate = CDbl(DtDate) 'returns 35795
This can be useful for converting back and forth between Unix dates, which have a starting point of 1 January 1970 (VB date number 25569), and are based as number of seconds, as this snippet demonstrates:
Dim dblDate As Double Dim dblUnix As Double dblDate = CDbl(dtDate) dblUnix = (dblDate - 25569) * 86400
All date functions, including CDate, are affected by the application's Calendar property setting. For example, if the Calendar property has been set to vbCalHijri, the underlying date number is increased by 206362.