counting cells that fall within a certain date range

[MR. NICE]

I feel I am close but I am not getting an accurate count. I have 100
activities and/or cells that have a date (the date represents when I need to
be finished with the activity). I would like to get a count of how many
activities each week I need to complete based on the dates that are in each
cell. Currently I am using
=COUNTIF($H$8:$H$107,"=<"+O6)+COUNTIF($H$8:$H$107,"=>"+O5) the O5 & O6 are
the date range. the formula is counting but it is only counting the
activities that fall ON the dates not within the range. What is up and can
anyone help? If my boss only knew how much time I put into this freakn'
formula..... Thanks

 

[David Biddulph]

What you were probably trying to ask for was
=COUNTIF($H$8:$H$107,"<="&O6)-COUNTIF($H$8:$H$107,"<"&O5)

 

[MR. NICE]

Yes I this is the formula I started out with and it returns the value of
zero. There are actually 11 activities that fall within this date range

 

[Fred Smith]

Then you need to check your data. Are your data true excel dates, or text
masquerading as dates?

Regards,
Fred.

 

[David Biddulph]

If it returns a value of zero then your data values are wrong, not the
formula.

Check the values in the range $H$8:$H$107 and in O5 and O6. See what
happens if you use Format/ Cells to reformat the cells. Will the values in
the cells change to and fro between, for example, 17 Jun 2008 and 17/6/08 if
you reformat the cells? If they don't, you've probably got text values
rather than real dates. Check for extraneous spaces or other non-printing
characters.

 

[MR. NICE]

The dates switch back and forth when I reformat them. They appear as numbers
when I select general (under the format choices). I still get Zero as my
solution but when I used a plus in front of the O5 and O6 I did at least get
a value. It does not appear that there is any spaces in the cells

 

[David Biddulph]

So when you see them as numbers when you format as General, are you sure
that you've got values which fall between the numbers you see in O5 and O6
(and is the number in O6 definitely larger than the number in O5)?
Check again that you've got the data in the cells where you thought you had
them, and that you haven't got the cell references wrong.

If you are still struggling to find out what you've done wrong, break the
formula down.
=COUNTIF($H$8:$H$107,"<="&O6) gives the number of dates which are less than
or equal to the end date in O6.
=COUNTIF($H$8:$H$107,"<"&O5) gives the number of dates which are less than
the starting date in O5 and which should therefore be omitted from the
result.
In each case, adjust the value in O5 or O6 and see whether it has the right
effect on your formula result.

The formula is simple, so it must be the data values which are wrong.

 

[MR. NICE]

How do I know if it is text disguised as text, if it is how do Change it to
true date format?

 

[MR. NICE]

yes something is definitely wrong my dates are right and when I broke it down
it does not even want to count any of them

 

[MR. NICE]

I got it wow... it must of had something to do with text.... I extrapolate
this data out of another piece of soft ware and it configures it as a lostus
123 wks file and in the conversion it must do something so I had copy the
data and paste it into an excel spreadsheet and it worked fine... I had just
opened it and saved as a spreadsheet for some reasons this did not work...
thanks for your help