I have a column that is text formatted like
20110408_2041. How can I format it 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:
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
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:
You can do that by writing a small script (with C# for example) that reads the strings from the column converts them to date time format and then saves them back to the Excel column.
Sorry, I don't know if you can do that directly from Excel.