Validation Criteria of "Custom" fails to work

[0-0 Wai Wai ^-^]

Additional information:
- I'm using Office XP
- I'm using Windows XP

===========

In Validation / Settings Tab / Validation Criteria / "Allow" Box / Custom:
I type in a formula as something like as follows:
=day(A2)-today()
OR
=J3-J1

Even if the cell (where validation applys) is invalid, there is no red circle
around.
I clicked "formula auditing toolbar | Circle Invalid Data", but it still won't
work.

Do you know why?
How to use "custom" validation criteria?

 

[JE McGimpsey]

DAY() will return a number between 1 and 31, depending on the day of the
month. TODAY() returns an integer offset from the base date (e.g., 38114
for 7 May 2004 using the 1900 date system), so DAY() - TODAY() will
always return a non-zero value (i.e., always allow entry).

What are you trying to accomplish?

 

[pauluk]

First can you explan what extactly you trying to achieve.

It looks like you are trying to take todays day 7 and take ay the date

so 7 - 7/05/04 but then that would give a minus valu and you could n
get a date from that.

if its =TODAY()-DAY()

then you may get a date. The valdation tool only alwyas a specifi
input into that cell\cells. ie if you use =TODAY() you can only put th
same date is as the the computer clock is set to

 

[0-0 Wai Wai ^-^]

DAY() will return a number between 1 and 31, depending on the day of the
month. TODAY() returns an integer offset from the base date (e.g., 38114
for 7 May 2004 using the 1900 date system), so DAY() - TODAY() will
always return a non-zero value (i.e., always allow entry).

What are you trying to accomplish?

In the example, I'm trying to calculate the no. of days between a specific date
and today

Eg:
Today: 1990/4/2
End_Date: 1990/4/7 (in cell A2)

=Today()-Day(A2) = 5
(it means 5 days between today and end date)


But anyway, how to make "custom" works?
It fail to check any number when I use "custom". Even if I enter some other
formula eg:
=J3-J1

 

[0-0 Wai Wai ^-^]

Sorry, some silly mistakes made.
They are:

Eg:
Today: 1990/4/2
End_Date: 1990/4/7 (in cell A2)

=Today()-Day(A2) = 5
(it means 5 days between today and end date)

The formula should be reverse as:
=Day(A2)-Today()

 

[0-0 Wai Wai ^-^]

First can you explan what extactly you trying to achieve.
Sure.
In the example, I'm trying to calculate the no. of days between a specific date
and today

Eg:
Today: 1990/4/2
End_Date: 1990/4/7 (in cell A2)

=Day(A2)-Today() = 5
(it means 5 days between today and end date)

Shortly, I would like to count down the days left.

It looks like you are trying to take todays day 7 and take ay the date

so 7 - 7/05/04 but then that would give a minus valu and you could no
get a date from that.

if its =TODAY()-DAY()

You, like me in my previous reply, reverse the formula which is wrong.

But anyway, The main focus is not the formula. Rather I would like to know why
the validation function fails to work if I choose "custom", and enter any
formula in the box provided.

Eg:
=J3-J1


Do you know how to use "custom" as a validation criterion?

 

[Peo Sjoblom]

Only way is to use

=TODAY()-Date_you_want_to_check

it will always format as dates so you need to change in to General
If not you can use

=TEXT(B1-A1,"General")

however that will be text

 

[JE McGimpsey]

First can you explan what extactly you trying to achieve.

Sure.
In the example, I'm trying to calculate the no. of days between a specific
date
and today

Eg:
Today: 1990/4/2
End_Date: 1990/4/7 (in cell A2)

=Day(A2)-Today() = 5
(it means 5 days between today and end date)

Shortly, I would like to count down the days left.[/QUOTE]

Then use

=A2 - TODAY()

and format the cell as General.

 

[0-0 Wai Wai ^-^]

Then use

=A2 - TODAY()

and format the cell as General.

Hi, did I lose you?
What I really wish to focus is:

I wish to use data validation for a particular cell. But it doesn't work if I
choose "custom" in the "allow" box.

1) In Data | Validation | Settings Tab | Validation Criteria, there is an
"Allow" Box.
2) I chose "custom", there is a formula.
3) I type in some formula, eg:
=J3 (Just Reference)
=J3-J1 (Reference-type formula)
=round(J1) (Function-type formula)

None of them can activate the validation function.
No matter I enter any number, it doesn't stop me.
What's more, even if I use formula auditing toolbar and clicked the icon -
"Circle Invalid Data", it still won't work.

Do you know why?
How to use data validation of "custom"?

 

[0-0 Wai Wai ^-^]

Only way is to use

=TODAY()-Date_you_want_to_check

it will always format as dates so you need to change in to General
If not you can use

=TEXT(B1-A1,"General")

however that will be text

Hi, did I lose you?
That's not really I wish to know.
The main question I'm asking from the far beginning is:

I wish to use data validation for a particular cell. But it doesn't work if I
choose "custom" in the "allow" box.

1) In Data | Validation | Settings Tab | Validation Criteria, there is an
"Allow" Box.
2) I chose "custom", there is a formula.
3) I type in some formula, eg:
=J3 (Just Reference)
=J3-J1 (Reference-type formula)
=round(J1) (Function-type formula)

None of them can activate the validation function.
No matter I enter any number, it doesn't stop me.
What's more, even if I use formula auditing toolbar and clicked the icon -
"Circle Invalid Data", it still won't work.

Do you know why?
How to use data validation of "custom"?

PS: Sorry for misleading caused by me.

 

[Peo Sjoblom]

You have to compare the validation cell with the criteria and if the
criteria is
J3 and the validation cell is let's say A3, then you need to use

=A3=J3

 

[0-0 Wai Wai ^-^]

All solved.
One final question related to this.

If the validation cell contains value only, when we input a wrong value in the
cell, the validation checker will stop us from inputting.

However if it is a formula, eg:

A1 10
A2 8
A3 6
A4 sum(A1:A3) [value=24]

Validation: =A4=24

So afterward, if I change any value from A1 to A3 and make the value of A4
unequal to 24, eg:

A1 **9**
A2 8
A3 6
A4 sum(A1:A3) [value=23]

Validation: =A4=24

It won't stop/warn me in that way.
The only thing I can do is to check by myself using the formula auditing toolbar
and clicking the icon - "Circle Invalid Data".

Do you know if there is any workaround for this?