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

Once you have the cell formatted as a date, is it not sufficient to
apply Data Validation to the cells using the 'Date' option in the
'Allow' menu?

You can specify all types of criteria for the date entered.

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

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

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