How to convert Jan 1 2011 1:23PM text to date/time format

[jondugenia]

Good day,

I have a excel worksheet with text entries such as...

Jan 1 2011 1:23PM
Jan 1 2011 1:35PM
Jan 1 2011 3:05PM

I'd like to know/learn how to convert this to date/time entries since my worksheet data spans 60,000++ lines.

I tried using format cell function but it did not work and apparently due to the non-comma after the date and the non-space between the time and AM/PM is not helping the conversion.

Hope you can send me advise on this...

Thanks and More Power!!!

Jon D

 

[twinetwstr]

multiply the text by 1.

It should convert any normal date time string into Excel Serial Time format..

Make sure the cells are not formatted as text, they should be formatted as General or Date.

Easy way to multiply a column of dates by 1:

Put 1 in an empty cell and COPY it

Highlight the column of text you want to convert

Right-click and select "Paste Special" > "Paste Special" > Multiply in 2007 or 2010
or just Paste Special and Multiply in 2003.

 

[T. Valko]

Good day,

I have a excel worksheet with text entries such as...

Jan 1 2011 1:23PM
Jan 1 2011 1:35PM
Jan 1 2011 3:05PM

I'd like to know/learn how to convert this to date/time entries since my worksheet data spans 60,000++ lines.

I tried using format cell function but it did not work and apparently due to the non-comma after the date and the non-space between the time and AM/PM is not helping the conversion.

Hope you can send me advise on this...

Thanks and More Power!!!

Jon D

This worked for me.

Do Edit>Replace

Find what: PM or AM
Replace with: [space]PM or [space]AM
Replace All

Find what: [space]2011
Replace with: , 2011
Replace All

Of course, you'd have to do this for each different year.

Someone may come along and post a macro to do it. If not, the Edit>Replace should do it.

 

[twinetwstr]

Oops, I tried it out, The issue is the lack of the comma after the day, and the lack of a space before the PM.

You can use the Ctrl H Replace tool to fix both those errors, or the Function

Substitute() to replace "PM" with " PM" and " 20" with ", 20" and it should work fine after that.

 

[Rick Rothstein]

You can use this formula to create your dates...

=--REPLACE(REPLACE(F1,LEN(F1)-1,0," "),FIND(" ",F1,5),1,", ")

and the you can Copy/PasteSpecial their Values over top of your original data.

 

[jondugenia]

Mega-thanks Rick Rothstein, twinetwstr, T. Valko...
i'll try your respective inputs...

More Power to Y'All...

 

[T. Valko]

Mega-thanks Rick Rothstein, twinetwstr, T. Valko...
i'll try your respective inputs...

More Power to Y'All...

You're welcome. Thanks for the feedback!

 

[jondugenia]

good day Rick Rothstein, twinetwstr, T. Valko...

It works!!!

mega-thanks for the assist...

pardon late reply, my pc was out...

More Power to Y'all...