Count # of values that fall within a certain date range

F

freeb1893

Board Regular
Joined
Jul 30, 2012
Messages
233
Office Version
  1. 365
Platform
  1. Windows
I have a couple cells that have formulas to calculate the last day of this week and the last day of last week:

Cell D2 is =TODAY() + (7 - WEEKDAY(TODAY()))
Cell E2 is =D2-7

I have a column that has dates in it, it includes blanks, and is populated via an array INDEX formula. This is a sample of the column data:

Date/Time Status Changed
5/9/23 1:08 PM​
7/5/22 1:08 PM​
10/10/22 2:10 PM​
10/3/22 5:09 PM​

This is the formula that populates it:

{=IF(ISNA(INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Merge Done"='RAW Data'!$M$2:$M$10000)*($M3='RAW Data'!$B$2:$B$10000),0)-0,9)),"",INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Merge Done"='RAW Data'!$M$2:$M$10000)*($M3='RAW Data'!$B$2:$B$10000),0)-0,9))}

This is a sample of my RAW Data table I'm indexed to get my column of dates:

LABELPKEYNUMSUMMARYPNAMECREATEDUPDATEDRESOLUTIONDATECURRENT STATUSCHANGE AUTHORCHANGE DATEFIELD CHANGEDOLDSTRINGNEWSTRING
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed4/28/2023 10:15 AMassigneeSmith, John
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed4/28/2023 10:15 AMassigneeSmith, JohnWilliams, Jesse
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed4/28/2023 10:15 AMassigneeWilliams, JesseSmith, John
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed4/28/2023 10:16 AMstatusReady to DevelopDevelopment
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed5/3/2023 3:50 PMstatusDevelopmentReady for Code Review
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed5/3/2023 3:50 PMassigneeSmith, John
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed5/9/2023 10:52 AMassigneeWilliams, Jesse
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed5/9/2023 1:08 PMstatusMergeMerge Done
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed5/9/2023 1:08 PMassigneeWilliams, Jesse
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed6/1/2023 7:57 AMpriorityCriticalExternal Block
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed6/1/2023 8:01 AMpriorityExternal BlockBlocker
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed6/26/2023 11:15 AMpriorityBlockerExternal Block
E2CAT-33TECH: Coordinate with AutoTools team to fix the modelYearId endpointStory2/20/2023 9:14 AM##################################Completed8/14/2023 10:25 AMstatusReady for ReleaseCompleted
E2CEDGE-13283Vehicle payments batch jobs in TWS analysis (GM and Ford)Story3/28/2022 1:23 PM3/15/2023 6:09 PMCancelled3/28/2022 1:23 PMassigneeWilliams, Tom
E2CEDGE-13283Vehicle payments batch jobs in TWS analysis (GM and Ford)Story3/28/2022 1:23 PM3/15/2023 6:09 PMCancelled4/4/2022 8:35 PMassigneeWilliams, Tom
E2CEDGE-13283Vehicle payments batch jobs in TWS analysis (GM and Ford)Story3/28/2022 1:23 PM3/15/2023 6:09 PMCancelled5/26/2022 10:16 AMstatusOpenCancelled
E2CEDGE-13293QUOTE DELETION batch jobs in TWS analysisStory3/29/2022 2:07 PM3/15/2023 6:09 PMOpen3/29/2022 2:07 PMassigneeWilliams, TomGibbs, Harry
E2CEDGE-13293QUOTE DELETION batch jobs in TWS analysisStory3/29/2022 2:07 PM3/15/2023 6:09 PMOpen3/30/2022 8:41 AMassigneeGibbs, Harry
E2CEDGE-13645CAN_UPD_LICENSEEpic5/9/2022 4:28 PM8/16/2023 8:45 AM8/16/2023 8:45 AMCancelled8/16/2023 8:45 AMstatusOpenCancelled
E2CEDGE-13900Data Update Batch JobsUser Story6/27/2022 2:50 PM#################11/8/2022 7:55 AMCancelled11/8/2022 7:55 AMstatusOpenCancelled
E2CEDGE-13901Learn Batch job scheduling in TWSStory6/27/2022 3:42 PM#################Cancelled6/28/2022 7:51 AMassigneePalmer, Arnold
E2CEDGE-13901Learn Batch job scheduling in TWSStory6/27/2022 3:42 PM#################Cancelled7/27/2022 12:51 PMstatusGathering RequirementsCancelled
E2CEDGE-13901Learn Batch job scheduling in TWSStory6/27/2022 3:42 PM#################Cancelled7/27/2022 12:52 PMstatusCancelledCancelled
E2CEDGE-13901Learn Batch job scheduling in TWSStory6/27/2022 3:42 PM#################Cancelled11/8/2022 7:55 AMstatusOpenCancelled
E2CEDGE-13904Define the different types of batch jobsStory6/28/2022 7:47 AM#################Cancelled6/28/2022 7:49 AMassigneeDoe, Jane
E2CEDGE-13904Define the different types of batch jobsStory6/28/2022 7:47 AM#################Cancelled6/28/2022 7:56 AMassigneeDoe, JaneDoe, John
E2CEDGE-13904Define the different types of batch jobsStory6/28/2022 7:47 AM#################Cancelled6/30/2022 7:30 AMstatusPre-GroomingCancelled
E2CEDGE-13905List migration options for each batch job typeStory6/28/2022 7:55 AM#################Cancelled7/6/2022 9:18 AMpriorityMajorExternal Block
E2CEDGE-13905List migration options for each batch job typeStory6/28/2022 7:55 AM#################Cancelled7/18/2022 8:03 AMpriorityExternal BlockMajor
E2CEDGE-13905List migration options for each batch job typeStory6/28/2022 7:55 AM#################Cancelled7/18/2022 12:55 PMstatusGathering RequirementsCancelled
E2CEDGE-13906Validate if the List of Batch jobs is completeStory6/28/2022 7:59 AM#################Cancelled11/8/2022 7:56 AMstatusOpenCancelled
E2CEDGE-13907Validate the count of Long running jobsStory6/28/2022 8:00 AM#################Cancelled7/8/2022 9:36 AMassigneeGibbs, Harry
E2CEDGE-13907Validate the count of Long running jobsStory6/28/2022 8:00 AM#################Cancelled7/19/2022 9:16 AMstatusGathering RequirementsCancelled
E2CEDGE-13907Validate the count of Long running jobsStory6/28/2022 8:00 AM#################Cancelled7/27/2022 10:53 AMassigneeGibbs, HarryWilliams, Tom
E2CEDGE-13908Identify common themes or patterns of Job failureStory6/28/2022 8:03 AM#################Cancelled6/29/2022 10:24 AMassigneeWilliams, Tom
E2CEDGE-13908Identify common themes or patterns of Job failureStory6/28/2022 8:03 AM#################Cancelled7/25/2022 11:00 AMstatusGathering RequirementsCancelled
E2CEDGE-13908Identify common themes or patterns of Job failureStory6/28/2022 8:03 AM#################Cancelled7/26/2022 1:33 PMstatusCancelledCancelled
E2CEDGE-13908Identify common themes or patterns of Job failureStory6/28/2022 8:03 AM#################Cancelled7/27/2022 10:41 AMstatusCancelledCancelled
E2CEDGE-13908Identify common themes or patterns of Job failureStory6/28/2022 8:03 AM#################Cancelled7/27/2022 12:57 PMstatusCancelledCancelled
E2CEDGE-13909Identify common actions or type of actions taken at the end of a jobStory6/28/2022 8:05 AM#################Cancelled6/29/2022 10:33 AMassigneeGibbs, Harry
E2CEDGE-13909Identify common actions or type of actions taken at the end of a jobStory6/28/2022 8:05 AM#################Cancelled7/5/2022 1:08 PMstatusReady to DevelopDevelopment
E2CEDGE-13909Identify common actions or type of actions taken at the end of a jobStory6/28/2022 8:05 AM#################Cancelled7/5/2022 1:08 PMstatusDevelopmentReady for Code Review
E2CEDGE-13909Identify common actions or type of actions taken at the end of a jobStory6/28/2022 8:05 AM#################Cancelled7/5/2022 1:08 PMstatusMergeMerge Done
E2CEDGE-13909Identify common actions or type of actions taken at the end of a jobStory6/28/2022 8:05 AM#################Cancelled7/5/2022 1:09 PMassigneeGibbs, Harry
E2CEDGE-13909Identify common actions or type of actions taken at the end of a jobStory6/28/2022 8:05 AM#################Cancelled7/5/2022 1:28 PMassigneeGibbs, Harry
E2CEDGE-13909Identify common actions or type of actions taken at the end of a jobStory6/28/2022 8:05 AM#################Cancelled7/5/2022 1:29 PMassigneeGibbs, Harry
E2CEDGE-13909Identify common actions or type of actions taken at the end of a jobStory6/28/2022 8:05 AM#################Cancelled7/5/2022 1:29 PMstatusMerge DoneCancelled
E2CEDGE-13910Identify common actions or type of actions before a job runsStory6/28/2022 8:06 AM#################Cancelled7/5/2022 1:30 PMassigneeGibbs, Harry
E2CEDGE-13910Identify common actions or type of actions before a job runsStory6/28/2022 8:06 AM#################Cancelled7/11/2022 9:15 AMstatusGathering RequirementsCancelled
E2CEDGE-13911Identify techniques used by jobs in wait mode like pollingStory6/28/2022 8:09 AM#################Cancelled8/2/2022 12:56 PMstatusOpenCancelled
E2CEDGE-13952EDGE class structure analysisStory7/8/2022 10:34 AM#################Cancelled7/8/2022 10:35 AMassigneeDoe, John
E2CEDGE-13952EDGE class structure analysisStory7/8/2022 10:34 AM#################Cancelled7/18/2022 9:24 AMstatusGathering RequirementsCancelled

