Need help converting date/time from csv presented as "December 21st 2020 6:03 am", etc.

[Locomikey5150]

Hello,

First post so bare with...

I was given a csv file with date/time presented as "December 21st 2020 6:03:00 am", etc. I can't figure out how to convert to a format Excel recognizes for pivot tables and such. All months and days are in the file, along with times in am and pm.

Does anyone know what I need to do here. Thanks in advance for your help!

Michael

[alansidman]

Please read the yellow banner at the top of this page on how to attach a file.

[Locomikey5150]

If this is a duplicate reply, my apologies (still learning). File is now attached, and thank you.

Michael

[p45cal]

There's those pesky sts after 21; I imagine there are some nds and rds and ths too.
If you remove those first with find & replace, then you'll be able to do a Text-to-Columns, splitting the date/times at the end of the date part, and choosing Dates:DMY for the first column and General for the second. Then you can add them back again into a single column of proper Excel Date/Time stamps.
It would probably be safer to use Power Query to import directly from the csv files into Excel.

Attach a sample csv file (preferably not an Excel file saved as a csv)

Incidentally, what do those date and time stamps look like when there is only one digit for the day of the month?

[Locomikey5150]

Thanks, I'll give that a whirl. About to head out for a bit, but will give it a shot and report back once complete. Really appreciate the assist!

[Locomikey5150]

This did the trick, thank you! I really appreciate the assist.