Creating a macro that converts a text-based timestamp into YYYY-MM-DD h:ss:mm format in Excel

I get data outputted in an excel sheet, where there is a timestamp of the time the data collection started. The timestamp is present in text form, in the form M/DD/YYYY hh:mm:ss.ms, for example 4/21/2023 16:03:41.791.

I would like to create a macro whereby this text-based timestamp is converted into one Excel can recognize, namely YYYY-MM-DD h:mm:ss. Output would be 2023-04-21 4:03:41 PM for the previous example.

Here is what I have so far

Sub ConvertTimestamp()
    Dim originalTimestamp As String
    Dim convertedTimestamp As String
    
    ' Specify the cell containing the original timestamp
    originalTimestamp = Range("O2").Value
    
    ' Convert the timestamp format
    convertedTimestamp = Format(CDate(originalTimestamp), "yyyy-mm-dd h:mm")
    
    ' Update the cell with the converted timestamp
    Range("O2").Value = convertedTimestamp
End Sub

However, I am getting some errors, namely runtime error 13: Type mismatch.

How should I modify my script to acheive my goal?

It's the milliseconds that cause you trouble. Try:

    ' Convert the timestamp format
    convertedTimestamp = Format(CDate(Left(originalTimestamp, Len(originalTimestamp) - 4)), "yyyy-mm-dd h:nn")

Convert Date as Text to Specific Format

• Note that the result of the function is a string that will be converted to a date when writing it to a cell but you also have to set the desired formatting of the cell.
• If you would want to use it as a UDF you would want to modify the function to return a date instead of a string.

The Function

<!-- language: lang-vb -->

' Convert 4/21/2023 16:03:41.791 to 2023-04-21 16:03:41.791
Function ConvertToMyDate( _
    ByVal DateString As String, _
    Optional ByVal OldDateDelimiter As String = "/", _
    Optional ByVal NewDateDelimiter As String = "-", _
    Optional ByVal DateTimeDelimiter As String = " ", _
    Optional ByVal MiliSecondsDelimiter As String = ".") _
As String
    On Error GoTo ClearError
    
    Dim Result As String

    Dim dPos As Long: dPos = InStr(DateString, DateTimeDelimiter)

    If dPos > 0 Then
        Dim OldDateStr As String: OldDateStr = Left(DateString, dPos - 1)
        Dim OldDate() As String:
        OldDate = Split(OldDateStr, OldDateDelimiter)
        Dim OldTimeStr As String: OldTimeStr = Right(DateString, _
            Len(DateString) - dPos - Len(DateTimeDelimiter) + 1)
        dPos = InStr(OldTimeStr, MiliSecondsDelimiter)
        Dim NewTimeStr As String
        If dPos > 0 Then
            NewTimeStr = Left(OldTimeStr, dPos - 1)
        Else
            NewTimeStr = OldTimeStr
        End If
        Result = OldDate(2) _
            & NewDateDelimiter & Format(CLng(OldDate(0)), "0#") _
            & NewDateDelimiter & OldDate(1) _
            & DateTimeDelimiter _
            & IIf(Len(NewTimeStr) > 0, NewTimeStr, "0:00:00")
    End If

    ConvertToMyDate = Result
    
ProcExit:
    Exit Function
ClearError:
    Debug.Print "Run-time error '" & Err.Number & "':" & vbLf & Err.Description
    Resume ProcExit
End Function

An Example

<!-- language: lang-vb -->

Sub Test()
    Sheet1.Range("B1").Value = ConvertToMyDate(Sheet1.Range("A1").Text)
End Sub

You can do this all in one line (without having to define extra variables):

Range("O2").NumberFormat = "yyyy-mm-dd h:mm"

Give that a try and let me know how it works for you!