Bug 147825

Summary: Duration formats ([HH]:MM:SS, [MM]:SS, [SS]) are limited to max uint32 seconds values.
Product: LibreOffice Reporter: L.H. Carsten <info>
Component: CalcAssignee: Not Assigned <libreoffice-bugs>
Status: RESOLVED NOTABUG    
Severity: normal CC: erack, himajin100000, miguelangelrv
Priority: medium    
Version: 6.4.7.2 release   
Hardware: All   
OS: All   
See Also: https://bugs.documentfoundation.org/show_bug.cgi?id=144697
Whiteboard:
Crash report or crash signature: Regression By:

Description L.H. Carsten 2022-03-07 11:48:23 UTC 
Description:
The time format to prevent HH MM SS to roll over has a hard 24 hour limit.
If I set the format to HH:MM:[SS] the maximum amount of seconds is 86399, which is 23 hrs 59 min 59 sec. Also the minutes max out at 1439 and then roll over to zero.
Using the square brackets I can prevent the minutes and seconds rollover at 59, but I am not able to get more than 23 hours.
With the use of the square brackets maximum:
HH is 23
MM is 1439
SS is 86399

Steps to Reproduce:
1.Enter a number larger than 86399 in a cell (e.g. in A1)
2.Enter =TIME(0,0,A1) in 3 other cells (e.g. B1,B2,B3)
3.Set format of cell B1 to category: Time with format: HH:MM:[SS]
4.Set format of cell B2 to category: Time with format: HH:[MM]:SS
5.Set format of cell B1 to category: Time with format: [HH]:MM:SS

Actual Results:
Whatever is entered in the first cell (A1), the results in the three other cells will never exceed 23 hours, never exceed 1439 minutes and never exceed 86399 seconds. When 86400 is entered the three other cells show 00:00:00

Expected Results:
The three cells should reflect the total amount of seconds even if it is larger 86399 and show the corresponding amount of minutes or hours depending on where the brackets are used.


Reproducible: Always


User Profile Reset: No



Additional Info:
Version: 6.4.7.2
Build ID: 1:6.4.7-0ubuntu0.20.04.2
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3; 
Locale: en-GB (en_US.UTF-8); UI-Language: en-US
Calc: threaded
Comment 1 Mike Kaganski 2022-03-07 12:02:43 UTC 
I cannot reproduce with current 7.3.1.3, even not taking into account that HH:MM:[SS] and HH:[MM]:SS are both invalid format strings (the square brackets must be applied to the most significant category, so either [MM]:SS, or [SS], but no time pieces to the left of the square brackets).

Anyway, I put 150 to A1 (which means "150 days"), and format it to the invalid 'HH:MM:[SS]', and I get the cell display 00:00:12960000, where 12960000 is the correct number of seconds in 150 days.
Comment 2 Mike Kaganski 2022-03-07 12:08:33 UTC 
(In reply to L.H. Carsten from comment #0)
> 1.Enter a number larger than 86399 in a cell

Ah - I'm sorry, the number larger than 86399. In last versions, it is fixed - in a way that for any number larger than 49710, [SS] and [MM]:SS shows #FMT (see bug 129878).
Comment 3 L.H. Carsten 2022-03-07 12:20:29 UTC 
Further experimentation shows that if one adds cells already formatted as time, the result WILL exceed the maximum earlier mentioned. Obviously the format of the cell with the sum value needs to use the brackets.
Comment 4 L.H. Carsten 2022-03-07 12:27:13 UTC 
Sorry Mike, I didn't notice your comment when I added mine (#3).
Ok so it's already fixed and I need to update. I will have to wait though till the update is in my package system. That's not a problem. My goal was to make this info available and didn't find a report on this issue. So thanks for your quick response.
Comment 5 L.H. Carsten 2022-03-07 12:40:01 UTC 
> that HH:MM:[SS] and HH:[MM]:SS are both invalid format strings (the square brackets must be applied to the most significant category, so either [MM]:SS, or [SS], but no time pieces to the left of the square brackets).

Indeed, that makes sense. It delivers the same result nevertheless, so I didn't notice that mistake! :)
Comment 6 Eike Rathke 2022-03-07 13:04:23 UTC 
In SvNumberformat::ImpGetTimeOutput(), hour, minute and second values are calculated using uint32 and fTime (seconds in double) is limited to D_MAX_U_INT32. Maybe instead we could use uint64 and limit fTime more sensibly depending on how many decimal fractions are to be displayed (e.g. 2^52-1 largest integer value representable in double if no fractions, 2^48-1 if one decimal, 2^44-1 if two, ...), i.e. 2^(52-nCntPost*4)-1 or some such.

The duration formats display #FMT since LO 7.0.0 for >4294967296 seconds (71582788 minutes, 1193046 hours, 49710 days).

Fwiw, the input value 86399 is in *days* (not seconds), that would be 86399*86400=7464873600 seconds or 124414560 minutes or 2073576 hours.
Comment 7 m_a_riosv 2022-03-07 17:01:42 UTC 
(In reply to L.H. Carsten from comment #0)
> ...
> 2.Enter =TIME(0,0,A1) in 3 other cells (e.g. B1,B2,B3)
> ...

I'm wrong or TIME() gives a time result as part of one day
23:59:59	=TIME(0;0;86399)
23:59:00	=TIME(0;86399;0)
23:00:00	=TIME(86399;0;0)
it returns the maximum for seconds, minutes or hours, and the format [xx] has only one value to sum.

I think there is not a bug.
Comment 8 L.H. Carsten 2022-03-07 18:06:55 UTC 
(In reply to m.a.riosv from comment #7)
> (In reply to L.H. Carsten from comment #0)
> > ...
> > 2.Enter =TIME(0,0,A1) in 3 other cells (e.g. B1,B2,B3)
> > ...
> 
> I'm wrong or TIME() gives a time result as part of one day
> 23:59:59	=TIME(0;0;86399)
> 23:59:00	=TIME(0;86399;0)
> 23:00:00	=TIME(86399;0;0)
> it returns the maximum for seconds, minutes or hours, and the format [xx]
> has only one value to sum.
> 
> I think there is not a bug.

Indeed it seems the 'issue' is with the Time function not with the cell format. Change the cell format to number general and the output of the =Time() is always < 1
Not sure if it's a bug or a feature though!
Comment 9 Eike Rathke 2022-03-07 21:00:56 UTC 
Same as Excel, TIME() returns a value in the interval (0,1] (or rather the decimal representation of 00:00:00 to 23:59:59 in fractions of a day) applying a modulo operation on its arguments.
Comment 10 Eike Rathke 2022-03-07 21:03:01 UTC 
Sorry, interval [0,1) of course..
Comment 11 L.H. Carsten 2022-03-08 09:01:42 UTC 
I suggest this bug report can be closed as resolved, no bug!
Sorry for my confusion.