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

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
94
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
Joined
Sep 8, 2018
Messages
94
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).
 

Some videos you may like

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

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,403
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Sep 8, 2018
Messages
94
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
Joined
Sep 8, 2018
Messages
94
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,403
Office Version
  1. 2016
Platform
  1. Windows
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)
 
Solution

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
94
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.
 

Watch MrExcel Video

Forum statistics

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

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top