Confusion between Cell Format & Data Validation

[IRV]

When trying to type text into a Date cell as set by Data Validation,
there's a rather unhelpful warning that the input must be in date form.
I kept changing the Cell Format (or Number format in the Document
Palette) trying to fix it, before I realized that the way you fix it is
in Data Validation. I wish this option was offered in the error dialog.

 

[JE McGimpsey]

When trying to type text into a Date cell as set by Data Validation,
there's a rather unhelpful warning that the input must be in date form.
I kept changing the Cell Format (or Number format in the Document
Palette) trying to fix it, before I realized that the way you fix it is
in Data Validation. I wish this option was offered in the error dialog.

What settings do you have in Data Validation? What was your exact error
message?

Data validation doesn't require an entry in date form - the parser
evaluates the entry before Data Validation ever sees it. Instead it
compares the entry to the criteria you set.

If you click on Error Message pane of the Data Validation dialog, you
can set the error message to whatever you wish. If a custom message was
set to say that the input must be in date form, then whoever set that
message made an error.

 

[CyberTaz]

Sorry, Irv, but - even though I appreciate your frustration - I
couldn't disagree with you more.

When trying to type text into a Date cell as set by Data Validation

The purpose of Data Validation is to prevent entry of data that doesn't
comply with the validation rules in cells intended for a specific type
of content & to advise the user that the entry does not comply.

there's a rather unhelpful warning that the input must be in date form

That's on whomever applied the validation. Both the Message & the
Behavior are determined by that individual.

before I realized that the way you fix it is in Data Validation

Sorry you weren't more familiar with the program's features, but now
that you have learned from the experience, perhaps it won't be as much
of a problem should it recur.

I wish this option was offered in the error dialog

What would be the purpose of trying to restrict data entry if you're
going to give the offender a direct & overt option to countermand the
rule?... Although, if the designer chooses to do so, the option to
override data validation can easily be included, without turning it
off.

Regards |:>)

 

[IRV]

CyberTaz, Your points are valid in many cases, I think. However, the
cells in my spreadsheet became limited by date not because I specified
them as such in the Data Validator, but because (if I recall correctly)
of settings I made in the List Manager. I later changed the List
Manager to normal cells, but the Data Validation restriction remained
and confused me. I can see how this can be a useful tool in most
circumstances.

The error was something along the lines of The input must be a date
later than 1/10/1904 (I don't remember exactly).

 

[CyberTaz]

Hi Irv-

I see what you are referring to, and your analysis is on the mark.

Turning off the List Manager _did_ leave the cells formatted for Date, so
that's why you got the "1/2/1904" error... That's different than what you
would have seen if Data Validation had been applied, although the List
Wizard gives the opportunity to set a validation rule. I had the impression
that perhaps someone else might have designed the sheet.

Just for future reference, you can avoid the problem if you click the
Columns button and select a format before you dispense with a list.
Otherwise you need to manually reformat the cells.

Glad you got it resolved!

Regards |:>)