We help IT Professionals succeed at work.

Excel Data Validation

Cactus1993
Cactus1993 asked
on
How do I enter Data Validation so that the user is required to enter a TIME in the format 9:08.44, where the 9 is the minutes, the 8 is the seconds, and the .44 is the hundredths of a second.

(The cell output looks like 9:08.44 on the spreadsheet due to the way it is formatted, but the actual entry appears in the formula box as 12:09:08 AM)

I want to make sure they enter their number (in this case, running times) in this format, otherwise the output formulas won't work.

Thanks!
Comment
Watch Question

Kamaraj SubramanianApplication Support Analyst

Commented:
right click on the cell -> choose "format cells" -> choose Number tab --> choose Text and click ok
Cactus1993Owner

Author

Commented:
Not that easy. If the text entry is input, but still isn't in the correct format of :

mm:ss.00

... it's not going to work.
Most Valuable Expert 2012
Top Expert 2012
Commented:
First proposed solution:  a simple/fit for purpose approach (this can be done more pedantically, but let's see if the simple solution works, without needing VBA for validation testing - just easier to support):

First, I suggest formattimg (custom format) the cells to mm:ss.00, so you see that after its is typed, regardless of whether the user entered 12:09:08 AM or 9:08.44.  Then, we can test to see if the data entered is within a "reasonable" time from 12:00:00 AM.

Second proposed solution:
Take a look at this tip:  It shows you how to setup an input mask like this, where the user just types the digits 093844 where the cell to the right (with some math) would convert that to 9:38.44

You could then do data validation on entering 6 numeric values.  Would something like this be an option?

Third proposed solution:
Put some VBA in to check exactly the text being typed in, and that it conformed to the mask [m]m:ss.0[0]
I thought about that and came up with a solution - but at least at this time, it REQUIRES the user to type a ' ahead of the number - so its entered as a TEXT entry

E.g., the user enters '9:05:4  instead of 9:05.4  - the first would PASS validation, the second would not.

See attached and try that out and see if that's the direction you'd like to go,

Dave
dataValidationTimeEntry-r1.xlsm
Most Valuable Expert 2012
Top Expert 2012

Commented:
PS - re: second proposed solutoin: here was that tip http://excel.tips.net/T002746_Using_an_Input_Mask.html

Dave
Cactus1993Owner

Author

Commented:
Dave:

Thanks so much for taking the time and offering three different solutions. Unfortunately, they won't work my spreadsheet. I'm entering columns of run times, and have to use them in various subsequent calculations, so they can't be text entries whatsoever. It's not only the appearance of the format, but working with them as well that I'm after. All my calculations work currently, I'm just needing to validate for different users that their entries are in the correct format, otherwise it throws off all those calculations.

To your solutions:

1. The column of time entries is already custom formatted to mm:ss.00 in my spreadsheets. It's the input I want to validate for the user, ensuring it's always entered correctly.

2. http:excelips.net solution: I can't use the contents of the cell directly in time calculations using this solution.

3. Like with the second solution, entering the time with a ' makes it unusable in time calculations.

Thanks again, Dave. Hopefully you might have another idea, too?

Tim.
Most Valuable Expert 2012
Top Expert 2012

Commented:
You need to meet me halfway here -
Did you try solution #3 to see if it operated the way you'd like (independent of text entry)?  I spent some time on that and would appreciate if that's the behavior you're looking for, independent of text vs numeric entry.

Text entries can be transformed to numeric, ahead of your need to work with them later in your spreadsheet.

This last option, if the "simpler" approaches don't work for you, is probably the best hope for validation within Excel.  Give it a shot - Just download it and let me know if the behavior is like what you want.  

I realize you want numeric entry - may be possible - or we might need to make a modification to your base workbook - I hadn't got that far beyond trying to get back on what I'd done to this point.  

So - try it out and let me know.  Try to break it.  Do text ' entry for now.

Dave
Cactus1993Owner

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Cactus1994's comment http:/Q_27431182.html#37083419

for the following reason:

Hi Dave:<br /><br />I'm a little hesitant to start adding VBA to an already complex spreadsheet (lots of nested formulas, graphics, etc.) and am fearful adding VBA to the mix might make it too heavy in the distribution of it to many different users. Plus, while I think I'm very good at formulas, nesting, graphics, layouts ... I admit I'm not as adept at VBA.<br /><br />Someone else suggest that getting back to the Data Validation area, I could set a maximum of 59:59 of the entry ... thereby eliminating the possibility of an incorrect entry of 9:08:00 instead of 0:09:08. That may work and eliminate a more complex methodology?
Cactus1993Owner

Author

Commented:
Not sure what's going on here. I just wanted to award points and close out the question.
Cactus1993Owner

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Cactus1994's comment http:/Q_27431182.html#37083419

for the following reason:

Hi Dave:<br /><br />I'm a little hesitant to start adding VBA to an already complex spreadsheet (lots of nested formulas, graphics, etc.) and am fearful adding VBA to the mix might make it too heavy in the distribution of it to many different users. Plus, while I think I'm very good at formulas, nesting, graphics, layouts ... I admit I'm not as adept at VBA.<br /><br />Someone else suggest that getting back to the Data Validation area, I could set a maximum of 59:59 of the entry ... thereby eliminating the possibility of an incorrect entry of 9:08:00 instead of 0:09:08. That may work and eliminate a more complex methodology?
Cactus1993Owner

Author

Commented:
Please award the 500 pts to dlmille ... I can't seem to cancel whatever request was entered "not" to award points to him/her.
Most Valuable Expert 2012
Top Expert 2012

Commented:
Just hit request attention and state the issue and a moderator will handle.

Yes, you can put a numeric limit on the entry - ensuring that no alpha are entered and the number is in a "reasonable" range.  The max, i believe, should be 59, as DV doesn't take time formats (I tried that :)

Cheers,

Dave
Cactus1993Owner

Author

Commented:
This is completely confusing. Just remove this objection, and credit dlmille with the points, please.
Cactus1993Owner

Author

Commented:
Dave: I also hit request attention to this. Somehow an objection is blocking everything, so hopefully a moderator will indeed get involved and handle this. Thanks again. Tim.
Cactus1993Owner

Author

Commented:
I also responded according to the following request from EE:

"To cancel this request and generate a request in Community Support for Moderator review, state your reason for objecting in the standard comment box and click the 'Object' button. This question will be closed on 11/8/2011 if there are no objections."
Most Valuable Expert 2012
Top Expert 2012

Commented:
Since there is an Objection, it won't automatically close and a moderator will have to intervene.  Don't sweat it.

Dave
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
In Excel 2003 and 2010, you can specify data validation as a time between a lower limit and and upper limit. The lower limit might be the world record for the distance and the upper limit should be chosen generously given the caliber of your runners. This will force the data entry person to enter at least a single decimal place for the seconds and thereby avoid confusion between hours and minutes.

This seems like a pretty practical compromise to me, but is really Dave's first suggestion with some more detail.

If you like one of his Comments you should be able to choose it as the Answer. Acting as Zone Advisor, I can see those links. If you cannot see them, please post which Comment you want to choose as Answer and I'll try to close the question for you as you have suggested.

Brad

Commented:
Cancelling the Objection so that the asker can award as needed.

Thanks,

JustAMod
CS Moderator

Explore More ContentExplore courses, solutions, and other research materials related to this topic.