[Calc] Rounding auto-inserted time values

[zzxkkw]

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?

[Lupp]

-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.

[Lupp]

Intentionally 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.