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,
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,374
Office Version
  1. 365
Platform
  1. Windows
See if this does what you want.

VBA Code:
Sub Time_Range()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=TEXT(1+REPLACE(RC[-1],3,0,"":"")-1/48,""hhmm-"")&TEXT(REPLACE(RC[-1],3,0,"":"")+1/48,""hhmm"")"
    .Value = .Value
  End With
End Sub


Test data & results:

20 11 15.xlsm
AB
1Text TimeHour Range
211501120-1220
302250155-0255
422252155-2255
523502320-0020
600012331-0031
Time range
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
See if this does what you want.

VBA Code:
Sub Time_Range()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=TEXT(1+REPLACE(RC[-1],3,0,"":"")-1/48,""hhmm-"")&TEXT(REPLACE(RC[-1],3,0,"":"")+1/48,""hhmm"")"
    .Value = .Value
  End With
End Sub


Test data & results:

20 11 15.xlsm
AB
1Text TimeHour Range
211501120-1220
302250155-0255
422252155-2255
523502320-0020
600012331-0031
Time range
Thanks for getting back with me on that. It's been a few days. In the mean time I came up with
VBA Code:
Function GET30MINRANGE(strTime As String) As String
Dim strRange, strHour, strMin, lowerBound, upperBound As String
Dim timeVal As Date

strMin = Mid(strTime, 3, 2)
strHour = Left(strTime, 2)
timeVal = Date + TimeValue(strHour & ":" & strMin)

lowerBound = Format(timeVal - TimeSerial(0, 30, 0), "hhnn")
upperBound = Format(timeVal + TimeSerial(0, 30, 0), "hhnn")
strRange = lowerBound & "-" & upperBound

GET30MINRANGE = strRange
End Function

Then I use the code:
Excel Formula:
=IFERROR(GET30MINRANGE(B2),"")
in column H. It gives me the results I need after a macro is used to cut and paste the values in. The only thing I need now is it to ignore the word ETA in Column B. I got it to ignore blanks spaces, but still trying to to get to ignore ETA. I want ETA to remain in the Column. I did try yours but it did end up putting the formula in the cells; I'm sure it's an oversight on my part. I will like your submission, so others don't respond. I tried to delete the post but couldn't figure out how. Many thanks again.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,374
Office Version
  1. 365
Platform
  1. Windows
If you have developed the udf to use in the worksheet then you could perhaps consider using native worksheet functions instead since they are generally more efficient than udfs?

Adapting my previous approach to allow for blanks and ETA, what about the formulas in columns G:H below?
Two formulas because I wasn't quite sure what you meant about "keeping ETA" - didn't know whether you meant in column B or the formula column.

Livin404_1.xlsm
BCDEFGH
1Text TimeHour RangeHour Range
211501120-12201120-1220
302250155-02550155-0255
4  
5ETAETA 
622252155-22552155-2255
723502320-00202320-0020
800012331-00312331-0031
Time range
Cell Formulas
RangeFormula
G2:G8G2=IFERROR(TEXT(1+REPLACE(B2,3,0,":")-1/48,"hhmm-")&TEXT(REPLACE(B2,3,0,":")+1/48,"hhmm"),B2&"")
H2:H8H2=IFERROR(TEXT(1+REPLACE(B2,3,0,":")-1/48,"hhmm-")&TEXT(REPLACE(B2,3,0,":")+1/48,"hhmm"),"")
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

If you have developed the udf to use in the worksheet then you could perhaps consider using native worksheet functions instead since they are generally more efficient than udfs?

Adapting my previous approach to allow for blanks and ETA, what about the formulas in columns G:H below?
Two formulas because I wasn't quite sure what you meant about "keeping ETA" - didn't know whether you meant in column B or the formula column.

Livin404_1.xlsm
BCDEFGH
1Text TimeHour RangeHour Range
211501120-12201120-1220
302250155-02550155-0255
4  
5ETAETA 
622252155-22552155-2255
723502320-00202320-0020
800012331-00312331-0031
Time range
Cell Formulas
RangeFormula
G2:G8G2=IFERROR(TEXT(1+REPLACE(B2,3,0,":")-1/48,"hhmm-")&TEXT(REPLACE(B2,3,0,":")+1/48,"hhmm"),B2&"")
H2:H8H2=IFERROR(TEXT(1+REPLACE(B2,3,0,":")-1/48,"hhmm-")&TEXT(REPLACE(B2,3,0,":")+1/48,"hhmm"),"")
I want to keep the ETA. The formula I have is fine. I already got the VBA to copy and paste cells values back into Column B, so just need to tweak the one formula to include ignore ETA. I will try yours the one that keeps ETA to see what happens. Thanks
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hey I just tried the formula you had in Column G and it works brilliant. Its a longer formula but the VBA is shorter thank you!! I was wondering how hard would it get your formula in worksheet "Inbound FIDS" Column H as a VBA? There are about 8 macros for that worksheet and one of the earlier ones calls for columns to be deleted, and I do not want the formula there until Column B is positioned from another Column. Your work continues to inspire me. Thank you so much.
 

Peter_SSs

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

ADVERTISEMENT

I already got the VBA to copy and paste cells values back into Column B
Somehow I missed that you were putting the values back into column B, though I now can see you said that right at the start. :oops:

So, yet another way if you want to try it, without using any helper columns or formulas on the worksheet.

VBA Code:
Sub Time_Range_v2()
  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 Sub

If you do want to stick with your udf and using a helper column in the worksheet, then just change your worksheet formula to

Excel Formula:
=IFERROR(GET30MINRANGE(B2),B2&"")
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
That last entry you made is by far the best, and it just saved me from 2-3 extra macros. How can I make it for worksheet ("Inbound FIDS")? Thank you!
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Yes, I have a Run_Fids Button which activates about six macros on my “72 Hr”worksheet sending it to “Inbound FIDS”. So I would need something to identify the sheet to be “Inbound FIDS”. It’s a huge project I’ve been working on for the past three weeks. I have about 26 macros to get me the “ 72 Hr” worksheet. Your input nearly completes everything. I just got one other macro that needs just a small change then I’m done. I’ll post that later. Thank you so much.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,117
Messages
5,622,821
Members
415,934
Latest member
adstocking

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