6

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?

Lance Roberts
  • 8,837
Sarah
  • 71

4 Answers4

6

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".
Hand-E-Food
  • 4,961
2

Old Post, but this may help some searching Google (also I'm not an Excel expert)

  1. Format the Cell as Text with a custom format 00\:00
    This will allow users to enter a military time into the cell without ":"

  2. Set Data Validation options on the cell (EG E16) as custom with the following formula 

    =AND(ISNUMBER(E16),(VALUE(LEFT(TEXT(E16,"00\:00"),2))>=0),(VALUE(LEFT(TEXT(E16,"00\:00"),2))<=23),(VALUE(RIGHT(TEXT(E16,"00\:00"),2))>=0),(VALUE(RIGHT(TEXT(E16,"00\:00"),2))<=59))

The validation checks if it is a number and then if the HH side of the time value is between 0 and 23, and then if the MM is between 0 and 59.

OverStacked
  • 21
0

I would like to suggest VBA (Macro) will take care of Time entry in Specified Column or Range. This Macro will convert any 4 digits value into an appropriate Time Format.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Dim xHour As String
Dim xMinute As String
Dim xWord As String

Application.EnableEvents = False
xWord = Format(Target.Value, "0000")
xHour = Left(xWord, 2)
xMinute = Right(xWord, 2)

On Error Resume Next
Target.Value = TimeValue(xHour & ":" & xMinute)

On Error Resume Next
Application.EnableEvents = True

End Sub

N.B.

  • If you enter 1725 Macro will convert it into 05:25:00 PM.
  • Range ("A:A") is editable, it should Range("A1:A10") or Range("B10:C10") also.
Rajesh Sinha
  • 9,403
0

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.

Mike
  • 1