Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to ensure the users of a particular spreadsheet are entering the time data in military time. I'm finding some folks are omitting the colon, and it's causing problems. Is there a way to force a specific formatting for a range of cells?

share|improve this question

I'm using Excel 2010 so I'm not sure where the equivilent option is in Excel 2003.

  1. Select the cells you want to restrict.
  2. Format the cells with the appropriate number format "hh:mm".
  3. In the "Data" ribbon (menu, maybe?), select "Data Validation".
  4. Under "Settings", "Validation criteria", set:
    Allow = Time
    Ignore blank = Yes
    Data = between
    Start time = 0:00
    End time = 23:59:59
    (stupid thing won't accept "less than 24:00")
  5. User "Error Alert", set:
    Style = Stop
    Title = Time Entry
    Error message = Please type times in hh:mm format with the colon.
  6. Press "OK".
share|improve this answer
Thank you so much. This solved my problem. – Sarah Aug 20 '11 at 4:14
Glad to help! Please mark this as the correct answer. :-) – Hand-E-Food Aug 21 '11 at 23:17

I use the same thing to restrict to military time, however, the Data Validation accepts fat-fingered entires (three digits after the colon) and then shows an incorrect time. For instance if you want to enter 13:44 for your time and accidently put 13:444, it not only takes it but displays a time of 20:24

That 20:24 time is just what I entered for the hours (13) plus the 444 minutes converted to hours:minutes (7:24) so instead of the desired 13:44 it gives 13:00 + 7:24 or 20:24.

Now, I understand the math and why Excel converts 444 minutes to 7:24, but that should NOT occur in a Data Validation entry. Being this is Data Validation it should either truncate 444 to 44 or give an error message.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .