counting valid dates in a range of cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there, I have a sheet that I am using to keep tabs on follow up interviews
in a research project. When the participant is interviewed I have either the
date it occurred or "client withdrawn" or some comment like this, or the cell
is blank.

I would like to count the number of cells with a valid date, without
specifying the date.
I would also be interested in a second formula where I can specify two dates
to count between.

many thanks for your valuable suggestions! (I did search first), Theo
 
Hi!

Try these:

To count all dates: (assumes the dates are in fact true Excel dates)

=COUNT(A1:A10)

To count dates within a range (inclusive):

B1 = start date = 1/1/2006
C1 = end date = 7/1/2006

=COUNTIF(A1:A10,">="&B1)-COUNTIF(A1:A10,">"&C1)

Or:

=SUMPRODUCT(--(A1:A10>=B1),--(A1:A10<=C1))

Biff
 
thanks, I thionk though that the first formula would count the comments as
well wouldn't it?. I need a count that ignores strings and blanks but does
count dates only?

cheers
 
TBA said:
thanks, I thionk though that the first formula would count the comments as
well wouldn't it?. I need a count that ignores strings and blanks but does
count dates only?
Click to expand...

If you think the first formula will count comments as well, the only way to
find out would be to try the formula. Did it count comments? <g>

Biff
 
ahh!, there you go, I was having a glass half empty moment, rather than just
believing the glass was actually full!- thanks for that, all working...
 
You're welcome!

Official explanation: The COUNT function counts numeric values only. It will
ignore text values and empty/blank cells. Dates are really numeric values
that are FORMATTED to look like a date.

6/28/2006 may look like a string but it's really the numeric value 38896
(provided that it hasn't been preformatted or entered specifically as a TEXT
entry).

Biff
 
Back
Top