Excel 2007 : Using COUNTIF to count dates stored as text

I have several Excel 2007 spreadsheets containing dates stored as text in yyyy-mm-dd format. I'd like a formula that will count the number of dates in a row. For example, these are the contents of columns A-G in rows 1 & 2:

ID#.......Date1...............Date2...............Date3................Date4................Date5...............Date6
474......2000-06-18.....2000-09-10.....2002-07-28......2005-12-04.....2006-05-14......0001-01-01

I want to count the number of dates in this row that are 2005 or later, so I tried to use this formula in cell H2:

=COUNTIF(B2:G2,">=2005-01-01")

I expected this to evaluate to 2, but instead it came out as 0. I wondered if maybe my syntax was wrong, so I tried this:

=COUNTIF(B2:G2,">="&"2005-01-01")

but that also came out to 0. When I tried this:

=COUNTIF(B2:G2,">=""2005-01-01")

it came out to 6.

I've always been able to compare dates stored as text, so I'm not sure why COUNTIF isn't working properly. What am I missing? The sheer volume of dates involved makes converting them from text format to date format highly impractical.

Thanks!

Here are two approaches that seem to work:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Does that help?

Try then:

=SUMPRODUCT(--(B2:G2+0>="2005-01-01"+0))

It assumes that all the text strings in B2:G2 are valid dates formatted as yyyy-mm-dd

do you have other text in that range?

The +0 coerces text strings to numbers (if they can't be converted to numbers, you get the #VALUE error).

The order of operations within the parentheses is... coerce each range with the +0, then check if the components of the left range is greater than or equal to the right components.

Then the -- is another coercer (could have used +0 at the other end). This coerces the TRUE/FALSE results from the >= checks to 1/0 respectively, then Sumproduct sums the results.