Microsoft Excel formula to convert ISO 8601 date with milliseconds resolution to time stamp

formula.md

Formula

This will convert an ISO 8601 date such as: 2018-03-19T10:18:08.137747874Z

to a timestamp with milliseconds resolution: 1521454688137,00

Please note that the decimal part (,00) can be stripped away, milliseconds are the last three digits.

=(((DATEVALUE(LEFT(A1;10))+TIMEVALUE(MID(A1;12;8)))-DATE(1970;1;1))*86400000)+VALUE(MID(A1;FIND(".";A1)+1;3))

Replace A1 with the actual column name.

Thanks, works great!

In German Excel we need to replace the formula identifiers:

=(((DATWERT(LINKS(A1;10))+ZEITWERT(TEIL(A1;12;8)))-DATUM(1970;1;1))*86400000)+WERT(TEIL(A1;FINDEN(".";A1)+1;3))

Thanks very much for this.

Just in case anyone happens to stumble across this, I kept getting errors from Excel using the formula... eventually, I realised it is because, for me, the param separator in Excel is a comma, not a semi-colon.

The fix was simple enough... replace semi-colons with commas

=(((DATEVALUE(LEFT(A1,10))+TIMEVALUE(MID(A1,12,8)))-DATE(1970,1,1))*86400000)+VALUE(MID(A1,FIND(".",A1)+1,3))

@edwardaux @andreaskueffel thank you for providing the localized versions.

The "+TIMEVALUE" should be -TIMEVALUE.

Owen

@edwardaux : Thanks so much for posting the fix for our version of Excel ! :)