Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data validation on a custom format
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data validation on a custom format
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. Robert Crandal wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data validation on a custom format
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. "Dave Peterson" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data validation on a custom format
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. Robert Crandal wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom data validation on cells with data validation values | Excel Worksheet Functions | |||
Format Cell as custom type but data doesn't display like I custom. | Excel Discussion (Misc queries) | |||
custom data validation | Excel Worksheet Functions | |||
Custom function valid in Data Validation/Conditional Format? | Excel Programming | |||
Custom Format and Data Validation Q | Excel Worksheet Functions |