Countif formula help

[Siyanna]

Hi Guys,

Please advise what i am doing wrong
I need to count how many dates are = to date in 01
the range compared is Col C. In Col C i have just dates i.e 15/05/2012
In O1 i have date + time however i just need the date.

I also need to compare how many dates are between 2 and 5 days and also > than 5 days.

I have tried this formula for the = part

=COUNTIF(C:C,date(year(O1,month(O1),day(O1))))

Many Thanks

 

[baitmaster]

You're missing a parenthesis DATE(YEAR(O1),MONTH(O1),DAY(O1))

You could just use INT(O1) instead, to remove the time element from the date

Answer to the "duration" question depends on (a) what version of Excel you're using, and (b) what you mean by "how many dates are between 2 and 5 days and also > than 5 days". I assume you mean a difference of these amounts from the value in O1


In Excel 07 or later you could use something like =COUNTIFS(C:C,">"&INT(O1)-5,C:C,"<"&INT(O1)-2)

In Excel 03 or earlier you'll need a different formula, let me know