Excel formula to convert ISO 8601 formatted timestamp into Excel datetime

Assume, you have a spreadsheet having in A column timestamps formatted in ISO 8601 manner, like "2011-11-23T14:00:00Z" (you may ignore the Z character) for now.

If you need to get Excel timestamps from it, you have to do some conversion, (Excel is not doing this work automatically for me).

So having the value in cell A1 "2011-11-23T14:00:00Z",  use following formula:
=DATUMHODN(MIDB(A1;1;10))+ČASHODN(MIDB(A1;12;8))

(this was Chech Excell)

=DATEVALUE(MIDB(A1;1;10))+TIMEVALUE(MIDB(A1;12;8))
and this for English speaking one.

It is parsing part of the string and it is possible, that with some locale settings, this could make results wrong (I did not find this problem, just estimating possible problems). In such a case, a bit more talkative, but also more explicit version can be used

=DATUM(MIDB(A1;1;4);MIDB(A1;6;2);MIDB(A1;9;2))+ČAS(MIDB(A1;12;2);MIDB(A1;15;2);MIDB(A1;18;2))

or English variant:


=DATE(MIDB(A1;1;4);MIDB(A1;6;2);MIDB(A1;9;2))+TIME(MIDB(A1;12;2);MIDB(A1;15;2);MIDB(A1;18;2))

As the result is in all cases a number, do not forget to set proper cell format for this value.

Note, that this is ignoring the timezone information and fractions of seconds..