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

Zedrick13

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

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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())
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0
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: 5
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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
Back
Top