Issue 45291 - Date validity not correctly evluated
Summary: Date validity not correctly evluated
Alias: None
Product: Calc
Classification: Application
Component: ui (show other issues)
Version: OOo 1.1.4
Hardware: All All
: P3 Trivial with 3 votes (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
Keywords: oooqa
Depends on:
Reported: 2005-03-17 13:01 UTC by steve_may_bcc
Modified: 2013-08-07 15:14 UTC (History)
6 users (show)

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

the patch for date/time data validity (3.65 KB, text/plain)
2009-02-13 06:01 UTC, gaojingmei
no flags Details
the updated patch for this issue (2.10 KB, text/plain)
2009-02-19 06:16 UTC, gaojingmei
no flags Details
the updated patch of this issue (2.82 KB, text/plain)
2009-02-26 08:19 UTC, gaojingmei
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description steve_may_bcc 2005-03-17 13:01:43 UTC
I am trying to set date validity for a cell, using the Tools/Validity dialog. 
All options for data types appear to work OK apart from Date, in all Data 
options (i.e. Equals, Between, etc).
For example, setting Date Between Min 01/01/2005 and Max 01/01/2010 should 
allow dates such as 01/01/2006, but returns an Invalid Date message. 
I have tried a variety of cell date formats, etc. but with the same result.
Setting "Not Between" instead of "Between" in the example above DOES allow 
entry of 01/01/06.
Comment 1 frank 2005-03-24 09:53:03 UTC

could not reproduce on OOo1.9.78 so closing worksforme.

Comment 2 frank 2005-03-24 09:53:19 UTC
closed wfm
Comment 3 alex.thurgood 2006-01-05 19:20:15 UTC
This bug is present in 2.0.1 and has been validated by at least 5 different
people on the French qa list using FR locale, various OS

reopening issue and setting OS all and oooqa

Comment 4 alex.thurgood 2006-01-05 19:21:30 UTC
confirming issue
Comment 5 alex.thurgood 2006-01-05 19:26:40 UTC
I might add that if you use the internal date representation number instead of
the date, then the function appears to work. It's still a bug though, as it
means you have to enter the date first in another cell, format that cell to show
the internal representation and then copy it into the dialog box :-( I don't
honestly think it was designed to work like that ;-)

Comment 6 frank 2006-01-30 11:27:56 UTC

tried it with internal build m154 and can reproduce if I use not locale conform
separators. Using 1/1/2006 in a german locale leads to a fault. Using 1.1.2006
works perfectly.

So this is not a bug but wrong use of separators.

Closing wfm.

Comment 7 frank 2006-01-30 11:31:10 UTC
closed wfm
Comment 8 alex.thurgood 2006-01-31 06:50:17 UTC
Using the separators as you mentioned is the correct way of representing the
date in a German or French or Spanish locale, so how can you say that it is not
a bug. We have had a debate similar to this in the past with another Calc
function and a "it isn't a bug if you do it the US way" mentality.

Excel doesn't do this, neither should OOo.

You can't expect users to type their dates in one way that is natural for their
locale and then have to type them in another way for one or more of the
functions that the product makes available. It is inconsistent and IMHO bad
product quality. For many users who use a locale other than en_US, this is a
bug. You even admit that the bug can be reproduced if you enter the date with
the locale separators.

I'm reopening, since one of the goals of V2 was increased compatibility with MS
products, and that means that the goal hasn't been reached here.

Comment 9 frank 2006-01-31 13:47:40 UTC
Hi Niklas,

please comment on this one and do whatever has to be done.

Comment 10 niklas.nebel 2006-03-14 19:27:36 UTC
The problem is that you can enter a formula for the value, and in formulas, the
"/" character is parsed as division operator (see issue 5750). The parsing has
to be changed for validation type "Date". Accepting.
Comment 11 niklas.nebel 2006-07-07 19:06:20 UTC
changing target
Comment 12 niklas.nebel 2007-12-04 18:07:18 UTC
retarget 2.x -> 3.x
Comment 13 ydutrieux 2008-01-05 00:49:01 UTC
I agree that this is not a user 'friendly' option.
As the problem could be internaly (manualy) solved with a date validity value :
and it works.

I think, this could be easily implemented in the check-box code to convert a
date value in the corresponding value trough the function.
Locale is then respected isnt'it ?

Comment 14 amy2008 2009-01-08 03:01:45 UTC
Meiying -> Frank and wurzel,
Criteria Allow as Date and Time have the similar problem.
If Date, date between 2008-10-1 and 2009-10-1, OK. Enter 2009-1-1, return 
invalid Date message.
If Time, time between 9:30:30 and 12:30:30, OK. Enter 11:11:11, return invalid 
value too.
Best regards!
Comment 15 amy2008 2009-01-08 03:03:58 UTC
Meiying -> Niklas,
Would you like checking it if you are free? Waiting for your confirmation.

Best regards!
Comment 16 gaojingmei 2009-02-13 06:01:02 UTC
Created attachment 60135 [details]
the patch for date/time data validity
Comment 17 gaojingmei 2009-02-13 06:22:41 UTC
In data/Validity dialog, the inputing value should be checked, for example,
After setting the  data equals "a", it should pop up a message dialog and say
the data value is illegal.
Comment 18 niklas.nebel 2009-02-17 19:23:56 UTC
gaojingmei, ScConditionEntry is also used for conditional formatting, so in
ScConditionEntry::Interpret you can't assume that you're handling validity.

It's probably best to convert the date string into the corresponding number when
the validity entry is created from the dialog.

An error message for invalid input is a separate subject (issue 55161).
Comment 19 gaojingmei 2009-02-19 06:16:35 UTC
Created attachment 60294 [details]
the updated patch for this issue
Comment 20 gaojingmei 2009-02-26 08:19:00 UTC
Created attachment 60498 [details]
the updated patch of this issue
Comment 21 niklas.nebel 2009-03-18 14:35:37 UTC
I added the last patch to CWS "calc49".
Comment 22 niklas.nebel 2009-04-03 09:01:54 UTC
reassigning to QA for verification
Comment 23 oc 2009-05-12 12:07:57 UTC
verified in internal build cws_calc49
Comment 24 amy2008 2009-06-02 07:57:41 UTC
Verified in DEV300m49 on WinXP