Bug 136050 - Calc changes the cell type from time to date when the format-code is changed
Summary: Calc changes the cell type from time to date when the format-code is changed
Status: CLOSED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.4.4.2 release
Hardware: All All
: medium minor
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Number-Format
  Show dependency treegraph
 
Reported: 2020-08-23 15:27 UTC by BDF
Modified: 2020-08-25 19:59 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
data file with different format codes (26.64 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-08-23 15:28 UTC, BDF
Details

Note You need to log in before you can comment on or make changes to this bug.
Description BDF 2020-08-23 15:27:21 UTC
Description:
I have a calc table where collect data about driving my car. The collected data is collected as follwing:
  km from: The odometer when I leave the start point (accurate to a 10th of a kilometer - only used for a different bug)
    km to: The odometer when I arrive the end point (accurate to a 10th of a kilometer - only used for a different bug)
time from: The clock when I leave the start point (accurate on the minute)
  time to: The clock when I arrive the end point (accurate on the minute)

When I auto calc the time driven (eg. "=D2-C2") the results show up as [HH]:MM:SS by default. When I change how the time is displayed with the format-code, the cell type is changed from time to date and the result is incorrect.

Example:
time from: 17:54
  time to: 18:13

The calculation results in:
* [HH]:MM:SS = 00:19:00 (default)
* [HH]:MM = 00:19
* HH:MM = 00:19
* MM:SS = 19:00
* MM = 12

The default [HH]:MM:SS as well as [HH]:MM, HH:MM and MM:SS work fine. When the format-code is changed to "MM" the displayed result is incorrect.
This can be fixed with "[MM" or "[MM]", but I would expect "MM" to work as well. I know that "MM" is used in the date-type cells to display the month, so from the code-logic point of view, it makes sence that that "MM" must represent a month. However, since HH:MM and MM:SS work fine, it does not make a whole lot of sence seen from a human-logic point of view.

For this I have no idea how I would fix this - or what I would expect the program to do. Maybe CALC could add square brackets by default when you edit the cell as time-time and only if you switch to the date type by hand you can get rid of them.

Steps to Reproduce:
1. Enter a time like 00:19:00
2. Change the cell format-code from "[HH]:MM:SS" to "MM"

Actual Results:
The result is displayed as month "12"

Expected Results:
The result should display only the mintes "19"


Reproducible: Always


User Profile Reset: No



Additional Info:
Version: 6.4.4.2 (x64)
Build-ID: 3d775be2011f3886db32dfd395a6a6d1ca2630ff
CPU-Threads: 6; BS: Windows 6.1 Service Pack 1 Build 7601; UI-Render: Standard; VCL: win; 
Gebietsschema: de-AT (de_AT); UI-Sprache: de-DE
Calc: CL

----

I know that I should use the latest version of LO for this, but right now, I'm short on time. I test this with the lastest version of the 7.x branch once I had time to deal with it. However, I expect the results to be the same.
Comment 1 BDF 2020-08-23 15:28:08 UTC
Created attachment 164610 [details]
data file with different format codes
Comment 2 Eike Rathke 2020-08-25 19:59:29 UTC
A single MM format code is month of year, not minutes, and displays the underlying cell value accordingly. This is not a bug and will not be changed. There is no time type or date type cell. A date or time or date+time is just a floating point number, days since null date, where 0.5 is 12 hours, the time 00:19 with 0 days since null date formatted as date happens to be 1899-12-31 so the result for MM is 12.

You can use the format code [MM] to format a duration in minutes only.