Data validation on a custom format

[Robert Crandal]

Hi again everyone....

I am currently using the following custom format:

[h]:mm

How can I use data validation to prevent invalid
data from being entered into a cell of that format??

For my purposes, I only want data which consists
of 1 or more digits in the "hours" field, folowed by
one colon, followed by 1 to 2 digits in the minutes
field.

Do I need to enter some sort of formula into the
data validation field??

thank you!

 

[Dave Peterson]

There's an option in the Data|Validation|Settings tab
that you can set for Time.

It sounds like you want something between 00:00:00 and 9:59:59

Your formatting will hide any seconds that the user enters.

Hi again everyone....

I am currently using the following custom format:

[h]:mm

How can I use data validation to prevent invalid
data from being entered into a cell of that format??

For my purposes, I only want data which consists
of 1 or more digits in the "hours" field, folowed by
one colon, followed by 1 to 2 digits in the minutes
field.

Do I need to enter some sort of formula into the
data validation field??

thank you!

 

[Robert Crandal]

Hmmm, that isnt exactly what I wanted because my data is
not for specifying a time on a clock. My custom format
is used for specifying time durations in hours and
minutes elapsed. So, for example, the following data values
are valid:

00:33 ' 0 hrs & 33 minutes
1:00 ' 1 hr & 0 minutes
250:12 ' 250 hrs & 12 minutes

I would just like to be able to prevent users from entering
data that is different than the above examples. For example,
here are a few invalid data types:

00:::33 ' too many colons
0a:22 ' contains alpha chars
12:11123 ' too many minutes?
...
etc. etc.

There's an option in the Data|Validation|Settings tab
that you can set for Time.

It sounds like you want something between 00:00:00 and 9:59:59

Your formatting will hide any seconds that the user enters.

Hi again everyone....

I am currently using the following custom format:

[h]:mm

 

[Dave Peterson]

Try limiting the value to a date.

And make sure it's less than 10 (for 240 hours) or 20 (for 480 hours) (or
whatever limit you want).

Then try entering some of those values.

Post back with the entries that failed that should have been ok and the ones
that were accepted that should not have been.

Hi again everyone....

I am currently using the following custom format:

[h]:mm

How can I use data validation to prevent invalid
data from being entered into a cell of that format??

For my purposes, I only want data which consists
of 1 or more digits in the "hours" field, folowed by
one colon, followed by 1 to 2 digits in the minutes
field.

Do I need to enter some sort of formula into the
data validation field??

thank you!