Validating Time e.g. 23:17

[Help me]

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

[JakeyC]

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.

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Help me]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Toppers]

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >