Time Validation

Geekamo
Geekamo used Ask the Experts™
on
Hello Experts!

I am working on a spreadsheet, in which range E:I contains time values.

IE - 1:02:32 (1 hour, 2 minutes, 32 seconds) or 0:01:07 (1 minute, 7 seconds)

All time values can range from just seconds, into hours.  The format of the time is "#:##:##".

What validation rule do I need to apply, in order to force users to type in this format

#:##:##

Thank you in advance for your help!

~ Geekamo
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Microsoft MVP Excel
Most Valuable Expert 2011
Awarded 2010
Commented:
Hello,

format the cell with one of the built-in time formats or the custom format

h:mm:ss

Now the decimal part of any number that is entered into that cell will be shown as a time value.

Excel stores time as decimals. There is no data validation out of the box that can check if a user has entered the time value 1:20:33 or the corresponding decimal 0.0559375

cheers, teylyn
Geekamo

Author

Commented:
@ teylyn

Thank you for your input!  I appreciate it.

~ Geekamo

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial