asked on

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

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

ASKER CERTIFIED SOLUTION

Rob Henson

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

barry houdini

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 Henson

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

Cactus1993

ASKER

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?

barry houdini

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

Cactus1993

ASKER

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