# Countif formula help

#### Siyanna

##### Well-known Member
Hi Guys,

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

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### baitmaster

##### Well-known Member
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

Replies
3
Views
112
Replies
8
Views
560
Replies
22
Views
660
Replies
0
Views
229
Replies
6
Views
407

1,196,022
Messages
6,012,909
Members
441,741
Latest member
abaz21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back