Excel Formula for Converting ISO-8601 Timestamp to Excel Date

When KDXplore or KDSmart use a Timestamp for a scored Trait value the timestamp is recorded in ISO-8601 format. Using this format means that you can correctly compare data collected in different timezones.

Unfortunately Microsoft Excel does not automatically recognize this format. However, using the formula below, you can easily convert the date/time string in ISO-8601 format into an Excel date/time value and, by using an appropriate cell format (e.g. yyyy-mm-dd hh:mm:ss) render this in a more useful form.

The formula is:

	=DATEVALUE(LEFT(A1,10))
	+ TIMEVALUE(MID(A1,12,8))
	+ IF("Z"=MID(A1,20,LEN(A1)-19),0,(INT(MID(A1,20,LEN(A1)-19)/100)*60+MOD(MID(A1,20,LEN(A1)-19),100))/1440)

If you wish to know what the formula is doing, the explanation is below. The formula above is split across three lines to faciliate the following explanation.

Excel's internal date time format is a "real" number where the part before the decimal point is the number of days and the portion after the decimal point is the "fraction" of a day.

The first line of the formula extracts the year/month/day portion and converts it into the "full days" portion of the result
The second part of the formula extracts the hours/minutes/seconds portion and converts it into the "day fraction" portion.
The third and final part obtains the "timezone" portion of the ISO-8601 timestamp and converts it into another "day fraction" part. It checks for the special "Z" timezone that indicates a zero offset. If it is not "Z" the timezone is in ±HHMM format so the formula extracts the HH portion separate from the MM portion, computes the total as a number of minutes and divides by 1440 (the number of minutes in a day) to arrive at a final "day fraction" component that is added to the amount determined by the first two parts of the formula. Any leading plus or minus sign is used to correctly perform this computation.