Excel Data Validation

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

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.

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

Dave

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.

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

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?

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

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?

Please award the 500 pts to dlmille ... I can't seem to cancel whatever request was entered "not" to award points to him/her.

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

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

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.

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."

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

Dave

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

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

Thanks,

JustAMod
CS Moderator