In VBA, trying to replace all times (in a string) with another time, ie: all xxxxCST to (xxxx 0100-CodePudding

I have an excel sheet full of times in CST times where a single cell contains xxxxCST-xxxxCST (for example 1000-1100CST). I want to use VBA to replace all times in CST with EST, thus adding 1 hour. In the above example that would be 1100-1200EST (I'm using 24 time format).

I thought this would be a find and replace type solution, and have tried using excel wild cards where ? is any one character so that I find anything with four characters and CST (????CST) to replace it with something, but cannot figure out how to add the 0100 hr since the ????CST I think is a string and I would have to find the CST item first, separate the numerical part, then add the 0100 then recombine, etc. I may be going about this the wrong way, but ultimately would like to replace all xxxxCST to (xxxx 0100)EST and similarly if this was in a word document rather than excel replace all xxxxCST to (xxxx 0100)EST.

Thank you in advance.

CodePudding user response：

Your thought process is correct. You need to pull the string apart, make needed adjustments and then recombine. Here is some code to illustrate how this would be done:

Option Explicit

Private Sub Command1_Click()
   Debug.Print Add1Hour("1000-1100CST")
End Sub

Private Function Add1Hour(ByVal Time As String) As String
   Dim pieces As Variant
   Dim startTime As Integer
   Dim endTime As Integer
   
   pieces = Split(Time, "-")
   startTime = CInt(pieces(0))   100
   endTime = CInt(Left(pieces(1), 4))   100
   Add1Hour = startTime & "-" & endTime & "EST"
End Function

CodePudding user response：

You could handle the value as true time values:

CstTime = "1000-1100CST"
Parts = Split(CstTime, "-")

StartTime = DateAdd("h", 1, TimeValue(Format(Val(Parts(0)), "00\:00")))
' 11:00:00 
EndTime = DateAdd("h", 1, TimeValue(Format(Val(Parts(1)), "00\:00")))
' 12:00:00 

EstTime = Format(StartTime, "hhnn\-") & Format(EndTime, "hhnn\E\S\T")
' 1100-1200EST