Livin404
Well-known Member
- Joined
- Jan 7, 2019
- Messages
- 743
- Office Version
- 365
- 2019
- Platform
- 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 ”
” and that gave me 11:50. For Column I which now I’ll use Column H as my source I used the formula “
” 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 “
” to get value 12:20. In Column K I used “
” 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 “
” 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,
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))
Excel Formula:
=MOD(H2-0.5/24,1)
Excel Formula:
=(H2+0.5/24)
Excel Formula:
=TEXT(I2,"hh:mm")&"-"&TEXT(J2,"hh:mm")
Excel Formula:
=SUBSTITUTE(K2,":","")