We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

calc - calculate with custom timestamps [closed]

asked 2016-11-29 12:31:49 +0200

DiesNuts gravatar image

updated 2021-05-27 14:25:15 +0200

Alex Kemp gravatar image

Let's say I have cells with custom timestamps, e.g.


And I want to calculate the time elapsed in-between them (in this case, roughly 3060seconds).

How do I do that?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-02 12:55:49.851951

1 Answer

Sort by » oldest newest most voted

answered 2016-11-29 13:08:41 +0200

erAck gravatar image

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

edit flag offensive delete link more


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.

DiesNuts gravatar imageDiesNuts ( 2016-11-29 15:05:03 +0200 )edit

Question Tools

1 follower


Asked: 2016-11-29 12:31:49 +0200

Seen: 267 times

Last updated: Nov 29 '16