Converting dates to regular Excel-compataible values

I’m downloading sales files from an ecommerce partner.
The partner is kicking out dates and times for my sales in the format:
2013-01-13T05:33:38+00:00
2012-11-30T22:46:35+00:00

For, respectively, January 13, 2013 5:33 AM and November 11, 2012 10:46 PM.
I’ve got tens of thousands of these transactions per year that I’m trying to analyze in a spreadsheet.
My life would be much simpler if I could put these into a format that MS Excel would recognize as a Date/Time.
How do I get these formatted the way I want?

There may well be a more elegant solution, but whenever I import text files (and that’s most days) I always go back to basics; segment the text, convert the text into numbers, combine the numbers into date:time

So if the text string is in column A:

Col B (Year) =VALUE(LEFT(A3,4))
Col C (Month) =VALUE(MID(A3,6,2))
Col D (Day) '=MID(A3,9,2)
Col D (Excel date) =DATE(B3,C3,D3)
Col E (Excel time) =VALUE(MID(A3,12,8))

Do it all in a single formula if that suits you,. I find the way above easier to debug if there are any inconsistencies in the text string format.

Assuming these are in column A, the simplest way I’ve found is to insert a column B and then use the formula in B1 =LEFT(A1,10)+0. This will put the date made up from the first 10 characters in A1 as a date into B1. The +0 is just a simple way to covert the text into a value.

You can then format the B column in whatever date format you wish.
Copy this formula into all of B and you have all of A as dates in B.
You can then if you wish copy the entire column B and use “alt-e special values” to paste the values into column B replacing the formulas.

ETA: Penultimate thule’s reply wasn’t here when I started, Just wanted to say that =Value() does the same thing as adding zero and is probably nicer as it’s obvious what it’s doing. I just find adding 0 faster to type.

Assuming that A is the column containing the original text, format another column in the desired date format, then put


=VALUE(LEFT(A1, 10) & " " & MID(A1, 12, 8))

in the first cell and auto-fill the rest of the new column with the formula.

You could do this with a one line sed or perl script that would preprocess your input file to give you rational (to Excel) time and date format. I’m sure that you could write a VBA script within Excel that could do the same thing, but I haven’t had to touch VBA in a very long time (and never will again if I can get away with it).

Personally I think adding extra columns to your spreadsheet just to process the data is kludgy, but then I find Excel’s normal operation to be pretty kludgy as well.

Stranger

Can Excel really not handle those? That’s surprising because they are ISO 8601 time values. That’s about as standard as it gets.

If you parse them yourself, keep in mind that the part after the ‘+’ is the time zone information. The ones you listed are in UTC (essentially what used to be known as GMT.)

Kellner,

You get points for the right answer.
Not sure what I was doing wrong before, but I changed the data type on that field to the second to last entry under TIME in the FORMAT CELL dialog in Excel and they’re now acting just like they should.