Convert written date

[JamesBowdidge]

Hi,

I have an extract that spits out a date in this format...

Nov 6, 2018 9:59:35 AM

And I need it to be recognied as a "proper" date in excel..

Can anyone help?

thanks

J

 

[MARK858]

If you put in a cell does it convert to a date?

Just going out for a while so if it does convert try the formula below (I am sure someone will improve on it as I just threw it together).
You'll have to custom format it as mmm d, yyyy hh:mm:ss AM/PM if you want it to appear as per the original.

Excel Workbook
	A	B	C
1	Nov 6, 2018 9:59:35 AM		Nov 6, 2018 09:59:35 AM
2	Nov 16, 2018 9:59:35 AM		Nov 16, 2018 09:59:35 AM
Sheet3

 

[Scott Huish]

A bit shorter:
=--(MID(SUBSTITUTE(A1,","," "&LEFT(A1,3)&","),5,50))

 

[JamesBowdidge]

Yes it does, so I need to swap the month and day around to make excel recognise the date.. Hmmm

edit - didnt see the second pge with the posble answers.. will try now..

 

[JamesBowdidge]

Mark/Scott,

thanks so much, both are working and I con continue with my analysis now!!

Muh Appreciated!!

J