Convert a date like this to MM/DD/YYYY Sep 7 2015 8:23PM

I need to convert about 100,000 date with this format Sep 7 2015 8:23PM to mm/dd/yyyy. Can you please help?

Thank you
 
Convert to what? What are your regional settings? Is this a real rate/time or text looking like it ?
Can you post a sample sheet ( no pics) ?
 
maybe something like....

=0+TRIM(REPLACE(REPLACE(A4,LEN(A4)-6,99,""),7,0,", "))

this would be dependent on the 3 letter month abbreviation
 
the formula in Post 5 will convert your text string to a date.
you would just need to format the cell as such
 
I need to convert Sep 7 2015 8:23PM to 08/07/2015
If text string in A1 then try
Code:
=CHOOSE(MATCH(MID(A1,1,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Nov","Dec"},0),1,2,3,4,5,6,7,8,9,10,11,12)&"/"&VALUE(MID(A1,FIND(" ",A1,1),3))&"/"&VALUE(MID(A1,7,5))
or
Code:
=DATE(VALUE(MID(A1,7,5)),CHOOSE(MATCH(MID(A1,1,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Nov","Dec"},0),1,2,3,4,5,6,7,8,9,10,11,12),MID(A1,4,3))
 
Back
Top