Time Formatting

I would like to avoid using the colon when entering a time. Can a cell be formatted to read the a military time like 1525 (not 15:25) and convert it to 3:25pm?

Thanks in advance

Hello rtcol,

Excel doesn't provide a time format without a colon. If you wanted to enter the numbers in this way, you would need a macro. The macro would then examine the cells, make the conversion, and then set the cell's value to the am/pm format.

Here is an example that will convert any valid 24 hour entry (less the colon) in the range of D2 to D10 of the Worksheet you choose (see the installation instructions).

Private Sub Worksheet_Change(ByVal Target As Range)
  
  Dim Hrs As Integer
  Dim Minutes As Integer
  Dim TimeRng As Range
  
  Application.EnableEvents = False
  
    Set TimeRng = ActiveSheet.Range("D2:D10")
      If Not Intersect(Target, TimeRng) Is Nothing Then
         If IsEmpty(Target) Then GoTo LeaveSub
         If Target.Value < 0 Or Target.Value > 2400 Then GoTo LeaveSub
            Hrs = Target \ 100
            Mins = Target - (Hrs * 100)
         Target = Format(TimeSerial(Hrs, Mins, 0), "hh:mm am/pm")
      End If
      
LeaveSub:
  Application.EnableEvents = True
  
End Sub

How to Save a Worksheet Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Save the macro in your Workbook using CTRL+S

Sincerely,
Leith Ross