Convert "mmmm d, yyyy" format into "dd/mm/yyyy" format with Excel

I have a bunch of data with strings of the format "mmmm d, yyyy", (for example: December 7, 2015 , or January 10, 2016). I want to convert these strings to dates with the format: dd/mm/yyyy (for example: 20/04/2012). However, I have not been able to find a way how to do this.

Your help is much appreciated.

data

Edit: I figured it out

My computer date time settings (windows 7) were European, whilst the date format "mmmm d, yyyy" is American. Basically what I did was change the operating system long date to be "mmmm d, yyyy" after which excel recognized the dates.

Here's a link if you do not know how to change the settings: http://www.pcadvisor.co.uk/how-to/software/how-to-change-the-date-and-time-on-your-pc-3267622/ look at step three.

D. Drako

Posted 2017-04-25T17:14:27.800

Reputation: 11

Try something like this =--A1 where A1 is the cell with the date. Then format the new cell like you want. – Scott Craner – 2017-04-25T17:19:22.920

Good find! You should post a self answer to explain how you solved this (instead of editing it into your question). – I say Reinstate Monica – 2017-04-26T09:45:35.423

Answers

Highlight target cells
Right click and select Format Cells.

Then you can use this window to make the changes

Sorean

Posted 2017-04-25T17:14:27.800

Reputation: 668

I tried it on a computer with excel 2013 and this worked. However on excel 2010 it doesn't. Do you maybe know another method to make it work on excel 2010? – D. Drako – 2017-04-25T17:47:38.407

That doesn't make sense, the same window is in 2010. I have it there as well. Alternatively, since you have access to a 2013 version. Open the file there, make the changes, go back to your 2010 machine. – Sorean – 2017-04-25T17:50:08.290

1@D.Drako Are you sure the underlying data in the cells is excel date/time data? If you format the cell as a number it should be a 5-digit number, possibly with a decimal point. The formatting suggested in this answer won't work if the data in your cells isn't of this type. Further, it can't begin with a leading ' character. – I say Reinstate Monica – 2017-04-26T04:07:53.180

@Twisty no it is not, and does not display as a date. I uploaded a picture of the format on the main post – D. Drako – 2017-04-26T08:52:36.577