Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 02 May 2016 18:35:14 +0200cell format date time durationhttps://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/Need to find a clean way to **change a remainder value of time, from decimal to minutes**. ie 0.25 should be 0.25/60 = 15 mins. if I format the result cell C1 , best I can come up with is a fraction with a **format code** 0.00/60
The following formula I am using gives the duration or time difference between 2 dates and times. I can't seem to seperate my exponent to the left of the decimal from the trig on the right of the decimal point? should be basic. Don't want macros.
Cells A1 = 28/04/2016 , B1 = 06:15
A2 = 29/04/2016 , B2 = 12:25
C1= =SUM((A2+B2)-(A1+A2))*24
C1 looks like 30.10/60 with my format code.
So , any ideas how I can remove the /60 part for a clean number. I'd ideally like 40hrs 15Min as a result. If the exp and trig can be seperated,I can do that bit. Thanks for any help.
Thu, 28 Apr 2016 22:43:04 +0200https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/Answer by Lupp for <p>Need to find a clean way to <strong>change a remainder value of time, from decimal to minutes</strong>. ie 0.25 should be 0.25/60 = 15 mins. if I format the result cell C1 , best I can come up with is a fraction with a <strong>format code</strong> 0.00/60</p>
<p>The following formula I am using gives the duration or time difference between 2 dates and times. I can't seem to seperate my exponent to the left of the decimal from the trig on the right of the decimal point? should be basic. Don't want macros.</p>
<p>Cells A1 = 28/04/2016 , B1 = 06:15
A2 = 29/04/2016 , B2 = 12:25
C1= =SUM((A2+B2)-(A1+A2))*24</p>
<p>C1 looks like 30.10/60 with my format code.
So , any ideas how I can remove the /60 part for a clean number. I'd ideally like 40hrs 15Min as a result. If the exp and trig can be seperated,I can do that bit. Thanks for any help.</p>
https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?answer=68973#post-id-68973I didn't understand the term "exponent" in the context, and I do not know the term "trig".
You may have a look into the attached.
To separate the integer part and the fractional part of a numeric value, you may use
`INT(Number)` and `MOD(Numb[ask68968TimeAgain001.ods](/upfiles/14618814087968083.ods)er;1)` respectively.
Sorry! Just found that some answers crossed while I was off for a short time.Fri, 29 Apr 2016 00:10:14 +0200https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?answer=68973#post-id-68973Comment by StarMariner for <p>I didn't understand the term "exponent" in the context, and I do not know the term "trig". </p>
<p>You may have a look into the attached. </p>
<p>To separate the integer part and the fractional part of a numeric value, you may use <br/>
<code>INT(Number)</code> and <code>MOD(Numb[ask68968TimeAgain001.ods](/upfiles/14618814087968083.ods)er;1)</code> respectively.</p>
<p>Sorry! Just found that some answers crossed while I was off for a short time.</p>
https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?comment=68976#post-id-68976Sorry my bad, old school processor terms :https://en.wikibooks.org/wiki/Microprocessor_Design/FPUFri, 29 Apr 2016 00:22:54 +0200https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?comment=68976#post-id-68976Answer by karolus for <p>Need to find a clean way to <strong>change a remainder value of time, from decimal to minutes</strong>. ie 0.25 should be 0.25/60 = 15 mins. if I format the result cell C1 , best I can come up with is a fraction with a <strong>format code</strong> 0.00/60</p>
<p>The following formula I am using gives the duration or time difference between 2 dates and times. I can't seem to seperate my exponent to the left of the decimal from the trig on the right of the decimal point? should be basic. Don't want macros.</p>
<p>Cells A1 = 28/04/2016 , B1 = 06:15
A2 = 29/04/2016 , B2 = 12:25
C1= =SUM((A2+B2)-(A1+A2))*24</p>
<p>C1 looks like 30.10/60 with my format code.
So , any ideas how I can remove the /60 part for a clean number. I'd ideally like 40hrs 15Min as a result. If the exp and trig can be seperated,I can do that bit. Thanks for any help.</p>
https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?answer=68971#post-id-68971Why not:
=A2+B2-A1-B1
with **Formatcode`[HH]:MM`** Thu, 28 Apr 2016 23:52:22 +0200https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?answer=68971#post-id-68971Comment by StarMariner for <p>Why not:</p>
<pre><code>=A2+B2-A1-B1
</code></pre>
<p>with <strong>Formatcode<code>[HH]:MM</code></strong> </p>
https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?comment=68975#post-id-68975I tried that , didnt work for me. It's limited to 24 hours , I needed the days as hours , 2 days being 48hrs ectc..ThanksFri, 29 Apr 2016 00:12:35 +0200https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?comment=68975#post-id-68975Comment by erAck for <p>Why not:</p>
<pre><code>=A2+B2-A1-B1
</code></pre>
<p>with <strong>Formatcode<code>[HH]:MM</code></strong> </p>
https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?comment=69199#post-id-69199It is not. Note the difference between `[HH]:MM` and `HH:MM`Mon, 02 May 2016 18:35:14 +0200https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?comment=69199#post-id-69199Answer by StarMariner for <p>Need to find a clean way to <strong>change a remainder value of time, from decimal to minutes</strong>. ie 0.25 should be 0.25/60 = 15 mins. if I format the result cell C1 , best I can come up with is a fraction with a <strong>format code</strong> 0.00/60</p>
<p>The following formula I am using gives the duration or time difference between 2 dates and times. I can't seem to seperate my exponent to the left of the decimal from the trig on the right of the decimal point? should be basic. Don't want macros.</p>
<p>Cells A1 = 28/04/2016 , B1 = 06:15
A2 = 29/04/2016 , B2 = 12:25
C1= =SUM((A2+B2)-(A1+A2))*24</p>
<p>C1 looks like 30.10/60 with my format code.
So , any ideas how I can remove the /60 part for a clean number. I'd ideally like 40hrs 15Min as a result. If the exp and trig can be seperated,I can do that bit. Thanks for any help.</p>
https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?answer=68972#post-id-68972Oh well , I have a solution: Have 2 cells, the first cell
`M5=SUM( (K5+L5)-(I5+J5))*24` # this is the full result
`N5=MOD(M5,1)*60` # this is the right side as a decimal .18 multiplied by 60 =11 minutes.
Now above that I have 2 cells for the title, which are merged.
Cell formating is just numbers with no trailing decimals.Fri, 29 Apr 2016 00:08:14 +0200https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?answer=68972#post-id-68972Answer by erAck for <p>Need to find a clean way to <strong>change a remainder value of time, from decimal to minutes</strong>. ie 0.25 should be 0.25/60 = 15 mins. if I format the result cell C1 , best I can come up with is a fraction with a <strong>format code</strong> 0.00/60</p>
<p>The following formula I am using gives the duration or time difference between 2 dates and times. I can't seem to seperate my exponent to the left of the decimal from the trig on the right of the decimal point? should be basic. Don't want macros.</p>
<p>Cells A1 = 28/04/2016 , B1 = 06:15
A2 = 29/04/2016 , B2 = 12:25
C1= =SUM((A2+B2)-(A1+A2))*24</p>
<p>C1 looks like 30.10/60 with my format code.
So , any ideas how I can remove the /60 part for a clean number. I'd ideally like 40hrs 15Min as a result. If the exp and trig can be seperated,I can do that bit. Thanks for any help.</p>
https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?answer=68969#post-id-68969Why not simply use the formula `=(A2+B2)-(A1+B1)` and apply the number format `[HH]:MM`
Btw, the SUM() function is completely unnecessary in this case, it sums only one argument for which the result is identical to the argument.
Thu, 28 Apr 2016 23:41:31 +0200https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?answer=68969#post-id-68969Comment by StarMariner for <p>Why not simply use the formula <code>=(A2+B2)-(A1+B1)</code> and apply the number format <code>[HH]:MM</code></p>
<p>Btw, the SUM() function is completely unnecessary in this case, it sums only one argument for which the result is identical to the argument.</p>
https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?comment=68977#post-id-68977I removed the SUM part, looks a lot nicer now, Thanks :)Fri, 29 Apr 2016 00:31:56 +0200https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?comment=68977#post-id-68977Comment by StarMariner for <p>Why not simply use the formula <code>=(A2+B2)-(A1+B1)</code> and apply the number format <code>[HH]:MM</code></p>
<p>Btw, the SUM() function is completely unnecessary in this case, it sums only one argument for which the result is identical to the argument.</p>
https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?comment=68974#post-id-68974old habit, thanks .Fri, 29 Apr 2016 00:11:07 +0200https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?comment=68974#post-id-68974Answer by Ratslinger for <p>Need to find a clean way to <strong>change a remainder value of time, from decimal to minutes</strong>. ie 0.25 should be 0.25/60 = 15 mins. if I format the result cell C1 , best I can come up with is a fraction with a <strong>format code</strong> 0.00/60</p>
<p>The following formula I am using gives the duration or time difference between 2 dates and times. I can't seem to seperate my exponent to the left of the decimal from the trig on the right of the decimal point? should be basic. Don't want macros.</p>
<p>Cells A1 = 28/04/2016 , B1 = 06:15
A2 = 29/04/2016 , B2 = 12:25
C1= =SUM((A2+B2)-(A1+A2))*24</p>
<p>C1 looks like 30.10/60 with my format code.
So , any ideas how I can remove the /60 part for a clean number. I'd ideally like 40hrs 15Min as a result. If the exp and trig can be seperated,I can do that bit. Thanks for any help.</p>
https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?answer=68980#post-id-68980Based upon the Date/times in the question, the result is 30hrs & 10 Min. All the calculations present this as a result. If all that remains is a resulting format, why not ` [HH] "Hrs" MM "Min"`?
Sample:[TimeFormat.png](/upfiles/14618871153271931.png)Fri, 29 Apr 2016 01:47:41 +0200https://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/?answer=68980#post-id-68980