I have an excel sheet created by a 3rd party program.
One of the columns has dates in this format: "Jan 19, 2015 03:00:00 PM"
I would like these dates to appear in the following format: "19/01/2015"
I have selected the cell or cells, right clicked and selected "Format Cells...", chose "Date" in the category, then chose "14/03/2001" in the type, to no avail, the dates won't change.
I also tried "Custom" from the category and "dd/mm/yyyy" from the type, again, no changes at all.
The file is not protected, the sheet is editable.
Could someone explain what I could be doing wrong?
Regards Crouz
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.