If And using dates

  • Thread starter Thread starter Newbie and Lost
  • Start date Start date
N

Newbie and Lost

I am trying to write a formula so the if the current date NOW() is between
08/01/09 to 09/13/09 then put 200 otherwise put 0. See formula below. It
works when I am referencing a cell with those dates but it won't work if I
type the dates into the formula. Am I writing the dates incorrectly? In
Access I usually put #01/08/09# but I tried that format and it didn't work
either.

My formula:
=IF(AND(NOW()>=8/3/9,NOW()<=9/13/9),200,0)

Jen
 
hi jen,
in my opinion, your formula should be
=IF(AND(NOW()>="08/03/2009",NOW()<="09/13/2009"),200,0)
 
Mike H said:
=IF(AND(NOW()>=DATE(2009,8,1),NOW()<=DATE(2009,9,13)),200,0)
Click to expand...

Since the OP used NOW(), I suspect this is what she needs:

=IF(AND(NOW()>=DATE(2009,8,1),NOW()<DATE(2009,9,14)),200,0)

Note the change from "<=" to "<" and from 9/13/2009 to 9/14/2009.

Test by replacing NOW() with a cell reference, and enter 9/13/2009 23:59:59
into the cell to simulate the latest NOW() on 9/13/2009.

IMHO, the OP should use TODAY(), not NOW(). Then the intuitive formula
would work as Mike (and the OP) wrote it.


----- original message -----
 
=IF(AND(NOW()>="08/03/2009",NOW()<="09/13/2009"),200,0)
Your formula FAIL. It returns "0" instead of 200

Try it like this:

=IF(AND(NOW()>=--"08/03/2009",NOW()<=--"09/13/2009"),200,0)
or
=(TODAY()>=40028)*(TODAY()<=40069)*200
 
YESHWANT JOSHI said:
in my opinion, your formula should be
=IF(AND(NOW()>="08/03/2009",NOW()<="09/13/2009"),200,0)
Click to expand...

Certainly not! If you had tried it yourself today (8/19/2009), it would not
have returned 200 as intended.

Arguably, perhaps you meant:

=IF(AND(NOW()>=--"08/03/2009",NOW()<=--"09/13/2009"),200,0)

Note the addition of "--" to convert the date strings to date serial
numbers.

But that is deprecated because the correctness depends on Regional and
Language settings. In particular, the modified formula returns a #VALUE
error for --"09/13/2009" when the Regional and Language date setting is
d/MM/yy. Although that is obviously not the case for the OP, we do not know
if her worksheet might be sent to someone who has a different Regional and
Language setting. For that reason, DATE() is the better choice.

PS: In another posting, I also mentioned the problem with comparing NOW()
<= DATE(2009,9,13). I think the OP shoud use TODAY().


----- original message -----
 
Back
Top