Strange time display

[ROSt53]

I looked into the matter @joe.aquilina.92 asked (Time formats in Calc suitable for use in calculations) as well and found something which appears to me as bug. However before I write a bug report I would get some opinions here.

LibO 4.2.6.3 on XP

I format a cell MM:SS and entered the string 55:30 directly into the cell and get as a result a display of 30:00 and applying the formula =cell/24 the result is 0.0963…
but
formatting a cell HH:MM and entered the string 55:30 directly into the cell and get as a result a display of 07:30 and applying the formula =cell/24 the result is 0.0963…

see the two screen shots:

Please also note that the input line displays always 55:33:00 when I choose A6 or A7

The file is attached. TestTime.ods

I would appreciate to get opinions on what I am seeing here.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
edit after comments:

@karolus and @Lupp - I looked at your answers and come the conclusion that the time must always be entered in a string like h:mm:ss to get it correct. Any other data string entry leads in my sheet to an error and what is displayed is not correct.

Wouldn’t it therefore be necessary to allow only an time entry in as a string of the form h:mm:ss or hh:mm:ss?

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
edit after comment from @Lupp

@Lupp - Sorry I did not want to say “…of the form h:mm:ss or hh:mm:ss?” but “…of the form hh:mm or mm:ss?”

With your long edit - thanks - my originally planned question “Wouldn’t it make sense…” becomes obsolete. For me the conclusion is to always (=independent of the cell format) enter time in the hh:mm:ss pattern to avoid any mistakes. A key element to understand is “For TOD there is a standard telling that an incomplete notation of the pattern ‘num:num’ is to read in the sense of “hours and minutes given”.” and the automatism that when 24hrs are full the display become 00:00:00 whereas the value in the cell is 24:00:00. This helped me to understand that entering the string 55:30 creates always a 0:07:30 (cell format: hh:mm:ss) or 07:30 (cell format: hh:mm). The value is 55hr 30min which is 48hr+07hr and 30 min but the display is only shows the 7hr+30min beyond the 48hr.

Pretty tricky, thus one must be very careful.

Thanks for the discussion and I hope that it was helpful for others as well.

[Lupp]

@ROSt53 - “… be necessary to allow only an time entry in as a string of the form h:mm:ss or hh:mm:ss?”

You are welcome! we may start to do it actually this way and not to wait for silly smart features getting declared deprecated and then abolished.

[Lupp]

The time format does not influence the recognition process. Thus 55:30 is 55 hours+30 minutes independent of the format code applied for the display of the cell. 55h 30min is actually 2,3125 days and this value divided by 24 is correctly evaluated to 0,0963 (4D). In the hh:mm format the 2 full days are simply suppressed as you know. Formats are often lying!

Editing in the wake of the questioners editing his question (2 hours ago):

ROSt52: … that the time must always be entered in a string like h:mm:ss to get it correct …

(You surely mean ‘h:m:s’.) That will depend on what you expect to be correct. The fact is that there are recognition patterns (Nice Features?) which are applied as soon as you hit ENTER after typing something into a cell of ‘Standard’ number format and in some additional cases I cannot find a specification for. The patterns are also applied if you call > ‘Data’ > ‘Text to Columns…’ with field type ‘Standard’. Are they always? Where specified?

These patterns I suppose to not be hard coded into Calc but placed somewhere in the config (All users? User profile? Where specified?) In any case there is no tool to change the patterns with one exception rather recently implemented : ‘Date recognition patterns’ under ‘Options’ > ‘Language Settings’ > ‘Languages’ > ‘Language of’. But even there you cannot abolish some “fundamental” patterns.

Back to time recognition patterns: The “full whole seconds format code for time of days” is ‘hh:mm:ss’. This is also an input format of acceptable reliability for TOD values - and it will even be automatically changed to the (wrongly assumed) “full whole seconds format code for time intervals” (which is ‘[hh]:mm:ss’) if you enter a value >=24:00:00. Nice feature? You are kidding! Just enter first 23:59:59 into a virgin cell and change the vallue by entering 24:00:00 next (or by adding VALUE(“0:0:1”)). Dangerous silly automatisms!

Anyhow: For TOD there is a standard telling that an incomplete notation of the pattern ‘num:num’ is to read in the sense of “hours and minutes given”.

A frequently central point for the many questions in this forum (and others) concerning “time” is the unspecified mixup of TOD with durations (see above). The input concept of Calc does’t even allow to enter durations in an unmistakeble way like 5h30min, e.g. … We tend to use formats like [hh]:mm for durations while the official standards I know (ISO or DIN) do not a bit encourage this.

The complete field of so called ‘AutoCorrection…’ and “Smart features” is a mess in fact but a goldmine for those who like misunderstandings misguidance and unaccountable errors. Explicit formatting, alas, is also often abused to the same effect.

[karolus]

hallo

enter instead 0:55:30

there is no other way for Calc to distinguish between input hh:mm and mm:ss