Parsing an ISO8601 date/time (including TimeZone) in Excel

Question

I need to parse an ISO8601 date/time format with an included timezone (from an external source) in Excel/VBA, to a normal Excel Date. As far as I can tell, Excel XP (which is what we're using) doesn't have a routine for that built-in, so I guess I'm looking at a custom VBA function for the parsing.

ISO8601 datetimes look like one of these:

2011-01-01
2011-01-01T12:00:00Z
2011-01-01T12:00:00+05:00
2011-01-01T12:00:00-05:00
2011-01-01T12:00:00.05381+05:00

Answer 1

There is a (reasonably) simple way to parse an ISO timestamp WITHOUT the time zone using formulas instead of macros. This is not exactly what the original poster has asked, but I found this question when trying to parse ISO timestamps in Excel and found this solution useful, so I thought I would share it here.

The following formula will parse an ISO timestamp, again WITHOUT the time zone:

=DATEVALUE(MID(A1,1,10))+TIMEVALUE(MID(A1,12,8))

This will produce the date in floating point format, which you can then format as a date using normal Excel formats.