Convert Text Date/Timestamp to Recognizable date?

I have a data report that has a date/timestamp like this "Sep 11 2021 5:45AM" and excel doesn't recognize this as a date and timestamp. How can I quickly convert this column to be a proper date/timestamp?

Originally Posted by 6StringJazzer

Find & Replace all

[space]2021
to
,[space]2021

and

AM
to
[space]AM

and

PM
to
[space]PM

Where I used [space] is just a space. I spelled it out to make it clearer what is there.

I didn't think to try that, it's 800k rows soooo it might be a little slow but gonna give it a shot.

Originally Posted by NewYears1978

I didn't think to try that, it's 800k rows soooo it might be a little slow but gonna give it a shot.

I am not sure if you were thinking 6StringJazzer was suggesting something else for you to do but his suggestion was for you to use Excel's Find/Replace dialog box (CTRL+H) to do the two replacements (one at a time of course)... that should not be all that slow.

As a side-note, be sure you don't have other text within the sheet that might contain the letters "am" in the text that would be affected by find and replace. That would certainly be annoying to have to fix! haha

If that is a possibility for the OP, then he should click the "Options>>" button in the dialog and put a checkmark in the Match case" checkbox.

Since the OP lives in the US, this formula should work...

Formula:

Please Login or Register  to view this content.

Originally Posted by 6StringJazzer

Sounds like these are all in one column so you can select the column first, then the Find & Replace will apply only to that column.

This is the solution I used.

I have to do this task at the start of every report so it's kind of tedious but not that big of deal, I will probably write macros to do this.

Originally Posted by 6StringJazzer

Where is this data coming from in the first place? Is it possible to address it at the source?

SQL database and I don't have access to it, but I did get with someone to change the queries to fix this formatting so this should be fixed soon =D For now the find replace worked well and got my project finished =D

Appreciate ya!