Bug 97631 - cell with =24/2 formatted as date causes saved file to violate specification
Summary: cell with =24/2 formatted as date causes saved file to violate specification
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All Linux (All)
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2016-02-08 09:32 UTC by Mateusz Konieczny
Modified: 2017-04-17 05:24 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

file saved with LO 5.2 (7.29 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2016-02-08 20:54 UTC, raal
12 formatted as a time and as a date. (16.68 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-04-03 16:41 UTC, Carlos

Note You need to log in before you can comment on or make changes to this bug.
Description Mateusz Konieczny 2016-02-08 09:32:15 UTC
To reproduce:

    create a new file
    put =24/2 into A1 cell
    format A1 cell as time (Format | Number Format | Time)
    save as test.ods

Time format may be also achieved by putting 23:00 in A2 and copying format from A2 to A1 cell.

This cell is saved as "<table:table-cell table:formula="of:=24/2" office:value-type="time" 
office:time-value="PT288H00M00S"><text:p>00:00:00</text:p></table:table-cell>" what appears to violate specification - see http://books.evc-cit.info/odbook/ch05.html#table-number-cells-section

Problem encountered as it crashed python library processing .ods files - see https://github.com/pyexcel/pyexcel-ods/issues/6
Comment 1 raal 2016-02-08 20:54:24 UTC
Created attachment 122461 [details]
file saved with LO 5.2

File saved with LO 5.2, seems to be correct. Please test.

<table:table-cell table:formula="of:=24/2" office:value-type="time" office:time-value="PT288H00M00S" calcext:value-type="time"><text:p>00:00</text:p></table:table-cell>

Build ID: 91a7580e03d5b47c6e2513afce85ddee45e730b6
CPU Threads: 4; OS Version: Linux 4.2; UI Render: default; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2016-02-04_00:17:43

Comment 2 raal 2016-02-08 21:10:36 UTC
(In reply to raal from comment #1)
> Created attachment 122461 [details]
> file saved with LO 5.2
see it now - from bug report 

'PT288H00M00S' violates this spec: PThhHmmMss,ffffS hence kills the 'time_value' converter. 

format specification is here http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2.html
Comment 3 raal 2016-02-08 21:39:08 UTC
From specification OpenDocument-v1.1.odt
Duration, as specified in §3.2.6 of [xmlschema-2]

[xmlschema-2]  Paul V. Biron, Ashok Malhotra, XML Schema Part 2: Datatypes Second Edition, http://www.w3.org/TR/2004/REC-xmlschema-2-20041028/, W3C, 2004.

Comment 4 Aron Budea 2016-08-03 06:37:42 UTC
Raal, does your observation count as repro?
Comment 5 Carlos 2017-04-03 15:20:40 UTC
Please see 
The spec says "time	The value is stored in a office:time-value attribute rather than a office:value. The value is stored in the form PThhHmmMss,ffffS (where ffff is the fractional part of a second). "

But it is unclear that the ",ffffS"  part is mandatory. 

It also presents an example of cell that produces a time value from the contents of three cells.

<table:table-cell table:formula="oooc:=TIME([.E1];[.E2];[.E3])"
  office:value-type="time" office:time-value="PT10H05M48S">
	<text:p>10:05:48 AM</text:p>

So the absence of the factional part is not the problem.
Comment 6 Carlos 2017-04-03 15:36:03 UTC
Perhaps Mateusz meant that in 
the 288 is incorrect. 

Let's consult the specs. 
"The office:time-value attribute has the data type duration"

"18.2W3C Schema Datatypes

The following [xmlschema-2] datatypes are used in this specification:

" Lexical representation

The lexical representation for duration is the [ISO 8601] 
extended format PnYn MnDTnH nMnS, 
where nY represents the number of years, 
nM the number of months, 
nD the number of days, 
'T' is the date/time separator, 
nH the number of hours, 
nM the number of minutes and nS the number of seconds. 
The number of seconds can include decimal digits to arbitrary precision.

The values of the Year, Month, Day, Hour and Minutes components 
are not restricted but allow an arbitrary unsigned integer, i.e., 
an integer that conforms to the pattern [0-9]+.. 
Similarly, the value of the Seconds component allows an arbitrary unsigned decimal. 
Following [ISO 8601], at least one digit must follow the decimal point if it appears. 
That is, the value of the Seconds component must conform to the pattern [0-9]+(\.[0-9]+)?. 
Thus, the lexical representation of duration does not follow the alternative format of § of [ISO 8601].

So the value 288 (for the Hour component) is valid as it conforms to the the pattern [0-9]+
Comment 7 Carlos 2017-04-03 16:34:56 UTC
The number 12 (or =12 or -24/12) is interpreted by LO as twelve days after the start of time. 
Twelve days is equivalent to 288 hours. 
Twelve days as a time is take to happen after the "start of time".
LO considers the "start of time" as being 1899-12-30 00:00:00.
So 12 is equivalent to '1900-01-11 00:00:00' or '288:00:00.00'.
Comment 8 Carlos 2017-04-03 16:41:38 UTC
Created attachment 132311 [details]
12 formatted as a time and as a date.
Comment 9 Mateusz Konieczny 2017-04-16 18:04:05 UTC
Thanks for rereading docs. Turned out that https://github.com/pyexcel/pyexcel-ods/issues/6 should be fixed (and it was fixed).
Comment 10 Aron Budea 2017-04-17 05:24:29 UTC
Let me adjust status to NOTABUG, as on LibreOffice's side this was not a bug in the first place.