Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 14 Nov 2018 09:57:50 +0100[Calc] Rounding auto-inserted time valueshttps://ask.libreoffice.org/en/question/172378/calc-rounding-auto-inserted-time-values/ Using Ctrl+Shift+; is great for quickly entering the current time, but it introduces a subtle problem when performing calculations on the values.
Consider a start time and end time entered this way in fields formatted to show HH:MM
Start time: 20:11
End time: 20:20
Duration: 00:08 (End time minus Start time)
Hold on! Shouldn't the duration should be 00:09?
When I look at the actual figures entered by Ctrl+Shift+;, I see ...
Start time: 20:11:48.42
End time: 20:20:26.28
and if I reformat the Duration field as HH:MM:SS, I get the result ...
Duration: 00:08:37.86
... which of course is correct.
So, my question(s):
Is there an easy way to either truncate the Insert Current Time function (Ctrl+Shift+;) so it only inserts HH:MM, *or* what's the easiest way to round that Duration field?
Wed, 14 Nov 2018 09:02:03 +0100https://ask.libreoffice.org/en/question/172378/calc-rounding-auto-inserted-time-values/Answer by Lupp for <p>Using Ctrl+Shift+; is great for quickly entering the current time, but it introduces a subtle problem when performing calculations on the values.</p>
<p>Consider a start time and end time entered this way in fields formatted to show HH:MM</p>
<pre><code>Start time: 20:11
End time: 20:20
Duration: 00:08 (End time minus Start time)
</code></pre>
<p>Hold on! Shouldn't the duration should be 00:09?</p>
<p>When I look at the actual figures entered by Ctrl+Shift+;, I see ...</p>
<pre><code>Start time: 20:11:48.42
End time: 20:20:26.28
</code></pre>
<p>and if I reformat the Duration field as HH:MM:SS, I get the result ...</p>
<pre><code>Duration: 00:08:37.86
</code></pre>
<p>... which of course is correct.</p>
<p>So, my question(s):</p>
<p>Is there an easy way to either truncate the Insert Current Time function (Ctrl+Shift+;) so it only inserts HH:MM, <em>or</em> what's the easiest way to round that Duration field? </p>
https://ask.libreoffice.org/en/question/172378/calc-rounding-auto-inserted-time-values/?answer=172381#post-id-172381-1- Displaying time formatted with the `H,M,S` code letters always rounds DOWN - for the display only. The actual value you get if referencing the respective cell is unchanged by the formatting.
-2- Calculating with rounded values never can be expected to reliably give the last digit right.
-3- It's a wide spread misuse of the TimeOfDay (TOD) formats containing colons, to use colons also when formatting durations. Better use a code string like `[HH]" h "MM" min "SS" s"`
-4- Using "pure" TOD values for StartTime and EndTime in calculations only is acceptable if there is **assured that these time values never are entered for different days**.
-5- To force a duration calculated as a difference of two time values to **look** correct, you need to round them both down to the lowest unit used, in your case minutes. Since DOT internally always is represented based on the `day (d)` as the implicit "unit", and `1 min = 1/1440 d` you get this rounded value of any specific `tod` by the expression `INT(tod*1440)/1440`, and thus the forced-look-correct duration by
`=(INT(EndTime*1440)-INT(StartTime*1440))/1440`. The result is still in the unit d and can thus be formatted using the time-specific code letters.
-6- Another way to do the rounding for StartTime e.g. would be TIMEVALUE(TEXT(StartTime;"[hh]:mm")). This may look fine, but would not work locale-independent, probably. (Unfortunately there are locales using different code letters or time formatting.) Not recommended.Wed, 14 Nov 2018 09:42:38 +0100https://ask.libreoffice.org/en/question/172378/calc-rounding-auto-inserted-time-values/?answer=172381#post-id-172381Comment by Lupp for <p>-1- Displaying time formatted with the <code>H,M,S</code> code letters always rounds DOWN - for the display only. The actual value you get if referencing the respective cell is unchanged by the formatting. <br>
-2- Calculating with rounded values never can be expected to reliably give the last digit right. <br>
-3- It's a wide spread misuse of the TimeOfDay (TOD) formats containing colons, to use colons also when formatting durations. Better use a code string like <code>[HH]" h "MM" min "SS" s"</code> <br>
-4- Using "pure" TOD values for StartTime and EndTime in calculations only is acceptable if there is <strong>assured that these time values never are entered for different days</strong>. <br>
-5- To force a duration calculated as a difference of two time values to <strong>look</strong> correct, you need to round them both down to the lowest unit used, in your case minutes. Since DOT internally always is represented based on the <code>day (d)</code> as the implicit "unit", and <code>1 min = 1/1440 d</code> you get this rounded value of any specific <code>tod</code> by the expression <code>INT(tod*1440)/1440</code>, and thus the forced-look-correct duration by <br>
<code>=(INT(EndTime*1440)-INT(StartTime*1440))/1440</code>. The result is still in the unit d and can thus be formatted using the time-specific code letters. <br>
-6- Another way to do the rounding for StartTime e.g. would be TIMEVALUE(TEXT(StartTime;"[hh]:mm")). This may look fine, but would not work locale-independent, probably. (Unfortunately there are locales using different code letters or time formatting.) Not recommended.</p>
https://ask.libreoffice.org/en/question/172378/calc-rounding-auto-inserted-time-values/?comment=172382#post-id-172382Intentionally only added as a comment:
If you want to use a "enter-TOD-downrounded-to-the-minutes" command very frequently, you may write a "macro" for the purpose and add for calling it a sensitive area (text or icon or both) to a toolbar or/and define a shortcut key to which that macro is assigned.Wed, 14 Nov 2018 09:57:50 +0100https://ask.libreoffice.org/en/question/172378/calc-rounding-auto-inserted-time-values/?comment=172382#post-id-172382