Change Date String and Time String in a String

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
421
Office Version
  1. 2019
Platform
  1. Windows
Thanks in advance for your assistance.

What would be the VBA code to replace the date and time in a string if they do not agree with what is in either the date column or string column.

Date: This is what appears because of the way I formatted it, but it’s still a date as when I look in the cell function box, it shows “7/20/2022”
Range(“A9”) = 2022-07-20, Wed

Time: This is what appears because of the way I formatted it and it has been changed from a time to a string.
Range(“B9”) = 02:09 PM EST

String
Range(“C9”) = James turned in assignment in on 2022-07-20, Wed @ 02:09 PM EST – It was early.


Example 1: Now say let’s say I update the date and time to:
Range(“A9”) = 2022-07-21, Thu
Range(“B9”) = 08:01 AM EST

I would like to change the String from:
Range(“C9”) = James turned in assignment in on 2022-07-20, Wed @ 02:09 PM EST – It was early.

To:
Range(“C9”) = James turned in assignment in on 2022-07-21, Thu @ 08:01 AM EST – It was early.


Example 2: Now let’s say I just update the date:
Range(“A9”) = 2022-07-21, Thu

I would like to change the String from:
Range(“C9”) = James turned in assignment in on 2022-07-20, Wed @ 02:09 PM EST – It was early.

To:
Range(“C9”) = James turned in assignment in on 2022-07-21, Thu @ 02:09 PM EST – It was early.


Example 3: Now let’s say I just update the time:
Range(“B9”) = 08:01 AM EST

I would like to change the String from:
Range(“C9”) = James turned in assignment in on 2022-07-20, Wed @ 02:09 PM EST – It was early.

To:
Range(“C9”) = James turned in assignment in on 2022-07-20, Wed @ 08:01 AM EST – It was early.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Thanks in advance for your assistance.

What would be the VBA code to replace the date and time in a string if they do not agree with what is in either the date column or string column.

Date: This is what appears because of the way I formatted it, but it’s still a date as when I look in the cell function box, it shows “7/20/2022”
Range(“A9”) = 2022-07-20, Wed

Time: This is what appears because of the way I formatted it and it has been changed from a time to a string.
Range(“B9”) = 02:09 PM EST

String
Range(“C9”) = James turned in assignment in on 2022-07-20, Wed @ 02:09 PM EST – It was early.


Example 1: Now say let’s say I update the date and time to:
Range(“A9”) = 2022-07-21, Thu
Range(“B9”) = 08:01 AM EST

I would like to change the String from:
Range(“C9”) = James turned in assignment in on 2022-07-20, Wed @ 02:09 PM EST – It was early.

To:
Range(“C9”) = James turned in assignment in on 2022-07-21, Thu @ 08:01 AM EST – It was early.


Example 2: Now let’s say I just update the date:
Range(“A9”) = 2022-07-21, Thu

I would like to change the String from:
Range(“C9”) = James turned in assignment in on 2022-07-20, Wed @ 02:09 PM EST – It was early.

To:
Range(“C9”) = James turned in assignment in on 2022-07-21, Thu @ 02:09 PM EST – It was early.


Example 3: Now let’s say I just update the time:
Range(“B9”) = 08:01 AM EST

I would like to change the String from:
Range(“C9”) = James turned in assignment in on 2022-07-20, Wed @ 02:09 PM EST – It was early.

To:
Range(“C9”) = James turned in assignment in on 2022-07-20, Wed @ 08:01 AM EST – It was early.

Update. After some more google searches I found some code that seems to work for me.


VBA Code:
Option Explicit

Sub ExtractRRWells()

Dim NwsorSum As String
Dim DateofEvent As Variant
Dim TimeofEvent As Variant

Dim Str1 As String, Str2 As String

Dim i As Long

    
For i = 1353 To 1355
    DateofEvent = Range("I" & i)
    DateofEvent = Format(DateofEvent, "YYYY-MM-DD, DDD")

    TimeofEvent = Range("J" & i)

    NwsorSum = Range("L" & i)


    'SuperMid(ByVal strMain As String, str1 As String, str2 As String, Optional reverse As Boolean) As String

    Str1 = SuperMid(NwsorSum, "Float) ", " @")
    Str2 = SuperMid(NwsorSum, "@ ", " -")
    
    If DateofEvent <> Str1 Then
        NwsorSum = Replace(NwsorSum, Str1, DateofEvent)
        Range("L" & i) = NwsorSum
    End If
    
    
    If TimeofEvent <> Str2 Then
        NwsorSum = Replace(NwsorSum, Str2, TimeofEvent)
        Range("L" & i) = NwsorSum
    End If

Next i

End Sub


Public Function SuperMid(ByVal strMain As String, Str1 As String, Str2 As String, Optional reverse As Boolean) As String
'Obtained courtesy of Ryan Wells
'Website: https://wellsr.com/vba/2016/excel/easily-extract-text-between-two-strings-with-vba/
'DESCRIPTION: Extract the portion of a string between the two substrings defined in str1 and str2.
'DEVELOPER: Ryan Wells (wellsr.com)
'HOW TO USE: - Pass the argument your main string and the 2 strings you want to find in the main string.
' - This function will extract the values between the end of your first string and the beginning
' of your next string.
' - If the optional boolean "reverse" is true, an InStrRev search will occur to find the last
' instance of the substrings in your main string.
Dim i As Integer, j As Integer, temp As Variant
On Error GoTo errhandler:
If reverse = True Then
    i = InStrRev(strMain, Str1)
    j = InStrRev(strMain, Str2)
    If Abs(j - i) < Len(Str1) Then j = InStrRev(strMain, Str2, i)
    If i = j Then 'try to search 2nd half of string for unique match
        j = InStrRev(strMain, Str2, i - 1)
    End If
Else
    i = InStr(1, strMain, Str1)
    j = InStr(1, strMain, Str2)
    If Abs(j - i) < Len(Str1) Then j = InStr(i + Len(Str1), strMain, Str2)
    If i = j Then 'try to search 2nd half of string for unique match
        j = InStr(i + 1, strMain, Str2)
    End If
End If
If i = 0 And j = 0 Then GoTo errhandler:
If j = 0 Then j = Len(strMain) + Len(Str2) 'just to make it arbitrarily large
If i = 0 Then i = Len(strMain) + Len(Str1) 'just to make it arbitrarily large
If i > j And j <> 0 Then 'swap order
    temp = j
    j = i
    i = temp
    temp = Str2
    Str2 = Str1
    Str1 = temp
End If
i = i + Len(Str1)
SuperMid = Mid(strMain, i, j - i)
Exit Function
errhandler:
MsgBox "Error extracting strings. Check your input" & vbNewLine & vbNewLine & "Aborting", , "Strings not found"
End
End Function
 
Upvote 0
Solution

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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
Back
Top