How to check Excel time values in Access VBA?
I'm processing an Excel workbook with Access VBA (see Reading an Excel workbook from Access) and now I want to check if a cell contains a valid time value. For dates I use IsDate() and it works fine.
But with time values it doesn't (since there is no IsTime() I also use IsDate() for time values). Or should I use IsNumeric because time values are stored as doubles?
With " 12:00:00" (with space) IsDate() gives TRUE (!?) but Excel itself doesn't recognize it as a time value, the VBA debugger also shows it as a text string.
With "12:00:00" IsDate() gives FALSE but Excel itself recognize it as a time value, the VBA debugger shows the value 0,5.
Thanks to @belisarius and @mwolfe02 I found this for my situation:
/* Valid time value */ IsDate() = False, IsNumeric() = True, TypeName() = Double /* Numeric (no time) value */ IsDate() = False, IsNumeric() = True, TypeName() = Double /* Valid date value */ IsDate() = True, IsNumeric() = False, TypeName() = Date /* Invalid time value (e.g. with leading space as in above example) */ IsDate() = True, IsNumeric() = False, TypeName() = String
So I only need to check if TypeName() returns Double and IsNumeric() True and to avoid confusion with 'normal' numeric values if the value is >= 0 and < 1. Right?
There are two different concepts:
1) Date & Time format
When you format a cell in Excel to contain date (and/or) time, Excel uses an internal representation. According to MS: By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default. Numbers to the right of the decimal point in the serial number represent the time; numbers to the left represent the date. For example, the serial number .5 represents the time 12:00 noon.
And that is it. Dates and times are stored as decimal numbers.
2) Things that VBA could convert to a Date/Time
This is what IsDate checks. According to MS:
Returns a Boolean value indicating whether an expression can be converted to a date. A True returned by IsDate does not mean that a cell has been formatted as Date/Time, it just tells you that you may convert this value to a date/time. But worse: the function may return different values for different platforms: In Microsoft Windows, the range of valid dates is January 1, 100 A.D. through December 31, 9999 A.D.; the ranges vary among operating systems.
What is clear is that Excel did not validate the contents of the cell as a date/time. It's almost sure a text containing whatever characters the user typed in (as the space in your example). The IsDate() return is telling you that VBA is not able to convert the value to a date, but perhaps you can, if you know the format (and write an ad-hoc function for the conversion).
Edit I think the answer to this question may help you too.