How do I force a user to use a specific format when entering time data?

Question

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?

Answer 1

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