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)
YYYY = four-digit yearMM = 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.