CODEWITHSUNDEEP
excelms-accessvba
waanders
waanders

Reputation: 9083

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.

Anybody?

UPDATE 9/12:

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?

Upvotes: 1

Views: 5057

Answers (1)

Dr. belisarius
Dr. belisarius

Reputation: 61016

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).

HTH!

Edit I think the answer to this question may help you too.

Upvotes: 4

Related Questions