Hi,
Try this. It assumes times between 00:00 and 23:59 and the format
is hh:mm i.e. contains a colon ":" as delimeter. You could change to a
function if/as required.

HTH

Sub validateTime(TimeStr)

Dim hh As Integer, mm As Integer

If InStr(1, TimeStr, ":") = 3 And Len(TimeStr) = 5 Then
If IsNumeric(Left(TimeStr, InStr(1, TimeStr, ":") - 1)) Then
hh = Left(TimeStr, InStr(1, TimeStr, ":") - 1)
If IsNumeric(Right(TimeStr, Len(TimeStr) - InStr(1, TimeStr, ":")))
Then
mm = Right(TimeStr, Len(TimeStr) - InStr(1, TimeStr, ":"))
If hh = 0 And hh <= 23 Then
If mm = 0 And mm <= 59 Then
Exit Sub
End If
End If
End If
End If
End If

MsgBox "Time " & TimeStr & " is invalid"

End Sub

"Help me" wrote:

Hi Guys, Thanks for your response.

Perhaps i should have been more specific. The problem is that the cells
will not specified a Date/Time. it will be set to general (text). So i need
to somehow use regular exp to determine whether the value in the cell is of
the type hh:mm.
cheerx

"Bob Phillips" wrote:

Excel stores time as a fraction of 1 day, 12 hours is .5, 8 hours is .3333.
So any number is valid time to Excel. I assume you mean you want to ensure
that they enter formatted time, but what is valid to you? Is 25:12 valid, or
does it have to be <= 24 hours?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Help me" wrote in message
...
Hi,

Just wondering whether i could steal some code from someone. I need to
validate a cell which should contain a valid time. e.g. 12:28 or 23:32
etc?

I've found some regular expression validation on the internet but it
doesnt
quite work well enough.

Cheers