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,

Maybe this

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

Mike

"Newbie and Lost" wrote:

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

This worked :) Thanks so much!!

"Mike H" wrote:

Hi,

Maybe this

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

Mike

"Newbie and Lost" wrote:

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

"Mike H" wrote:
=IF(AND(NOW()=DATE(2009,8,1),NOW()<=DATE(2009,9,1 3)),200,0)

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 -----

"Mike H" wrote in message
...
Hi,

Maybe this

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

Mike

"Newbie and Lost" wrote:

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)

"Newbie and Lost" wrote:

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

=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" wrote:

hi jen,
in my opinion, your formula should be
=IF(AND(NOW()="08/03/2009",NOW()<="09/13/2009"),200,0)

"Newbie and Lost" wrote:

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

"YESHWANT JOSHI" wrote:
in my opinion, your formula should be
=IF(AND(NOW()="08/03/2009",NOW()<="09/13/2009"),200,0)

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 -----

"YESHWANT JOSHI" wrote in message
...
hi jen,
in my opinion, your formula should be
=IF(AND(NOW()="08/03/2009",NOW()<="09/13/2009"),200,0)

"Newbie and Lost" wrote:

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

A little more cryptic than the others...

=200*(ABS(TODAY()-40047.5)<22.5)

--
Rick (MVP - Excel)


"Newbie and Lost" wrote in message
...
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