convert text string to date/time format

Assuming that your string is coded YYYMMDD_HHMM for Year, Month, Day, Hour, Minute, then you can construct an Excel formula to generate a cell in date/time format.

If the string is in cell A1, then this formula ought to do it:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,10,2),RIGHT(A1,2),0)

You will need to format the cell properly to see the time portion. This is tested in LibreOffice calc, but Excel appears to have the same functions.

Assuming the whole format is YYYYMMDD_HHMM and that the first such value is in cell A1, you can use the following formula in B1:

=DATEVALUE(MID(A1,7,2) & "/" & MID(A1,5,2) & "/" & MID(A1,1,4)) + TIMEVALUE(MID(A1,10,2) & ":" & MID(A1,12,2))

This creates a date serial number based on DD/MM/YYYY using the MID function to gather each part, and the DATEVALUE function to return the serial number. It then adds to this the time serial number which uses the MID function in a similar way to gather the hours and minutes.

You can then format the cell as a Date format, Time format, or a custom format of say dd/mm/yyyy hh:mm to see the end result. Just right click the cell, choose Format Cells then configure similar to the screenshot below:

Here's a screenshot of the end result:

I had the same problem and I did just one simple thing: =R1*1 in S1, with format selected as Time, and it worked. No complicated code or anything else was needed.