Skip to content

Instantly share code, notes, and snippets.

@edonosotti

edonosotti/formula.md

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.
Download ZIP
Microsoft Excel formula to convert ISO 8601 date with milliseconds resolution to time stamp
Raw
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.

@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