=COUNTIF(ALL!AC2:AC1000,"<NOW()-21")

This is the formula I'm using. I'm trying to determine how many dates
in column AC are more than three weeks ago. It's returning a 0 even
though it shouldn't. Can anyone see why? THanks.


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=555028

Nel post
*Spreadsheet* ha scritto:

=COUNTIF(ALL!AC2:AC1000,"<NOW()-21")

This is the formula I'm using. I'm trying to determine how many dates
in column AC are more than three weeks ago. It's returning a 0 even
though it shouldn't. Can anyone see why? THanks.


Hi,

try this:

=COUNTIF(A2:A49,"<"&TODAY()-21)

in this case you should prefer TODAY instead of NOW, because you don't need
the time, just th day.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

do it this way and use today instead of now
=COUNTIF($A$2:$A$7,"<"&TODAY()-21)

--
Don Guillett
SalesAid Software

"Spreadsheet"
wrote in message
...

=COUNTIF(ALL!AC2:AC1000,"<NOW()-21")

This is the formula I'm using. I'm trying to determine how many dates
in column AC are more than three weeks ago. It's returning a 0 even
though it shouldn't. Can anyone see why? THanks.


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile:
http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=555028

Thanks for replying. I'm now using
=COUNTIF(ALL!AC2:AC1000,"<"&TODAY()-21)

However, I'm having another problem. What I need to do is this:

Of all of the dates more than three weeks ago in column AC, how many of
those have a blank cell in column AG?


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=555028

Nel post
*Spreadsheet* ha scritto:

Thanks for replying. I'm now using
=COUNTIF(ALL!AC2:AC1000,"<"&TODAY()-21)

However, I'm having another problem. What I need to do is this:

Of all of the dates more than three weeks ago in column AC, how many
of those have a blank cell in column AG?


You can use this one:

=SUMPRODUCT((ALL!AC2:AC1000<(TODAY()-21))*(ALL!AG2:AG1000=""))

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Franz,

I'm not sure why you suggested SUMPRODUCT. I'm only interested in a
count.


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=555028

Nel post
*Spreadsheet* ha scritto:

Franz,

I'm not sure why you suggested SUMPRODUCT. I'm only interested in a
count.

Trust me. This *is* a count...


--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

The NOW formula should not be in quotes.

=COUNTIF(ALL!AC2:AC1000,"<"&NOW()-21)

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=555028