Using COUNTIF to count dates stored as text

**keris** · 01-12-2011, 05:30 PM

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!

**Ron Coderre** · 01-12-2011, 05:38 PM

Here are two approaches that seem to work:

=SUMPRODUCT(--(--LEFT(B2:G2,4)>=2005))

or

=SUM(COUNTIF(B2:G2,{2005,2006,2007,2008,2009,2010,2011}&"*"))

Does that help?

**keris** · 01-13-2011, 08:49 AM

Those do help for that particular criterion - thanks! But what if I want the criterion to be a certain date? For example, how would I count the number of dates that are later than 2005-06-17? (I can just see myself needing to do this in the future.)

**NBVC** · 01-13-2011, 08:59 AM

Try then:

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

**keris** · 01-13-2011, 09:16 AM

NBVC, that gives me a #VALUE! error ("A value used in the formula is of the wrong data type").

**NBVC** · 01-13-2011, 09:19 AM

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?

**keris** · 01-14-2011, 11:45 AM

Nope...the dates are the only contents of that range. However, since you mentioned "valid" dates, I experimented just now by clearing the cells that contained 0001-01-01. With that done, your formula works.

Could you explain how you got it? I'm confused by the +0 and because I'm not sure of the order of operations for the test. Would it work for selecting a date in the middle of the year, such as 2005-06-17?

**NBVC** · 01-14-2011, 11:58 AM

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.