Convert time to a 1 hour range

[Livin404]

Greetings I have in Column B a 24 hour time in a text format for example 1150. I need to get it to display 30 minutes prior and 30 minutes post, so I would need it to display “1120 – 1220” in Column B.



I know the formulas if I were to use the formula bar; however, I need a VBA so users could edit the end results, and I don’t want to lose my formulas.

Using the formula method, I had to create more columns to get my result. I’m hoping with a VBA it can all appear in the original Column. With the time put in Column D “1150” I had to establish the time in Column H with a formula of ”

 =TIMEVALUE(LEFT(D2,2)&":"&MID(D2,3,2))

” and that gave me 11:50. For Column I which now I’ll use Column H as my source I used the formula “

=MOD(H2-0.5/24,1)

” that would give me “11:20” this also accounts for if a time that would roll back to the previous day. For Column J I would use the formula “

=(H2+0.5/24)

” to get value 12:20. In Column K I used “

=TEXT(I2,"hh:mm")&"-"&TEXT(J2,"hh:mm")

” this would result in 11:20-12:20 and the format would be in General. Lastly I would need leave it in General format, so I can drop the colons; this is the formula I used “

=SUBSTITUTE(K2,":","")

” this results in 1120-1220. These work as designed, but I'm hoping to make the next step and not have the formulas. Thank you,

 

[Peter_SSs]

Try this

Sub Time_Range_v3()
  Dim wsAct As Worksheet
  
  Set wsAct = ActiveSheet
  Application.ScreenUpdating = False
  With Sheets("Inbound FIDS")
    .Activate
    With .Range("B2", .Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(#="""","""",iferror(text(1+replace(#,3,0,"":"")-1/48,""hhmm-"")&text(replace(#,3,0,"":"")+1/48,""hhmm""),#))", "#", .Address))
    End With
  End With
  wsAct.Activate
  Application.ScreenUpdating = True
End Sub

 

[Livin404]

Thank you that did the trick. I was able to get it to work, but the problem I kept getting was the sheet I was sending it to became my active sheet which I didn't want. Your VBA is gold.

 

[Peter_SSs]

but the problem I kept getting was the sheet I was sending it to became my active sheet which I didn't want.

Not sure what you mean by that. My code leaves the active sheet as the same sheet as when my code starts. If you wanted a different active sheet at the end that is easy to change If you haven't done that already.

 

[Livin404]

Not sure what you mean by that. My code leaves the active sheet as the same sheet as when my code starts. If you wanted a different active sheet at the end that is easy to change If you haven't done that already.

Oh sorry your code was perfect. It was me earlier trying different options. One of my owns codes was working, but it changed my active sheets.

 

[Peter_SSs]

OK, glad it is sorted.