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.


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.

  • Try something like this =--A1 where A1 is the cell with the date. Then format the new cell like you want. – Scott Craner Apr 25 '17 at 17:19
  • 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 Apr 26 '17 at 9:45
  • Highlight target cells
  • Right click and select Format Cells.

Then you can use this window to make the changesenter image description here

  • 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 Apr 25 '17 at 17:47
  • 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 Apr 25 '17 at 17:50
  • 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 Apr 26 '17 at 4:07
  • @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 Apr 26 '17 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.