[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?

edit retag close merge delete

Sort by » oldest newest most voted

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

more