Prev: Nesting Lookup Functions
Next: "AND FUNCTION" RETURNS FALSE WHEN CRITERIA IS TRUELY MET. HEL
From: gamn on 23 Mar 2010 16:47
I have a problem using AND function on date data.

In cell A2, I have date date "10/1/2009".

In cell A3, I have AND fuction as =AND(A2>=10/1/2009, A2<=10/24/2009). This
should return a TRUE but instead returns FALSE. Why?


Thank you, BB
From: Paul C on 23 Mar 2010 16:57
The issue is how you expressed your dates

Try =AND(A2>=DATE(2009,10,1),A2<=DATE(2009,10,24))

As written Excel does not recognize 10/1/2009 as a date, it does the math 10
divided by 1 divided by 2009
--
If this helps, please remember to click yes.


"gamn" wrote:

> I have a problem using AND function on date data.
>
> In cell A2, I have date date "10/1/2009".
>
> In cell A3, I have AND fuction as =AND(A2>=10/1/2009, A2<=10/24/2009). This
> should return a TRUE but instead returns FALSE. Why?
>
>
> Thank you, BB
From: Ron Rosenfeld on 23 Mar 2010 17:09
On Tue, 23 Mar 2010 13:47:01 -0700, gamn <gamn(a)discussions.microsoft.com>
wrote:

>In cell A2, I have date date "10/1/2009".
>
>In cell A3, I have AND fuction as =AND(A2>=10/1/2009, A2<=10/24/2009). This
>should return a TRUE but instead returns FALSE. Why?

because 10/1/2009 is not the same as "10/1/2009"

The latter, in A2, is interpreted as a date and stored as an integer probably
equal to 40087, depending on the date system you are using in Excel.

The former: 10/1/2009 is computed as 10�1�2009 or 0.004978.

To unambiguously enter a date into your formula, you should either use a cell
reference containing the date, or use the DATE function. eg:

=and(a2>=date(2009,10,1),a2<=date(2009,10,24))

--ron
 | 
Pages: 1
Prev: Nesting Lookup Functions
Next: "AND FUNCTION" RETURNS FALSE WHEN CRITERIA IS TRUELY MET. HEL