Is there a way to enter military time in a cell (1700), and have the result formatted in hh:mm (5:00 PM)?

By default if you set the cell or column to format as Time 5:00 PM and enter 17:00 in the cell you will see it as 5:00 PM

Is that what you are looking for or are you looking for 1700 to be converted to Standard US TIME?

What I'm really trying to do is just enter regular time without the colon, for a large Excel sheet for payroll every week.

I thought using military time would work, as it at saved a couple steps entering 17:00 and then having the cell formatted so the result after typing it into the cell was 5:00 PM. (I obviously just using the "1:30 PM" format for the cells.)

Is there an easier way so I can just enter time without using the : (colon)?  It's a pain entering dozens and dozens of clock-in and clock-out times, and always having to shift and click to enter a colon into every time I input. Thanks.

Can you use a helper column?

you can do something like this.
Format the column to Time 5:00 PM and enter this formula in

=TIMEVALUE((CONCAT(LEFT(F1,2),":",MID(F1,3,2))))

Select allOpen in new window

F1 is where you enter you 1700 and G1 is where there formula is entered.

Or, you can use an event handler macro like this. This works for column 3. You can change this in the second row

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 Then
        If Len(Target) = 4 And IsNumeric(Target) Then
            Target.Value = Left(Target, 2) & ":" & Right(Target, 2)
        End If
    End If
End Sub

Unfortunately, I can't use a helper cell....

Saqib ... I'm a novice at macros and VBA. How exactly do I enter in what you suggested so this will work for me?

I am entering times into cells A7:O34 in one particular tab of a large Excel workbook.

Right-click on the sheet tab name
Select View code
Paste the given code in the VBA window
Close the VBA window

Got it. Perfect! It works ... thank you very much!!