vba - IsDate function returns unexpected results -
how come isdate("13.50") returns true isdate("12.25.2010") returns false?
i got tripped little "feature" , wanted raise awareness of of issues surrounding isdate function in vb , vba.
the simple case
as you'd expect, isdate returns true when passed date data type , false other data types except strings. strings, isdate returns true or false based on contents of string:
isdate(cdate("1/1/1980")) --> true isdate(#12/31/2000#) --> true isdate(12/24) --> false '12/24 evaluates double: 0.5' isdate("foo") --> false isdate("12/24") --> true isdatetime?
isdate should more precisely named isdatetime because returns true strings formatted times:
isdate("10:55 am") --> true isdate("23:30") --> true 'cdate("23:30") --> 11:30:00 pm' isdate("1:30:59") --> true 'cdate("1:30:59") --> 1:30:59 am' isdate("13:55 am") --> true 'cdate("13:55 am")--> 1:55:00 pm' isdate("13:55 pm") --> true 'cdate("13:55 pm")--> 1:55:00 pm' note last 2 examples above isdate not perfect validator of times.
the gotcha!
not isdate accept times, accepts times in many formats. 1 of uses period (.) separator. leads confusion, because period can used time separator not date separator:
isdate("13.50") --> true 'cdate("13.50") --> 1:50:00 pm' isdate("12.25") --> true 'cdate("12.25") --> 12:25:00 pm' isdate("12.25.10") --> true 'cdate("12.25.10") --> 12:25:10 pm' isdate("12.25.2010")--> false '2010 > 59 (number of seconds in minute - 1)' isdate("24.12") --> false '24 > 23 (number of hours in day - 1)' isdate("0.12") --> true 'cdate("0.12") --> 12:12:00 this can problem if parsing string , operating on based on apparent type. example:
function bar(var variant) if isdate(var) bar = "this date" elseif isnumeric(var) bar = "this numeric" else bar = "this else" end if end function ?bar("12.75") --> numeric ?bar("12.50") --> date the workarounds
if testing variant underlying data type, should use typename(var) = "date" rather isdate(var):
typename(#12/25/2010#) --> date typename("12/25/2010") --> string function bar(var variant) select case typename(var) case "date" bar = "this date type" case "long", "double", "single", "integer", "currency", "decimal", "byte" bar = "this numeric type" case "string" bar = "this string type" case "boolean" bar = "this boolean type" case else bar = "this other type" end select end function ?bar("12.25") --> string type ?bar(#12/25#) --> date type ?bar(12.25) --> numeric type if, however, dealing strings may dates or numbers (eg, parsing text file), should check if it's number before checking see if it's date:
function bar(var variant) if isnumeric(var) bar = "this numeric" elseif isdate(var) bar = "this date" else bar = "this else" end if end function ?bar("12.75") --> numeric ?bar("12.50") --> numeric ?bar("12:50") --> date even if care whether date, should make sure it's not number:
function bar(var variant) if isdate(var) , not isnumeric(var) bar = "this date" else bar = "this else" end if end function ?bar("12:50") --> date ?bar("12.50") --> else peculiarities of cdate
as @deanna pointed out in comments below, behavior of cdate() unreliable well. results vary based on whether passed string or number:
?cdate(0.5) --> 12:00:00 pm ?cdate("0.5") --> 12:05:00 trailing and leading zeroes significant if number passed string:
?cdate(".5") --> 12:00:00 pm ?cdate("0.5") --> 12:05:00 ?cdate("0.50") --> 12:50:00 ?cdate("0.500") --> 12:00:00 pm the behavior changes decimal part of string approaches 60-minute mark:
?cdate("0.59") --> 12:59:00 ?cdate("0.60") --> 2:24:00 pm the bottom line if need convert strings date/time need aware of format expect them in , re-format them appropriately before relying on cdate() convert them.
Comments
Post a Comment