Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How can I make Excel recognize a text in the following format as a date and time?

I have the following date in on cell: Tuesday, December 3, 2013 1:54:26 PM +01:00

And would like Excel to format the cell as a date, so that I can create charts based on the date.

share|improve this question
Is that just text format, or actual date/time formatted text? If its pure text, then you need to use some formula to grab the text that you care (December 3, 2013) and then convert that into date. If it is already date/time format, you can just change the format into Date only and work from there. – Darius Dec 5 '13 at 11:01
It's just text. – Børge Jan 8 '14 at 8:38

Try setting the cell type to date and see if Excel recognizes the format. Then just customize the cell format to whatever date you wish it to display.

share|improve this answer

If your text is in A2 use this formula in B2


That should give you a number which represents the date/time serial number (days since 1 Jan 1900), simply format B2 in required date time format, e.g.

m/d/yyyy hh:mm

I'm assuming you have US regional settings, if not formula may not work!

share|improve this answer
This formula gives me an error - Maybe it's because I've got Norwegian/European regional settings. – Børge Jan 10 '14 at 7:41

I managed to fix this, but the solution is not perfect. Here it goes:


I'm happy with the replace-part which gets rid of the day of week, but the LEFT I use to get rid of the time is leaving me with an extra space in the end when the time is 3 digits in stead of 4 (9:00 vs 11:00).

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .