Skip to content

Instantly share code, notes, and snippets.

View maxpower720's full-sized avatar

Max Weber maxpower720

View GitHub Profile
@adamhopkinson
adamhopkinson / gist:d239937c56b29cc1da2e
Last active April 5, 2019 08:58
Converting a TSQL DateTimeOffset to UTC in Excel
/*
* 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)