Excel formual to count dates in a column

[deaconj999]

Hello,

At present I am using the following formula to count the number of
date entries in a column,

=COUNT($AL$1000:$AL$5000,"<=today()")

I would like to know how to modify it so that it counts the number of
dates entered that have values from todays date but counting those
that only fall between today and the last 3 months.

Is there anyone out there that can amend my formula
please...................?

Many Thanks

Joe..........still learning................

 

[Dave Peterson]

First, I bet you meant:
=COUNTif($AL$1000:$AL$5000,"<="&today())

You could try:
=COUNTIF($AL$1000:$AL$5000,
">="&DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())))
-COUNTIF($AL$1000:$AL$5000,">"&TODAY())

You may want to play with >= or > depending on if you want to include/excluded
the end dates.

 

[Roger Govier]

Hi Joe

I guess you meant to type COUNTIF not COUNT
=COUNTIF($AL$1000:$AL$5000,"<=today()")


I think I would put the 2
Try
=COUNTIF($AL$1000:$AL$5000,"<="&today())-COUNTIF($AL$1000:$AL$5000,"<="&today()-91)
or
=COUNTIF($AL$1000:$AL$5000,"<="&today())-
COUNTIF($AL$1000:$AL$5000,"<="&DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))

I think I would put the dates in 2 separate cells
In A1=TODAY()
in B1=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))

=COUNTIF($AL$1000:$AL$5000,"<="&A1)-COUNTIF($AL$1000:$AL$5000,"<="&B1)

Be aware that the Date calculation taking 3 months away from a date can
throw up wrong results e.g.
With today as 31/05/2007, the formula would give 03/03/2007

Taking away 91 days isn't totally accurate, but may be close enough.
If you use 2 separate cells, then you could just type into each the
actual dates you wanted to use.

 

[Ragdyer]

I'm very interested in how you were able to return correct answers using
your formula of COUNT() ... unless ... of course, you really meant
COUNTIF()!

Months are a very "arbitrary" encompassing number.

Without using a specific date, since Today() *does change*, how about using
days instead?

=SUMPRODUCT(($AL$1000:$AL$5000<=TODAY())*($AL$1000:$AL$5000>=TODAY()-90))