Skip to content

Instantly share code, notes, and snippets.

@edonosotti
Created March 19, 2018 12:04
Show Gist options
  • Save edonosotti/c079a92d05dc6b6ecf2a56e0a6b79f13 to your computer and use it in GitHub Desktop.
Save edonosotti/c079a92d05dc6b6ecf2a56e0a6b79f13 to your computer and use it in GitHub Desktop.
Microsoft Excel formula to convert ISO 8601 date with milliseconds resolution to time stamp

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.

@andreaskueffel
Copy link

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))

@edwardaux
Copy link

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))

@edonosotti
Copy link
Author

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

@owenduffy
Copy link

The "+TIMEVALUE" should be -TIMEVALUE.

Owen

@piyushsoni
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment