# Countif dates 0/1/2/3 and beyond weeks ago

#### Zedrick13

##### Board Regular
Hi everyone,

I have a table that has column A (IRR!A:A) with dates I want to use countif to count the number of days that fall in the current week, previous week, 2 weeks ago, and 3 and more weeks ago. I already figured out how to count for the current week using this formula: =COUNTIF(IRR!A:A,TODAY()+1-WEEKDAY(TODAY()-1)). Can you please help me with the formula for the previous week, 2 weeks ago, and 3 and more weeks ago? Thanks in advance.

#### Zedrick13

##### Board Regular
I think I know why now. The return type must be 3 instead of 2.
Thank you very much @Toadstool. I hope you can help me with one more thing. How do I define on this formula if I want to count the dates that are not just from 3 weeks ago but also beyond that. So for the last column above, I need to count dates that are 3 weeks or older from this week (still Mon - Fri).

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

##### Well-known Member
I can't see the issue except you had the explanaton in the wrong sequence.

Here's the calculation with a specific formula for each range:

Zedrick13.xlsx
ABCDEF
120-Nov-20Current1 week ago2 weeks ago3 or more weeks ago
230-Nov-201238
330-Nov-20
427-Nov-20
530-Nov-20
601-Dec-20
701-Dec-20
804-Dec-20
907-Dec-20
1010-Dec-20
1113-Dec-20
1214-Dec-20
1320-Dec-20
1427-Dec-20
IRR
Cell Formulas
RangeFormula
C2C2=COUNTIFS(\$A:\$A,">="&TODAY()-(WEEKDAY(TODAY(),2)),\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),2))+6)
D2D2=COUNTIFS(\$A:\$A,">="&TODAY()-(WEEKDAY(TODAY(),2)*7),\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),2)*7)+6)
E2E2=COUNTIFS(\$A:\$A,">="&TODAY()-(WEEKDAY(TODAY(),2)*14),\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),2)*14)+6)
F2F2=COUNTIFS(\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),2)*21)+6)

#### Zedrick13

##### Board Regular
I can't see the issue except you had the explanaton in the wrong sequence.

Here's the calculation with a specific formula for each range:

Zedrick13.xlsx
ABCDEF
120-Nov-20Current1 week ago2 weeks ago3 or more weeks ago
230-Nov-201238
330-Nov-20
427-Nov-20
530-Nov-20
601-Dec-20
701-Dec-20
804-Dec-20
907-Dec-20
1010-Dec-20
1113-Dec-20
1214-Dec-20
1320-Dec-20
1427-Dec-20
IRR
Cell Formulas
RangeFormula
C2C2=COUNTIFS(\$A:\$A,">="&TODAY()-(WEEKDAY(TODAY(),2)),\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),2))+6)
D2D2=COUNTIFS(\$A:\$A,">="&TODAY()-(WEEKDAY(TODAY(),2)*7),\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),2)*7)+6)
E2E2=COUNTIFS(\$A:\$A,">="&TODAY()-(WEEKDAY(TODAY(),2)*14),\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),2)*14)+6)
F2F2=COUNTIFS(\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),2)*21)+6)
Thanks again, @Toadstool. This helped me a lot. I really appreciate your time and effort.

#### Zedrick13

##### Board Regular
Something unexpected happened to the formulas when the year turned. The formula is now unable to properly count last year's dates. Can somebody please help? Thanks.

##### Well-known Member
Zedrick13.xlsx
ABCDEF
120-Dec-20Current1 week ago2 weeks ago3 or more weeks ago
221-Dec-201778
322-Dec-20
423-Dec-20
524-Dec-20
625-Dec-20
726-Dec-20
827-Dec-20
928-Dec-20
1029-Dec-20
1130-Dec-20
1231-Dec-20
1301-Jan-21
1402-Jan-21
1503-Jan-21
1604-Jan-21
1705-Jan-21
1806-Jan-21
1907-Jan-21
2008-Jan-21
2109-Jan-21
2210-Jan-21
2311-Jan-21
IRR (3)
Cell Formulas
RangeFormula
C2C2=COUNTIFS(\$A:\$A,">="&TODAY()-(WEEKDAY(TODAY(),3)),\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),3))+6)
D2D2=COUNTIFS(\$A:\$A,">="&TODAY()-(WEEKDAY(TODAY(),3))-7,\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),3))+6-7)
E2E2=COUNTIFS(\$A:\$A,">="&TODAY()-(WEEKDAY(TODAY(),3))-14,\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),3))+6-14)
F2F2=COUNTIFS(\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),3))+6-21)

#### Zedrick13

##### Board Regular
Zedrick13.xlsx
ABCDEF
120-Dec-20Current1 week ago2 weeks ago3 or more weeks ago
221-Dec-201778
322-Dec-20
423-Dec-20
524-Dec-20
625-Dec-20
726-Dec-20
827-Dec-20
928-Dec-20
1029-Dec-20
1130-Dec-20
1231-Dec-20
1301-Jan-21
1402-Jan-21
1503-Jan-21
1604-Jan-21
1705-Jan-21
1806-Jan-21
1907-Jan-21
2008-Jan-21
2109-Jan-21
2210-Jan-21
2311-Jan-21
IRR (3)
Cell Formulas
RangeFormula
C2C2=COUNTIFS(\$A:\$A,">="&TODAY()-(WEEKDAY(TODAY(),3)),\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),3))+6)
D2D2=COUNTIFS(\$A:\$A,">="&TODAY()-(WEEKDAY(TODAY(),3))-7,\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),3))+6-7)
E2E2=COUNTIFS(\$A:\$A,">="&TODAY()-(WEEKDAY(TODAY(),3))-14,\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),3))+6-14)
F2F2=COUNTIFS(\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),3))+6-21)
You're really awesome. This is really helpful. I can't thank you enough.

Replies
0
Views
77
Replies
1
Views
72
Replies
3
Views
82
Replies
6
Views
52
Replies
2
Views
84

1,128,126
Messages
5,628,858
Members
416,345
Latest member

### 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