Ask Your Question
0

calc - calculate with custom timestamps

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

DiesNuts gravatar image

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

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

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

Comments

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 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 133 times

Last updated: Nov 29 '16