Let’s say I have cells with custom timestamps, e.g.
2016-10-18T14:14:09.831Z-GET
2016-10-18T15:05:09.854Z-GET
And I want to calculate the time elapsed in-between them (in this case, roughly 3060seconds).
How do I do that?
You have a text string from which you need to remove the timezone information to be recognized as date+time value. For example, with 2016-10-18T14:14:09.831Z-GET in A1 the date+time value can be obtained with =VALUE(LEFT(A1,LEN(A1)-5)) (if different timezones other than “Z-GET” can be present you’ll have to adapt to that). Two of such values can simply be subtracted, the result is in days so if you want the result to be in seconds then either use the format code [SS].000 or multiply the result by 86400.
To not use a formula to obtain the date+time value but convert a column of values you can use menu Data - Text to Columns to split, and as separator use Z
Btw, “Z-GET” is not a valid ISO 8601 timezone designator…
Thanks a lot. Word of caution: using format code [SS].000 changes the way the value is displayed but not the value itself. So unless you don’t care about the conversion to seconds (other than for cosmetic reasons), you cannot use the code and should multiply by 86400, instead.