I have a date in C1 and D1 such as 1/1/2010 and 1/7/2010 which is a 7 day period.
Then down the sheet for each 7 day period throughout the year.
In a list of inspections, each inspection date is entered in column E
In another column down the page I would like to count how many inspection dates fall between and including the two dates in column C and D
Counting Dates between Two Dates
-
- Lounger
- Posts: 38
- Joined: 26 Feb 2010, 14:01
Re: Counting Dates between Two Dates
An example with the count in G column.
I should have lowed the data and stated below Row 1, which I can adjust later.
I should have lowed the data and stated below Row 1, which I can adjust later.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Counting Dates between Two Dates
Let's say you insert headers in row 1.
In G2, enter the formula
=SUMPRODUCT(($E$2:$E$6>=C2)*($E$2:$E$6<=D2))
(adjusting $E$2:$E$6 to the full list of inspection dates)
Fill down as far as needed.
Alternatively, since you're using Excel 2007 or later:
=COUNTIFS($E$2:$E$6,">="&C2,$E$2:$E$6,"<="&D2)
See the attached version.
In G2, enter the formula
=SUMPRODUCT(($E$2:$E$6>=C2)*($E$2:$E$6<=D2))
(adjusting $E$2:$E$6 to the full list of inspection dates)
Fill down as far as needed.
Alternatively, since you're using Excel 2007 or later:
=COUNTIFS($E$2:$E$6,">="&C2,$E$2:$E$6,"<="&D2)
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 38
- Joined: 26 Feb 2010, 14:01
Re: Counting Dates between Two Dates
Exactly what I needed, thanks again!