Use time in hh:mm AM/PM format from imported .txt with VBA

Isacri05

You haven't attached a  file so I can't see what form the dates are but you say they are "d/mm/yyyy hh:mm:ss" and you want (just) "h:mm:ss AM/PM". You can do by using VBA's CDate function to convert the text to a date/time (number) then format that number as text using VBA's Format function. You're left with a text string line "6:09:51 AM".

To do that, replace the code section below:

        For Each r In Selection
            v = r.Text
            r.Clear
            r.NumberFormat = "dd/mm/yyyy hh:mm:ss"
            r.Value = DateSerial(Mid(v, 7, 4), Mid(v, 4, 2), Left(v, 2)) + TimeSerial(Mid(v, 12, 2), Mid(v, 15, 2), Right(v, 2))
        Next r

with:

        For Each r In Selection
            r.Value = Format(CDate(r.Text), "h:mm:ss AM/PM")
        Next r

To retain the date/time number (e.g. for time calculations) but see only the time portion, use this instead:

        For Each r In Selection
            r.Value = CDate(r.Text)
            r.NumberFormat = "h:mm:ss AM/PM"
        Next r

Note that you shouldn't turn the speed-up measures (Application.ScreenUpdating,  .Calculation and .EnableEvents) on and off several times in your macro- just turn them off at the start then back on as you exit the sub. Very often they're not needed (e.g. for small data sets like yours). Fior more information, see Don's tutorial here: Guide to Making Your Macro Run Faster and Better in Excel

Revision 05 May 2023:

In the attached  revised version of the user's file, the following code will import both types of (embedded) text files and display dates aas AM/PM and number values as decimals. It's commented for your guidance:

Sub import_txt()

    Dim command As Variant
    Dim fileFilterPattern As String
    Dim n As Long

    On Error Resume Next

    fileFilterPattern = "Text Files (*.txt; *.csv),*.txt;*.csv"
    command = Application.GetOpenFilename(fileFilterPattern)

    If command = False Then
        MsgBox "No selection."
        Exit Sub
    End If

    Application.ScreenUpdating = False

    Open command For Input As #1
    ' ignore first line
    Line Input #1, command
    ' loop through lines
    Do Until EOF(1)
        Line Input #1, command
        ' check it's not empty
        If Len(command) > 0 Then
            ' if line is comma-separated, replace with tabs
            If InStr(command, ",") > 0 Then command = Replace(command, ",", Chr(9))
            ' create an array from tab-separated string
            command = Split(command, Chr(9))
            ' write to cells
            ActiveCell.Offset(n, 0).Resize(1, UBound(command) + 1).Value = command
            ' for date/ (second column)
            With ActiveCell.Offset(n, 1)
                ' convert to date and format display
                .Value = CDate(.Text)
                .NumberFormat = "h:mm:ss AM/PM"
                ' if 3 columns imp0rted...
                If UBound(command) = 2 Then
                ' ... convert next column along to a decimal and display to 3dp
                    .Offset(0, 1).Value = CDec(.Offset(0, 1).Value)
                    .Offset(0, 1).NumberFormat = "#,##0.000"
                End If
            End With
        End If

        n = n + 1
    Loop

    Close #1

    Application.ScreenUpdating = True

End Sub

To use it, select an empty cell with rows to the right and below empty then run the macro (selecting one of the sample files which should be saved out previously).

In the addtional sheet "TRIAL" there are examples of the imported data for each type.

REVISION 06 MAY 2023:

Seems the user really wants to use values already imported (as text) in worksheet "import text"  and show them in formatted cells in "extract time" without converting the values.

One way to do that is to use Excel's TIMEVALUE function and point to the imported text strings...

In the second attached file, cells A2:B12 in sheet "extract time" are already formatted to display as "h:mm:ss AM/PM" so TIMEVALUE works to convert time string, e.g. in cell A2 uses the formula

=TIMEVALUE('import text'!B3)

(and likewise down) and works for date/time strings, e.g. in B2

=TIMEVALUE('import text'!F3)

Hope this helps. If so,  please remember to return and mark this Answer as Selected.