We help IT Professionals succeed at work.

Excel Data Validation: Insure users enter times in format   hh:mm:ss   only?

Cactus1993
Cactus1993 asked
on
I have my columns formatted hh:mm:ss ... is there a way to insure users enter data such as simply 09:08, or at the worst, 00:09:08, when they enter for example the time of nine minutes, eight seconds?

I need the entries to be time entries as I need to run calculations with them -- they can't be text entry.

For example, the time 9:08 is actually 12:09:08 AM, but is formatted to look like 9:08 using the hh:mm:ss format. I want to ensure the user enters 00:09:08, or even better 9:08 ... otherwise, it will appear as 09:08:00 (nine hours, eight minutes, instead of nine minutes, eight seconds as I need.

Thanks.

Thanks. Workbook4.xlsx
Comment
Watch Question

Finance Analyst
CERTIFIED EXPERT
Commented:
Use Data Validation.

Press Alt + D + L to get the DV window.

The list of options under the "Allow" dropdown has an option for time. You can specify minimum and maximum times.

So if you know the activity is going to take less than an hour then set the Max to 00:59:00 ie 59 Minutes.

Thanks
Rob H
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
Note that if you enter 9:08 that will always be interpreted as 9 hours 8 minutes (regardless of cell formatting or data validation). You need to enter 0:09:08

regards, barry
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
As Barry says, an entry of 9:08 would be rejected by the DV I suggested because it is greater than the maximum value of 00:59:00

Thanks
Rob H
Cactus1993Owner

Author

Commented:
Thanks Rob. I didn't think of setting a max, and in fact, these times will never be over an hour. I want the user to know they have to enter the time as 0:09:08, so I guess using the max and have it trigger an error message if they don't, will work?

Barry, is there a way to validate the user actually enters 0:09:08 (including the zero before the first colon) or otherwise trigger an error message, without setting a maximum to the time in DV?
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
I'd go with Rob's suggestion.....I don't know another way....

Note that it doesn't actually force the user to enter a time. Any number that would be converted to a time within the valid range would be accepted, e.g. 0.01 (although if the cell is formatted as time it will display the converted time value)

regards, barry
Cactus1993Owner

Author

Commented:
Thanks again, guys. And good talking with you again, Barry.

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