Skip to content

Instantly share code, notes, and snippets.

@adamhopkinson

adamhopkinson/gist:d239937c56b29cc1da2e

Last active April 5, 2019 08:58
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save adamhopkinson/d239937c56b29cc1da2e to your computer and use it in GitHub Desktop.
Save adamhopkinson/d239937c56b29cc1da2e to your computer and use it in GitHub Desktop.
Download ZIP
Converting a TSQL DateTimeOffset to UTC in Excel
Raw
gistfile1.txt
/*
* if C2 contains a DateTimeOffset eg 2015-08-13 09:31:12.0000000 +01:00
* parse the date and time out and add them together
* then subtract the hours offset (which - in Excel time storage is hours/24)
* note that this ignores the minute-part of an offset (eg Venezuela)
*/
=DATE(LEFT($C2,4), MID($C2,6,2),MID($C2,9,2)) + TIME(MID($C2,12,2),MID($C2,15,2),MID($C2,18,2)) + (MID($C2,29,3)/24)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment