Formula for populating an Excel Spreadsheet

%3CLINGO-SUB%20id%3D%22lingo-sub-2628777%22%20slang%3D%22en-US%22%3EFormula%20for%20populating%20an%20Excel%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2628777%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20project%20is%20to%20populate%20an%20Excel%20Spreadsheet%20with%20military%20time%20w%2Fo%20using%20the%20colon%20%5B%3A%5D.%3C%2FP%3E%3CP%3EI%20want%20to%20do%20this%20with%20a%20formula%20and%20not%20have%20to%20keyboard%20it%20in%20using%20TEXT%20or%20having%20to%20use%20the%20colon.%26nbsp%3B%20T%3CSPAN%20class%3D%22lia-mentions-symbol%22%3E%40%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Blinks%20to%20members%3C%2FSPAN%3Ehis%20is%20for%20one%2024%20hour%20period.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2628777%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2632870%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20populating%20an%20Excel%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2632870%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F452489%22%20target%3D%22_blank%22%3E%40Charles_G%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20are%20some%20formula%20approaches%20as%20well%20as%20with%20VBA%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DLEFT(A1%2C%202)%20%26amp%3B%20%22%3A%22%20%26amp%3B%20RIGHT(A1%2C%202)%3C%2FP%3E%3CP%3E%3DREPLACE(A1%2C%26nbsp%3B%203%2C%26nbsp%3B%200%2C%26nbsp%3B%20%22%3A%22)%3C%2FP%3E%3CP%3E%3DTIME(LEFT(A1%2C2)%2CRIGHT(A1%2C2)%2C)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20VBA%20Code%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%0A%20%20%20Dim%20Eingabe%20As%20Variant%0A%20%20%20On%20Error%20GoTo%20ErrorHandler%0A%20%20%20If%20Not%20Intersect(Target%2C%20Columns(1))%20Is%20Nothing%20Then%0A%20%20%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20%20%20With%20Target%0A%20%20%20%20%20%20%20%20%20.Value%20%3D%20CDate(Left(Format(Target%2C%20%220000%22)%2C%202)%20%26amp%3B%20%22%3A%22%20%26amp%3B%20Right(Target%2C%202))%0A%20%20%20%20%20%20%20%20%20.NumberFormat%20%3D%20%22%5Bhh%5D%3Amm%22%0A%20%20%20%20%20%20End%20With%0A%20%20%20End%20If%0AErrorHandler%3A%0A%20%20%20Application.EnableEvents%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20any%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20Community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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.