In the end, I'm trying to count the number of records in my indexed dates column that that falls within my date range between the last day of this week, and last day of last week.

Anyone know how to put that into a formula?

I've been trying to use COUNTIFS but I keep getting a result of zero regardless of tweaks, and assume it's because of how my date range is based on formulas and not text input dates, or that my dates column is based on that INDEX formula, but either way it's hindering me from leveraging COUNTIFS and DATE functions to count the number of records that falls within my date range. Thanks in advance
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Its a bit odd, but when I first used this formula (my dates are in B5:b20), it also gave me a zero (when I had some spaces between dates ie. blank cells.

I'm using manual dates rather than your formulas.

So I populated the full column (ie. no blank cells between each date), and it came to life ?

Sorry I can't explain why, as now if I go delete some dates in that column to reveal blanks, it keeps working.

Rob

invoice_names.xlsm
ABCDEF
1
226/08/202319/08/2023
3
4
508/08/20232
6
719/08/2023
8
9
10
11
12
13
14
15
16
1723/08/2023
18
19
2015/03/2022
Sheet1
Cell Formulas
RangeFormula
D2D2=TODAY()+(7-WEEKDAY(TODAY()))
E2E2=D2-7
F5F5=COUNTIFS(B5:B20, ">=" & E2, B5:B20,"<="&D2)
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
Counting when record falls within a dynamically updating date range, additional matching criteria
Replies
1
Views
356
awoohaw
awoohaw
F
  • Question
Looking to index some data based on multiple criteria, then index further data based on the 1st indexed data results
2
Replies
13
Views
501
KRice
K
F
  • Question
Would like to index different data points based on multiple criteria, and then index additional data in the same columns based on date/time proximity
Replies
4
Views
361
freeb1893
F
E
  • Solved
Lookup value from range when name matches AND date falls within date range
Replies
4
Views
693
Alex Blakenburg
A
F
  • Question
Enhance simply subtract of date/times of time spent, to including indexing and subtracting time blocked from another table (with multiple criteria)
Replies
0
Views
227
freeb1893
F

Forum statistics

Threads
1,215,073
Messages
6,122,970
Members
449,095
Latest member
Mr Hughes

Share this page

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