If Statements with Dates

Simple, simple question. I have a list of dates and I need to calculate how many of the dates are before or between certain dates. I can't quite get my parameters correct.

Thank you.

I couldn't get that to work, I probably wasn't clear enough. Let's try this to start, I have a list of dates as follows:

15-Jul-08
31-Aug-08

28-Aug-08

21-Aug-07

And I'm just trying to get a count of the dates that are before August 2008 (hence 2).

And would it be OK to have empty rows within the date data?

Thanks.

Does anybody know how to do this? Thanks.

This is something of a sledgehammer to crack a nut...

Dates are stored by Excel as numbers, for example, today is 39766. To see this, type today's date in a cell, then format the cell to "general"...

When you insert a date, Excel converts it to a number, so it can add a week, take away a year, etc. Via formatting, it then converts it back to something you can understand (39766 means very little to me).

So to find dates earlier than today, you just need <39766!

The best way, in my opinion, of inserting this information (i.e. that number) is with =date(year,month,date) which you can see JB using in the first formula in his second post. An alternative is datevalue("datestring") e.g. datevalue("18/06/2008"). I wuoldn't recommend this because it is unreliable across different conventions (i.e. UK date style = dd/mm/yy, US date style = mm/dd/yy) which can be confusing. date() has no ambiguity.

In answer to this:

And I'm just trying to get a count of the dates that are before August 2008 (hence 2).

=COUNTIF(A1:A10,"<"&DATE(2008,8,1))

HTH

JB,

I like sumproduct formulae too, but they're actually quite slow and not always necessary; how about these revisions?:

=SUMPRODUCT((A1:A100>=B1)*(A1:A100<=C1))

=COUNTIF(A:A,"<="&C1)-COUNTIF(A:A,"<"&B1)

This is faster, you can see this means you also don't have to revise the range sizes all the time as countif can operate on the entire column.

=SUMPRODUCT(--(A1:A100<DATE(2008,8,1)))

and

=SUMPRODUCT(--(A1:A100<DATE(2008,8,1)))--(-COUNTBLANK(A1:A100))

Neither simple nor straightforward! (countif example given in previous post operates faster, is easier to understand, and accounts for blanks!

CC

Thanks guys.

I could actually get the sumproduct calculations to work but not the COUNTIF calcs. I'm more familiar with the COUNTIF as well but I keep getting an Err508 messages. Could if have something to do with the way excel reads dates as mentioned above? Any idea how to solve that issue?

Thanks.

Are your dates actual dates or are they dates stored as strings?