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.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
Hi Zedrick13,

Does this do what you ask?

Zedrick13.xlsx
ABCDEF
120-Nov-20Weeks:123
223-Nov-20Count:259
324-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
D2:F2D2=COUNTIFS($A:$A,">"&TODAY()-D$1*7,$A:$A,"<="&TODAY())
 

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
94
Hi Zedrick13,

Does this do what you ask?

Zedrick13.xlsx
ABCDEF
120-Nov-20Weeks:123
223-Nov-20Count:259
324-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
D2:F2D2=COUNTIFS($A:$A,">"&TODAY()-D$1*7,$A:$A,"<="&TODAY())
By the way, looking into the formula again it doesn't seem to add up properly. Manually counting the dates, don't you think the result should be like this:

Monday as the start of the week (preferred)
0 week (21-Dec to 27-Dec) = 1
1 week ago (14-Dec to 20-Dec) = 2
2 weeks ago (7-Dec to 13-Dec) = 3
3 weeks and more ago (earlier dates to 6-Dec) = 8

Sunday as the start of the week
0 week (20-Dec to 26-Dec) = 1
1 week ago (13-Dec to 19-Dec) = 2
2 weeks ago (6-Dec to 12-Dec) = 2
3 weeks and more ago (earlier dates to 5-Dec) = 8
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I misunderstood. I'll put the date ranges as two rows to make checking and the formula simpler.

Zedrick13.xlsx
ABCDEFG
120-Nov-20Start:21-Dec-2014-Dec-2007-Dec-2030-Nov-20
230-Nov-20End:27-Dec-2020-Dec-2013-Dec-2006-Dec-20
330-Nov-20Weeks:0123
427-Nov-20Count:1236
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
D1:G1D1=TODAY()-(WEEKDAY(TODAY(),2)*(D$3*7))
D2:G2D2=D1+6
D4:G4D4=COUNTIFS($A:$A,">="&D$1,$A:$A,"<="&D$2)
 

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
94
I misunderstood. I'll put the date ranges as two rows to make checking and the formula simpler.

Zedrick13.xlsx
ABCDEFG
120-Nov-20Start:21-Dec-2014-Dec-2007-Dec-2030-Nov-20
230-Nov-20End:27-Dec-2020-Dec-2013-Dec-2006-Dec-20
330-Nov-20Weeks:0123
427-Nov-20Count:1236
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
D1:G1D1=TODAY()-(WEEKDAY(TODAY(),2)*(D$3*7))
D2:G2D2=D1+6
D4:G4D4=COUNTIFS($A:$A,">="&D$1,$A:$A,"<="&D$2)
I really appreciate your help. But, is there a way to do this without referencing the dates? I need the formulas to automatically adjust based on the current date. For example, on the 4th of January, I need the formula to automatically recognize that the 27th would be for the past week. If we are to reference the date ranges, I would then need to always update them. Sorry if I'm asking for too much. Just wanna know if this is possible.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You did not enter the date ranges; they were calculated based on today.

If you'd rather just have a formula then does this work?

Zedrick13.xlsx
ABCDEFG
120-Nov-20Weeks:0123
230-Nov-20Count:1236
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
D2:G2D2=COUNTIFS($A:$A,">="&TODAY()-(WEEKDAY(TODAY(),2)*(D$1*7)),$A:$A,"<="&TODAY()-(WEEKDAY(TODAY(),2)*(D$1*7))+6)
 

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
94
You did not enter the date ranges; they were calculated based on today.

If you'd rather just have a formula then does this work?

Zedrick13.xlsx
ABCDEFG
120-Nov-20Weeks:0123
230-Nov-20Count:1236
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
D2:G2D2=COUNTIFS($A:$A,">="&TODAY()-(WEEKDAY(TODAY(),2)*(D$1*7)),$A:$A,"<="&TODAY()-(WEEKDAY(TODAY(),2)*(D$1*7))+6)
Something is still strange in the formula and the results you showed for some reason. Looking at column A, the result should be something like the below, right?

0 week - 1
1 week - 2
2 weeks - 3
3 or more weeks - 8

Or, was there something I missed?
 

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
94
You did not enter the date ranges; they were calculated based on today.

If you'd rather just have a formula then does this work?

Zedrick13.xlsx
ABCDEFG
120-Nov-20Weeks:0123
230-Nov-20Count:1236
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
D2:G2D2=COUNTIFS($A:$A,">="&TODAY()-(WEEKDAY(TODAY(),2)*(D$1*7)),$A:$A,"<="&TODAY()-(WEEKDAY(TODAY(),2)*(D$1*7))+6)
Also, I'm getting a different result when replicating what you did for some reason. Please see attached screenshot.
 

Attachments

  • Countif dates 0,1,2 or 3 and beyond weeks ago.png
    Countif dates 0,1,2 or 3 and beyond weeks ago.png
    20.2 KB · Views: 3

Watch MrExcel Video

Forum statistics

Threads
1,127,342
Messages
5,624,114
Members
416,012
Latest member
rockermom59

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