Formula for populating an Excel Spreadsheet

Copper Contributor

My project is to populate an Excel Spreadsheet with military time w/o using the colon [:].

I want to do this with a formula and not have to keyboard it in using TEXT or having to use the colon.  T@ links to membershis is for one 24 hour period.

1 Reply

@Charles_G 

Here are some formula approaches as well as with VBA code.

 

=LEFT(A1, 2) & ":" & RIGHT(A1, 2)

=REPLACE(A1,  3,  0,  ":")

=TIME(LEFT(A1,2),RIGHT(A1,2),)

 

With VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Eingabe As Variant
   On Error GoTo ErrorHandler
   If Not Intersect(Target, Columns(1)) Is Nothing Then
      Application.EnableEvents = False
      With Target
         .Value = CDate(Left(Format(Target, "0000"), 2) & ":" & Right(Target, 2))
         .NumberFormat = "[hh]:mm"
      End With
   End If
ErrorHandler:
   Application.EnableEvents = True
End Sub

 

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.