Ask Your Question
0

[SOLVED] Understanding how Calc stores time [closed]

asked 2019-10-25 17:19:14 +0100

T-buch gravatar image

updated 2019-10-26 10:56:07 +0100

I have a cell: A1

A1 = is a number specified with time values (TT:MM:SS). Eg. 3 hours, 25 min. and 26 sec. written as 03:25:26

I imagine myself to have a function that converts A2 = 03: 25: 26 to seconds (12,326 secs) and again after doing some calculation which give me this result: 45,095 secs - This I want to convert back to 12:31:35

However, I do not know of such a function?

Maybe there is a whole other and smarter way to do it?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2019-10-26 16:32:27.932204

Comments

Please don't modify the topic here to mention any fancy thing like [SOLVED] or the like, as common in forums. This is not a forum, and you need to mark the chosen answer as correct using the checkmark to the left of the answer. This properly marks the question, and helps others to see it (including using different filters on the site). Thanks!

Mike Kaganski gravatar imageMike Kaganski ( 2019-10-26 10:58:11 +0100 )edit

I was inspired by this question-tread https://ask.libreoffice.org/en/questi... I also think is a good service for the users of the site to mark a question as solved But if there is some page guidelines, which describe that you must not (link please), then of course I will not do so

Otherwise.... Tastes differ, as people say ;)

T-buch gravatar imageT-buch ( 2019-10-26 11:15:58 +0100 )edit

This is not a forum, but Ask site, which guidelines are described here - the link is on the main page.

Now that you've checked the mark, it already is marked for users - so modifying the title is useless, and gives a noise to everyone who participated here.

Mike Kaganski gravatar imageMike Kaganski ( 2019-10-26 11:33:19 +0100 )edit

3 Answers

Sort by » oldest newest most voted
1

answered 2019-10-25 17:54:15 +0100

Opaque gravatar image

updated 2019-10-26 12:23:24 +0100

Hello,

you don't need a function - the only things you need to know is that a time in calc is a fraction of a day and a day has 86400 seconds - thus the "function" to get the seconds is simply:

=A2*86400

edit flag offensive delete link more
1

answered 2019-10-25 18:25:54 +0100

erAck gravatar image

Any given (date+)time value is a floating point number with time in days, e.g. 0.5 == 12h. To convert the value displayed as time simply multiply it by 86400 to get the number of seconds, and to convert an amount of seconds to display it as time divide by 86400 (and best use the duration format [HH]:MM:SS so the hours aren't modulo 24 and proper rounding is applied).

However, it's not quite clear to me what your (30 / 1sec) actually should achieve. 1sec is what? And what unit are the 30 supposed to be? (You could clarify by editing your question). Anyway, to obtain the given result 45.095 the formula would be

=A2*86400*30/1000/A1

However, that is not in seconds (like you stated with X=45.095 sec.) but in "thirty-millis" or "three-hundreths", so to convert back to reach a result of 12:31:35 the formula (assuming the previous formula to be in A3 here) is

=A3*1000/86400

Looks a bit odd to me, but if that is what you want..

edit flag offensive delete link more

Comments

Thanks for the reply

Sorry, Now I have made some editing to clarify my point

So this part of your (erAck) answer is not actual any more:

However, it's not quite clear to me what your (30 / 1sec) actually should achieve. 1sec is what? And what >unit are the 30 supposed to be? (You could clarify by editing your question). Anyway, to obtain the given r>esult 45.095 the formula would be =A28640030/1000/A1 However, that is not in seconds (like you stated with X=45.095 sec.) but in "thirty-millis" or "three->hundreths", so to convert back to reach a result of 12:31:35 the formula (assuming the previous >formula to be in A3 here) is =A31000/86400 Looks a bit odd to me, but if that is what you want..*

T-buch gravatar imageT-buch ( 2019-10-25 22:25:22 +0100 )edit

But still the calculation holds if you want to go from 03:25:26 to 45.095 and from that to 12:31:35.

erAck gravatar imageerAck ( 2019-10-26 16:07:05 +0100 )edit
0

answered 2019-10-25 23:50:08 +0100

T-buch gravatar image

updated 2019-10-26 11:21:59 +0100

Ok its simple - if you (as you said) can remember there is 86400 secs in a day - Or you could write =A2*24*60*60. Also remember that the result-cell should not be formatted in TT:MM:SS. Just in the standard format

But I can't grasp the logic behind:

"the only things you need to know is that a time in calc is a fraction of a day and a day has 86400 seconds"

Why can the "03:25:26*86400 be 12.326 secs??? Someone who can show me how Calc does that calculation?

edit flag offensive delete link more

Comments

Because you don't understand that if "03:25:26" is really a calc time (and not just text), then it is stored as an number(i.e. as already mentioned in my answer: a fraction of a day; i.e 1.0 is 24 hours; 0.5=12 hours, 0.25=6 hours ....). If it is not a number it is not a calc time. Just make an example, and write 03:25:26 into a cell and format that cell as a number. Please read the OASIS ODF specification: http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1017886_715980110

The thing is that it is a common misunderstanding to assume a format somehow changes the data stored. The format is just a representation of what is stored.

Opaque gravatar imageOpaque ( 2019-10-26 00:48:37 +0100 )edit

Thanks now I understand - just to add some more to your explanation- that is:

The Number 86400 (which is 24(hour)60(min)60(sec) = a day - so if you want to know how many seconds there are in a half day=12 hours -> simple 0,5 * 86400

As you told me (and erAck) the 03:25:26 is just a representation - The way calc reads time is to see it as 0,142662037037037 of a day - hence 0,142662037037037 * 86400

PS By the way, Do you know If there is a way to be reminded that someone has made a comment to your tread

T-buch gravatar imageT-buch ( 2019-10-26 10:47:21 +0100 )edit

And please don't use the Answer field if not for a solution to the original problem. Use add a comment instead. Thanks.

erAck gravatar imageerAck ( 2019-10-26 16:03:38 +0100 )edit

Yes, I will never do that again - I swear;)

T-buch gravatar imageT-buch ( 2019-10-27 07:05:55 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2019-10-25 17:19:14 +0100

Seen: 59 times

Last updated: Oct 26