Issue 80318 - Data range validity : percent and date format not respected in the list
Summary: Data range validity : percent and date format not respected in the list
Alias: None
Product: Calc
Classification: Application
Component: formatting (show other issues)
Version: 680m223
Hardware: All All
: P3 Trivial with 4 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
: 82165 (view as issue list)
Depends on:
Reported: 2007-08-03 10:31 UTC by
Modified: 2017-05-20 11:11 UTC (History)
6 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Sample displaying the wrong formatting (13.83 KB, application/vnd.oasis.opendocument.spreadsheet)
2007-08-03 10:32 UTC,
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description 2007-08-03 10:31:38 UTC

Open the sample document :
- click in cell B16 to display the list, data are not displayed in percent
- click in cell E16 to display the list, data are not displayed in date formatting
Kind regards - Sophie
Comment 1 2007-08-03 10:32:40 UTC
Created attachment 47287 [details]
Sample displaying the wrong formatting
Comment 2 frank 2007-08-06 11:02:53 UTC
Hi Daniel,

please have a look at this one.

Comment 3 daniel.rentz 2007-12-19 10:00:58 UTC
Comment 4 daniel.rentz 2008-01-09 14:53:12 UTC
Excel inserts the formatted visible values of the source cells into the dropdown
list, but after selecting a value from the list, it inserts the raw unformatted
cell value into the destination cell, without changing its number format. To fix
this issue, we need to store the display string and the underlying value of a
cell in the dropdown list.

dr->er: with the current implementation using a formula cell and its result
matrix it is not possible to transport the formatted values (source code changes
from issue 56566). Not sure how to do this, maybe we need the possibility to ask
a formula cell for the result range instead of the result matrix, and to
manually process the range, as it was before issue 56566.
Comment 5 niklas.nebel 2008-01-09 15:09:34 UTC
See also issue 82165.
Comment 6 ooo 2008-01-09 15:45:57 UTC
Known limitation of the feature implemented with issue 56566. Since the virtual
formula cell used to calculate the arbitrary matrix (doesn't have to be a range
of cells) isn't even part of the document, there is no way to ask it for a
matrix range that matches the source range. Matrix needs a mechanism to
transport number formats in such special cases. Certainly nothing for 2.4 anymore.
Comment 7 ooo 2008-01-09 15:46:45 UTC
*** Issue 82165 has been marked as a duplicate of this issue. ***
Comment 8 ydutrieux 2008-01-09 17:06:53 UTC

perhaps this issue could be linked to issue 45291 ?

Comment 9 ooo 2008-01-09 17:41:07 UTC
Issue 45291 is about something different.
Comment 10 squenson 2008-02-08 18:00:32 UTC
Here is a workaround. Suppose you want some times to appear in the combo box,
and these times are in the range F1:F4, type the formula
TEXT($F$1:$F$4;"[HH]:MM:SS") in the source textbox in the Validity dialog, once
you have selected Cell range as a criteria. For percentages, use something like
TEXT($F$1:$F$4;"##0.00%"). Note that if you use the sorting option, the values
are sorted as text, not as numbers and therefore 75% will be between 7% and 8%.
Comment 11 lct_help 2008-04-04 20:44:23 UTC
This problem first appeared in OO v2.3 and continues in v2.4.  The problem does
not occur in v2.2.1. 

This problem also when using time formats.

The work around works if the spreadsheet is only required to work in OO but if
the same spreadsheet has to work in Excel as well it won't work because OO v2.3
and v2.4 now use a semicolon as a separator and not a comma which is required by
Excel. OO v2.2.1 accepted a comma.
Comment 12 Joe Smith 2009-07-06 16:50:09 UTC
Issue 97857 has a patch that looks like it should fix the portion of this issue
that deals with date values.
Comment 13 Christopher Schultz 2012-08-15 15:03:53 UTC
I just filed a similar bug with the LibreOffice folks (it's been plaguing me for years). Just in case anyone is interested in discussing amongst the two dev groups:
Comment 14 Marcus 2017-05-20 11:11:42 UTC
Reset assigne to the default "".