excel date formatting not working

Given your regional settings (UK), and the inability of formatting to change the date, your date-time string is text. The following formula will convert the date part to a "real" date, and you can then apply the formatting you wish:

=DATE(MID(A1,FIND(",",A1)+1,5),MATCH(LEFT(A1,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),MID(SUBSTITUTE(A1,",","   "),5,5))

Might be able to simplify a bit with more information as to the input format, but the above should work fine. Also, if you need to retain the Time portion, merely append:

+RIGHT(A1,11)

to the above formula.

The following worked for me:

• Select the date column.
• Go to the Data-tab and choose "Text to Columns".
• On the first screen, leave radio button on "delimited" and click Next.
• Unselect any delimiter boxes (everything blank) and click Next.
• Under column data format choose Date
• Click Finish.

Now you got date values