Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 at 8:38

3 Answers 3

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

=TRIM(REPLACE(LEFT(A2,FIND("+",A2)-1),1,FIND(",",A2),""))+0

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 at 7:41

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

=LEFT(REPLACE(G9;1;(FIND(",";G9));"");18)

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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