How to tell if cell value is time or not

[Shatin]

In cell A1 of a worksheet, the value is entered as 1:50, the format for the
cell is h:mm.

When I test whether the value of A1 is a time or not by using the IsDate()
function, the return value is false:

Debug.Print IsDate(Range("A1"))

How can I test whether the value in cell A1 is a time or not if IsDate()
can't be used?

Thanks!

 

[Joel]

A date is a number with Jan 1, 1900 equalling 1 and every day is 1. A time
is a number with zero equalling midnight with each hour representing 1/24.

The data you entered 1:50 is probably entered as text and not a number. Is
date eally check that the data in the cell is greater than 1.

First check to see if the cell is formated as text. Highlight the cell and
go into the format menu and check how the cell is formattted. Also make sure
there isn't a single quote in front of the number inidcating the data is text.

 

[Bill Martin]

In cell A1 of a worksheet, the value is entered as 1:50, the format for the
cell is h:mm.

When I test whether the value of A1 is a time or not by using the IsDate()
function, the return value is false:

Debug.Print IsDate(Range("A1"))

How can I test whether the value in cell A1 is a time or not if IsDate()
can't be used?

Thanks!

--------------------

Here's a cheezy way to test:

IsTime = false
If InStr(Range("A1").text, ":") > 0 Then IsTime = true

This is adapted from Walkenbach's book "Excel 2003 Power Programming
With VBA", page 332. It's a good reference book for anyone working
with VBA.

Good luck.

Bill

 

[Shatin]

The format is custom h:mm and not text. Even if I change the format to one
of Excel's suggested time formats, the result is still the same. Still
scratching my head...

 

[ward376]

Times and dates in Excel are just formatted numbers, dates to the left
of the decimal and times to the right. What does the content of the
cell look like when it's formatted as General?

You could test if<1 then it's a time.

Cliff Edwards

 

[Aviashn]

I found this interesting. As a quick experiment and possible
explanation as to what might be going wrong, try this..

In a blank workbook format "A1" as time with '13:30' format and "A2"
as text. Enter 1:30 into both.

Now run this macro...

Sub DetermineIfDateType()
Dim datDate As Date
Dim x As Integer
datDate = CDate("1:30")
Range("A3").Value = datDate
Range("A4").Value = Format(datDate, "H:MM")
Range("A5").Value = Format(Range("A2").Value, "H:MM")
For x = 1 To 5
If IsDate(Worksheets(1).Cells(x, 1).Value) = True Then
Worksheets(1).Cells(x, 2).Value = "Date"
Else
Worksheets(1).Cells(x, 2).Value = "Not Date"
End If
Next x
End Sub

Only the text value tests True for IsDate().