1
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.
oekstrem
Posted 2013-12-05T10:56:16.007
Reputation: 121
1
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).
oekstrem
Posted 2013-12-05T10:56:16.007
Reputation: 121
0
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.
Tomkarho
Posted 2013-12-05T10:56:16.007
Reputation: 231
0
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!
barry houdini
Posted 2013-12-05T10:56:16.007
Reputation: 10 434
This formula gives me an error - Maybe it's because I've got Norwegian/European regional settings. – oekstrem – 2014-01-10T07:41:39.700
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 – 2013-12-05T11:01:16.980
It's just text. – oekstrem – 2014-01-08T08:38:29.027