Bug 150049 - Formatted time is not rounding by most granular token
Summary: Formatted time is not rounding by most granular token
Status: UNCONFIRMED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2022-07-18 17:37 UTC by SheetJS
Modified: 2024-04-16 16:51 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description SheetJS 2022-07-18 17:37:33 UTC 
Description:
A format like `HH:MM:SS` should round based on the most granular time.

Steps to Reproduce:
=TEXT(0.00069,"ss")

Actual Results:
59

Expected Results:
00


Reproducible: Always


User Profile Reset: Yes



Additional Info:
Rounding does work with sub-second units. Testing with the value 0.002415:

| format | LO     | Excel  |
+:-------+:-------+:-------+
| ss     | 28.    | 29     |
| ss.0   | 28.7   | 28.7   |
| ss.00  | 28.66  | 28.66  |
| ss.000 | 28.656 | 28.656 |
Comment 1 Mike Kaganski 2022-07-18 19:33:12 UTC 
Not convinced.

In LibreOffice, there is explicitly decided difference between wall clock time formats (HH:MM:SS), and duration formats ([SS] or [HH]:MM:SS) - the former do not round, but truncate, because it's not tomorrow until it's tomorrow. But for durations, rounding happens. Compare

=TEXT(0.00069;"ss")

and 

=TEXT(0.00069;"[ss]")

OTOH: see bug 136615. It even has an abandoned change https://gerrit.libreoffice.org/c/core/+/117924.
Comment 2 SheetJS 2022-07-18 20:00:21 UTC 
Duration formats are currently broken (see https://bugs.documentfoundation.org/show_bug.cgi?id=150028)

The assertion "LibreOffice does not round wall clock time formats" is incorrect and an example was included in the "additional info" section:

"""
Rounding does work with sub-second units. Testing with the value 0.002415:

| format | LO     | Excel  |
+:-------+:-------+:-------+
| ss     | 28     | 29     |
| ss.0   | 28.7   | 28.7   |
| ss.00  | 28.66  | 28.66  |
| ss.000 | 28.656 | 28.656 |
"""

To be more explicit, if LO were internally consistent, for the value 0.002415 you would expect the following (written in the reverse order for clarity):

"ss.000" -> "28.656"
"ss.00"  -> "28.65"
"ss.0"   -> "28.6"
"ss"     -> "28"

However, LO generates the following:

"ss.000" -> "28.656"
"ss.00"  -> "28.66"  /* rounding :( */
"ss.0"   -> "28.7"   /* rounding :( */
"ss"     -> "28"     /* not rounding */
Comment 3 Mike Kaganski 2022-07-18 21:49:40 UTC 
(In reply to SheetJS from comment #2)

I hoped that the idea would be clear without long clarifications. It does not round to seconds (and higher parts), decimals are rounded, which doesn't change the argument.
Comment 4 Eike Rathke 2023-01-25 21:08:20 UTC 
We should not reintroduce the wrong wall clock time rounding.
So this would be a request to also truncate the fraction of seconds to its precision, instead of rounding it.
Comment 5 SamKevin 2024-01-25 07:27:03 UTC Comment hidden (spam)