Convert time to a 1 hour range

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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 ”
Excel Formula:
 =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 “
Excel 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 “
Excel Formula:
=(H2+0.5/24)
” to get value 12:20. In Column K I used “
Excel Formula:
=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 “
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows
Try this

VBA Code:
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
 
Solution

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top