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

[Zedrick13]

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]

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).

 

[Toadstool]

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
	A	B	C	D	E	F
1	20-Nov-20		Current	1 week ago	2 weeks ago	3 or more weeks ago
2	30-Nov-20		1	2	3	8
3	30-Nov-20
4	27-Nov-20
5	30-Nov-20
6	01-Dec-20
7	01-Dec-20
8	04-Dec-20
9	07-Dec-20
10	10-Dec-20
11	13-Dec-20
12	14-Dec-20
13	20-Dec-20
14	27-Dec-20
IRR

Cell Formulas
Range	Formula
C2	C2	=COUNTIFS($A:$A,">="&TODAY()-(WEEKDAY(TODAY(),2)),$A:$A,"<="&TODAY()-(WEEKDAY(TODAY(),2))+6)
D2	D2	=COUNTIFS($A:$A,">="&TODAY()-(WEEKDAY(TODAY(),2)*7),$A:$A,"<="&TODAY()-(WEEKDAY(TODAY(),2)*7)+6)
E2	E2	=COUNTIFS($A:$A,">="&TODAY()-(WEEKDAY(TODAY(),2)*14),$A:$A,"<="&TODAY()-(WEEKDAY(TODAY(),2)*14)+6)
F2	F2	=COUNTIFS($A:$A,"<="&TODAY()-(WEEKDAY(TODAY(),2)*21)+6)

 

[Zedrick13]

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
	A	B	C	D	E	F
1	20-Nov-20		Current	1 week ago	2 weeks ago	3 or more weeks ago
2	30-Nov-20		1	2	3	8
3	30-Nov-20
4	27-Nov-20
5	30-Nov-20
6	01-Dec-20
7	01-Dec-20
8	04-Dec-20
9	07-Dec-20
10	10-Dec-20
11	13-Dec-20
12	14-Dec-20
13	20-Dec-20
14	27-Dec-20
IRR

Cell Formulas
Range	Formula
C2	C2	=COUNTIFS($A:$A,">="&TODAY()-(WEEKDAY(TODAY(),2)),$A:$A,"<="&TODAY()-(WEEKDAY(TODAY(),2))+6)
D2	D2	=COUNTIFS($A:$A,">="&TODAY()-(WEEKDAY(TODAY(),2)*7),$A:$A,"<="&TODAY()-(WEEKDAY(TODAY(),2)*7)+6)
E2	E2	=COUNTIFS($A:$A,">="&TODAY()-(WEEKDAY(TODAY(),2)*14),$A:$A,"<="&TODAY()-(WEEKDAY(TODAY(),2)*14)+6)
F2	F2	=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]

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.

 

[Toadstool]

Zedrick13.xlsx
	A	B	C	D	E	F
1	20-Dec-20		Current	1 week ago	2 weeks ago	3 or more weeks ago
2	21-Dec-20		1	7	7	8
3	22-Dec-20
4	23-Dec-20
5	24-Dec-20
6	25-Dec-20
7	26-Dec-20
8	27-Dec-20
9	28-Dec-20
10	29-Dec-20
11	30-Dec-20
12	31-Dec-20
13	01-Jan-21
14	02-Jan-21
15	03-Jan-21
16	04-Jan-21
17	05-Jan-21
18	06-Jan-21
19	07-Jan-21
20	08-Jan-21
21	09-Jan-21
22	10-Jan-21
23	11-Jan-21
IRR (3)

Cell Formulas
Range	Formula
C2	C2	=COUNTIFS($A:$A,">="&TODAY()-(WEEKDAY(TODAY(),3)),$A:$A,"<="&TODAY()-(WEEKDAY(TODAY(),3))+6)
D2	D2	=COUNTIFS($A:$A,">="&TODAY()-(WEEKDAY(TODAY(),3))-7,$A:$A,"<="&TODAY()-(WEEKDAY(TODAY(),3))+6-7)
E2	E2	=COUNTIFS($A:$A,">="&TODAY()-(WEEKDAY(TODAY(),3))-14,$A:$A,"<="&TODAY()-(WEEKDAY(TODAY(),3))+6-14)
F2	F2	=COUNTIFS($A:$A,"<="&TODAY()-(WEEKDAY(TODAY(),3))+6-21)

 

[Zedrick13]

Zedrick13.xlsx
	A	B	C	D	E	F
1	20-Dec-20		Current	1 week ago	2 weeks ago	3 or more weeks ago
2	21-Dec-20		1	7	7	8
3	22-Dec-20
4	23-Dec-20
5	24-Dec-20
6	25-Dec-20
7	26-Dec-20
8	27-Dec-20
9	28-Dec-20
10	29-Dec-20
11	30-Dec-20
12	31-Dec-20
13	01-Jan-21
14	02-Jan-21
15	03-Jan-21
16	04-Jan-21
17	05-Jan-21
18	06-Jan-21
19	07-Jan-21
20	08-Jan-21
21	09-Jan-21
22	10-Jan-21
23	11-Jan-21
IRR (3)

Cell Formulas
Range	Formula
C2	C2	=COUNTIFS($A:$A,">="&TODAY()-(WEEKDAY(TODAY(),3)),$A:$A,"<="&TODAY()-(WEEKDAY(TODAY(),3))+6)
D2	D2	=COUNTIFS($A:$A,">="&TODAY()-(WEEKDAY(TODAY(),3))-7,$A:$A,"<="&TODAY()-(WEEKDAY(TODAY(),3))+6-7)
E2	E2	=COUNTIFS($A:$A,">="&TODAY()-(WEEKDAY(TODAY(),3))-14,$A:$A,"<="&TODAY()-(WEEKDAY(TODAY(),3))+6-14)
F2	F2	=COUNTIFS($A:$A,"<="&TODAY()-(WEEKDAY(TODAY(),3))+6-21)

You're really awesome. This is really helpful. I can't thank you enough.