Description: If the TODAY() function is used in a cell formula, then if that cell is copied the copy wrongly has its Number Format forced (with a Direct Format) to a Date format. This is wrong because the formula may not yield a date, e.g. if it is the difference between some date value and today, which yields as (possibly negative) integer, not a date. The bug seems to occur when the cell is copied into another sheet, but not if it is copied into another cell of the same sheet. STEPS TO REPRODUCE: 1. Open the attached demo spreadsheet (t.ods) 2. Select sheet "Sheet1". Cell A1 displays a negative number (the difference in days between 2016-06-01 and today's date) 3. Right-click the sheet tab, Move or Copy sheet..., OK RESULTS: The new sheet displays some date in 1999. Cell A1 has a Direct Format forcing the Number format to a date format. EXPECTED RESULTS: The new sheet should show the same negative value as the original sheet. The format should be the same as in the original sheet, which had a numeric Number format. Steps to Reproduce: see above Actual Results: see above Expected Results: see above Reproducible: Always User Profile Reset: No Additional Info: User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:50.0) Gecko/20100101 Firefox/50.0
Created attachment 130226 [details] Demo spreadsheet used in "Steps to reproduce"
I forgot to mention: When opening the demo document, do _not_ enable macros (if prompted). Also, this is a REGRESSION. It does not happen in LO 5.2
I can't reproduce. Version: 5.3.0.1 (x64) Build ID: 3b800451b1d0c48045de03b5b3c7bbbac87f20d9 CPU Threads: 4; OS Version: Windows 6.19; UI Render: default; Layout Engine: new; Locale: es-ES (es_ES); Calc: group
Regression introduced by: author Eike Rathke <erack@redhat.com> 2016-06-08 10:08:46 (GMT) committer Eike Rathke <erack@redhat.com> 2016-06-08 10:13:24 (GMT) commit 86d8893c270dcca6b29a8b7dd15654089481af4d (patch) tree a9c8bdde55c837708024d2d2330f4c5131177a65 parent 1ec01a340a063ef6d1b773e6a693c09234bd4f27 (diff) use FormulaTypedDoubleToken in PushDouble() for temporary interim results ... and extract type information in PopDouble() before this commit, A1 is -220 and after this A1 is 24/05/99 Adding Cc: to Eike Rathke
Bug is also in the latest master: 5.4.0.0.alpha0+ Build ID: 6bd7451ecd66417a4e8b8dff3874c15ba4d1536e x64
Per description not reproducible. Loading the sample document the cell already is formatted to date. Removing that (context menu Clear Direct Formatting) the cell has value -233 (today), copying that to the clipboard, adding a new sheet and pasting the cell to the new sheet the value is still -233. However, that the cell after load is formatted to date is wrong, the document doesn't say it's a date. Also entering that formula in a new cell without formatting it results in date.
Actually this isn't even wrong, DATEVALUE returns a number, not a date, and adding/subtracting a date to a number produces a date.
So we'll probably need the context in which DATEVALUE is used, the return type is a serial date number type unless it is the final cell value in which case it is a number type. Same for TIMEVALUE.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b85ee27d9f8039a6442429587598426e73aeb1ba Resolves: tdf#105158 set date or time return type for DATEVALUE TIMEVALUE It will be available in 5.4.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/33362 for 5-3 https://gerrit.libreoffice.org/33363 for 5-3-0
Eike, Google Sheets and Gnumeric both give a date unless 'format' is manually changed to 'General'. Do we have a good reason to not follow the industry standard rules of evaluation here?
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f32deca421a7a48cb87249a87eee83f629f172a7&h=libreoffice-5-3 Resolves: tdf#105158 set date or time return type for DATEVALUE TIMEVALUE It will be available in 5.3.1. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to Luke from comment #11) > Google Sheets and Gnumeric both give a date unless 'format' is manually > changed to 'General'. Do we have a good reason to not follow the industry > standard rules of evaluation here? It's arguable. What does one expect for TODAY()-DATEVALUE("1999-11-22") ? To me that's clearly not a date 1917-03-04 but the number 6273 instead (as if subtracting two dates). Also earlier versions of LibreOffice and OOo treated this context the same. Apart from that, ODFF defines the DATEVALUE return type to Date. Return type Number is implemented as a special case when DATEVALUE is used as the outermost function, for interoperability with Excel and others.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-3-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=4c353b6b858a6027a088dd6a4466690d3d606389&h=libreoffice-5-3-0 Resolves: tdf#105158 set date or time return type for DATEVALUE TIMEVALUE It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.