I need to count cells withdates in theme in a column. So that would be a
CountA function; but only if the values in the cells are within a certain
date range, a COUNTIF function. Here's what I thought:
=COUNTIF('All Employees'!O1351:O1364,"12/31/05,<2/1/06")
It returns a zero, which I know is not correct, as I checked it on a smaller
sample.

What am I doing wrong?

I also tried a SumIF function, but because Excel works on the premise that
all dates are really number of days since the turn of the century (1900), it
returns a ridiculous number.

This one seems to work:
=((COUNTIF('All Employees'!O1351:BC1364,"12/31/05"))-(COUNTIF('All
Employees'!O1361:BC1364,"2/1/06")))

Think this is OK?

Try

=COUNTIF('All Employees'!O1351:O1364,""&DATE(2005,12,31))-COUNTIF('All
Employees'!O1351:O1364,"="&DATE(2006,2,1))

or


=SUMPRODUCT(--('All Employees'!O1351:O1364--"2005-12-31"),--('All
Employees'!O1351:O1364<--"2006-02-01"))


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"audreyglennette" wrote in
message ...
I need to count cells withdates in theme in a column. So that would be a
CountA function; but only if the values in the cells are within a certain
date range, a COUNTIF function. Here's what I thought:
=COUNTIF('All Employees'!O1351:O1364,"12/31/05,<2/1/06")
It returns a zero, which I know is not correct, as I checked it on a
smaller
sample.

What am I doing wrong?

I also tried a SumIF function, but because Excel works on the premise that
all dates are really number of days since the turn of the century (1900),
it
returns a ridiculous number.

This one returns a #VALUE! error:
=COUNTIF('All Employees'!O1351:O1364,""&DATE(2005,12,31))-COUNTIF('All
Employees'!O1351:O1364,"="&DATE(2006,2,1))

This one retuens a #REF! error:
=SUMPRODUCT(--('All Employees'!O1351:O1364--"2005-12-31"),--('All
Employees'!O1351:O1364<--"2006-02-01"))

Any other ideas? Please?

Then your dates are not Excel dates but text so you can't really use a less
than condition
Btw, the second formula should not return a ref error unless copied and
pasted incorrectly, it should return zero since the dates are not numerical.
To use the formulas you must use "real" dates that Excel understand as
numbers in O1351:O1364


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"audreyglennette" wrote in
message ...
This one returns a #VALUE! error:
=COUNTIF('All Employees'!O1351:O1364,""&DATE(2005,12,31))-COUNTIF('All
Employees'!O1351:O1364,"="&DATE(2006,2,1))

This one retuens a #REF! error:
=SUMPRODUCT(--('All Employees'!O1351:O1364--"2005-12-31"),--('All
Employees'!O1351:O1364<--"2006-02-01"))

Any other ideas? Please?

What do you mean real dates? I am entering them as 12/1/05 and having the
cell display as Dec-06 for conciseness. Is this wrong?

Are you sure they are dates, compare them like =O1364-O1351
do you get a number as result or do you get the value error, if you get a
number they are real excel dates, if you get the error they are text and
cannot be used in calculations. If you don't have any leading or trailing
spaces you can convert them by copying an empty cell, select the dates and
do edit paste special and select add. However since you say you enter them
like 12/1/05 and use formatting to display as Dec-05 (I assume you had a
typo since 12/1/05 should not be Dec-06) then they are dates

Try

=COUNTIF('All Employees'!O1351:O1364,""&E1)-COUNTIF('All
Employees'!O1351:O1364,"="&F1)

and put the dates 12/31/05 in E1 and 02/01/06 in F1, make sure they are
dates (widen the column and see that they are right aligned without any
formatting applied) if you get zero it's text, if you get an error
then there's an error in the range itself or the formula has been copied and
pasted incorrectly


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"audreyglennette" wrote in
message ...
What do you mean real dates? I am entering them as 12/1/05 and having the
cell display as Dec-06 for conciseness. Is this wrong?