1

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?

Improve this question

1 Answer 1

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for your comprehensive answer. I still don't fully understand the issue but for my situation I've got the right information (I think), I will add my findings as an answer to my question

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.