Aug 09 2021 11:46 AM
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.
Aug 10 2021 09:28 AM
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.