Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 27 Oct 2019 07:05:55 +0100[SOLVED] Understanding how Calc stores timehttps://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/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?Fri, 25 Oct 2019 17:19:14 +0200https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/Comment by Mike Kaganski for <p>I have a cell: A1</p>
<p>A1 = is a number specified with time values (TT:MM:SS). Eg. 3 hours, 25 min. and 26 sec. written as 03:25:26</p>
<p>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</p>
<p><strong>However, I do not know of such a function?</strong></p>
<p>Maybe there is a whole other and smarter way to do it?</p>
https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?comment=214618#post-id-214618Please *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!Sat, 26 Oct 2019 10:58:11 +0200https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?comment=214618#post-id-214618Comment by T-buch for <p>I have a cell: A1</p>
<p>A1 = is a number specified with time values (TT:MM:SS). Eg. 3 hours, 25 min. and 26 sec. written as 03:25:26</p>
<p>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</p>
<p><strong>However, I do not know of such a function?</strong></p>
<p>Maybe there is a whole other and smarter way to do it?</p>
https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?comment=214620#post-id-214620I was inspired by this question-tread https://ask.libreoffice.org/en/question/214588/solved-draw-new-version-makes-file-file-4x-larger/
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 ;)Sat, 26 Oct 2019 11:15:58 +0200https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?comment=214620#post-id-214620Comment by Mike Kaganski for <p>I have a cell: A1</p>
<p>A1 = is a number specified with time values (TT:MM:SS). Eg. 3 hours, 25 min. and 26 sec. written as 03:25:26</p>
<p>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</p>
<p><strong>However, I do not know of such a function?</strong></p>
<p>Maybe there is a whole other and smarter way to do it?</p>
https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?comment=214624#post-id-214624This is not a forum, but Ask site, which guidelines are described [here](https://wiki.documentfoundation.org/Ask/Getting_Started) - 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.Sat, 26 Oct 2019 11:33:19 +0200https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?comment=214624#post-id-214624Answer by Opaque for <p>I have a cell: A1</p>
<p>A1 = is a number specified with time values (TT:MM:SS). Eg. 3 hours, 25 min. and 26 sec. written as 03:25:26</p>
<p>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</p>
<p><strong>However, I do not know of such a function?</strong></p>
<p>Maybe there is a whole other and smarter way to do it?</p>
https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?answer=214559#post-id-214559Hello,
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`
Fri, 25 Oct 2019 17:54:15 +0200https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?answer=214559#post-id-214559Answer by erAck for <p>I have a cell: A1</p>
<p>A1 = is a number specified with time values (TT:MM:SS). Eg. 3 hours, 25 min. and 26 sec. written as 03:25:26</p>
<p>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</p>
<p><strong>However, I do not know of such a function?</strong></p>
<p>Maybe there is a whole other and smarter way to do it?</p>
https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?answer=214564#post-id-214564Any 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..
Fri, 25 Oct 2019 18:25:54 +0200https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?answer=214564#post-id-214564Comment by erAck for <p>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 <code>[HH]:MM:SS</code> so the hours aren't modulo 24 and proper rounding is applied).</p>
<p>However, it's not quite clear to me what your <code>(30 / 1sec)</code> 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</p>
<pre><code>=A2*86400*30/1000/A1
</code></pre>
<p>However, that is not in seconds (like you stated with <em>X=45.095 sec.</em>) 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</p>
<pre><code>=A3*1000/86400
</code></pre>
<p>Looks a bit odd to me, but if that is what you want..</p>
https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?comment=214652#post-id-214652But still the calculation holds if you want to go from `03:25:26` to `45.095` and from that to `12:31:35`.Sat, 26 Oct 2019 16:07:05 +0200https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?comment=214652#post-id-214652Comment by T-buch for <p>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 <code>[HH]:MM:SS</code> so the hours aren't modulo 24 and proper rounding is applied).</p>
<p>However, it's not quite clear to me what your <code>(30 / 1sec)</code> 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</p>
<pre><code>=A2*86400*30/1000/A1
</code></pre>
<p>However, that is not in seconds (like you stated with <em>X=45.095 sec.</em>) 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</p>
<pre><code>=A3*1000/86400
</code></pre>
<p>Looks a bit odd to me, but if that is what you want..</p>
https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?comment=214591#post-id-214591Thanks 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
>=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..*Fri, 25 Oct 2019 22:25:22 +0200https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?comment=214591#post-id-214591Answer by T-buch for <p>I have a cell: A1</p>
<p>A1 = is a number specified with time values (TT:MM:SS). Eg. 3 hours, 25 min. and 26 sec. written as 03:25:26</p>
<p>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</p>
<p><strong>However, I do not know of such a function?</strong></p>
<p>Maybe there is a whole other and smarter way to do it?</p>
https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?answer=214592#post-id-214592Ok 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?
Fri, 25 Oct 2019 23:50:08 +0200https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?answer=214592#post-id-214592Comment by erAck for <p>Ok its simple - if you (as you said) can remember there is 86400 secs in a day - Or you could write <code>=A2*24*60*60</code>.
Also remember that the result-cell should not be formatted in TT:MM:SS. Just in the standard format</p>
<p>But I can't grasp the logic behind: </p>
<blockquote>
<p>"<em>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</em>"</p>
</blockquote>
<p>Why can the "03:25:26*86400 be 12.326 secs??? Someone who can show me how Calc does that calculation? </p>
https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?comment=214649#post-id-214649And please don't use the *Answer* field if not for a solution to the original problem. Use *add a comment* instead. Thanks.Sat, 26 Oct 2019 16:03:38 +0200https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?comment=214649#post-id-214649Comment by T-buch for <p>Ok its simple - if you (as you said) can remember there is 86400 secs in a day - Or you could write <code>=A2*24*60*60</code>.
Also remember that the result-cell should not be formatted in TT:MM:SS. Just in the standard format</p>
<p>But I can't grasp the logic behind: </p>
<blockquote>
<p>"<em>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</em>"</p>
</blockquote>
<p>Why can the "03:25:26*86400 be 12.326 secs??? Someone who can show me how Calc does that calculation? </p>
https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?comment=214704#post-id-214704Yes, I will never do that again - I swear;)Sun, 27 Oct 2019 07:05:55 +0100https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?comment=214704#post-id-214704Comment by Opaque for <p>Ok its simple - if you (as you said) can remember there is 86400 secs in a day - Or you could write <code>=A2*24*60*60</code>.
Also remember that the result-cell should not be formatted in TT:MM:SS. Just in the standard format</p>
<p>But I can't grasp the logic behind: </p>
<blockquote>
<p>"<em>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</em>"</p>
</blockquote>
<p>Why can the "03:25:26*86400 be 12.326 secs??? Someone who can show me how Calc does that calculation? </p>
https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?comment=214596#post-id-214596Because 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](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.Sat, 26 Oct 2019 00:48:37 +0200https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?comment=214596#post-id-214596Comment by T-buch for <p>Ok its simple - if you (as you said) can remember there is 86400 secs in a day - Or you could write <code>=A2*24*60*60</code>.
Also remember that the result-cell should not be formatted in TT:MM:SS. Just in the standard format</p>
<p>But I can't grasp the logic behind: </p>
<blockquote>
<p>"<em>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</em>"</p>
</blockquote>
<p>Why can the "03:25:26*86400 be 12.326 secs??? Someone who can show me how Calc does that calculation? </p>
https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?comment=214616#post-id-214616Thanks 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 treadSat, 26 Oct 2019 10:47:21 +0200https://ask.libreoffice.org/en/question/214550/solved-understanding-how-calc-stores-time/?comment=214616#post-id-214616