Trying to convert Date Format - Wed Sep 26 2012 05:03:42 GMT+0000 (UTC) to 26/09/2012

Hi All,

I have tried most of the standard function on Excel but cannot change the format of this cell.
I have looked through this forum for the answer and have found some formulas that are similar but as I am not that good at coding I cannot make them work with my cells.

If anyone can help that would be great.
I have attached the spreadsheet.

Hi shg, thanks for the quick reply but unfortunately I get a #VALUE error
thoughts?

Originally Posted by shg

In B1 and copy down,

=--SUBSTITUTE(MID(A1, 5, 20), " ", ", ", 2)

... and format as you like.

What i did

Text to column>fixed width> segregated only date section to be in 2nd column>selected 2nd column to be MDY

format to whatever you like after that

that formula does not work for me either

i works when i take out the -- at the front and then do the text to column to convert MDY to DMY

Hi All FIXED.

I used =SUBSTITUTE(MID(A1, 5, 11), " ", ", ", 2)
and I got Sep 26, 2012 (good enough for what I need.

Thanks shg and humdingaling.

BTW for beginners like me - the 5 represent where you would like to start from and the 11 represent were you want to finish.

Originally Posted by BEEGINA

Hi shg, thanks for the quick reply but unfortunately I get a #VALUE error
thoughts?