Monday 16 March 2020

Excel : Convert ISO 8601 formatted timestamp into datetime

I was getting data from Web page API into Excel. The datetime retrieve was in format ISO 8601 with
Complete date plus hours, minutes and seconds with optional "Z":

         YYYY-MM-DDThh:mm:ssTZD (eg 2020-03-16T10:06:05Z)

Where:

YYYY = four-digit year
MM   = two-digit month (01=January, etc.)
DD   = two-digit day of month (01 through 31)
hh   = two digits of hour (00 through 23) (am/pm NOT allowed)
mm   = two digits of minute (00 through 59)
ss   = two digits of second (00 through 59)
Z    = special UTC designator ("Z") (optional)

Tried to format the datetime type but that might required split column and time zone convert.

Finally after did some testing and below is the outcome of excel formula that worked for me.


Excel Format to convert ISO DateTime to Local GMT +8

=DATEVALUE(MID(C2,1,10))+TIMEVALUE(MID(C2,12,8))+(8/24)