count if date appears between dates 1

[robcarr]

Hi,

is it possible to count number of dates within a range

column c has starts dates
column d has end dates

I need to count all instances of date within a date range

date ranges starts column c - 20/08/09
date ranges ends column d - 22/08/09

if this was repeated 3 times in the column I would want to see the following result

3 - 20/08
3 - 21/08
3 - 22/08

i need to find out how many people are off for any particular day.

=SUMPRODUCT(($C$7:$C$800>=I10)*($D$7:$D$800<=I11))

column I has dates to look for.

I am using the above formula but this only looks at the start and end dates within the column and counts those, I need to be able to count if the date falls between the start and end date.

hope this makes sense.




Hope this is of use, Rob.

 

[SkipVought]

Hi,

You are not specific enough about the data on your sheet and what results you are expecting.

Post a specific example of a range of data, the actual result you get, and the expected result that you're not getting.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

column c has a date
column d has a date

as per 1st post

cell c7 has 20/08/09
cell d7 has 22/08/09
in cell i10 i have the date to check 20/08/09
in cell i11 i have the date to check 21/08/09
in cell i12 i have the date to check 22/08/09

i would like to see in cell J10 the total times 20/08/09 appears in any of the date ranges in column c and d

column C and D have approx 40 sets of date ranges between 20/08 and 30/11

each row in columnc c and d is a holiday request.

I need to know how many people have requested a particular day off.





Hope this is of use, Rob.

 

[SkipVought]

EXACTLY WHAT is your expected result for this example. I just need a NUMBER.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

i need to find out how many people are off for any particular day.


Hope this is of use, Rob.

 

[SkipVought]

Aggghhhh!

With YOUR example, my NUMBER is 1 for each date in column I...
[tt]
=SUMPRODUCT(--($C$7:$C$10<=I10)*($D$7:$D$10>=I10))
[/tt]
modify to fit your range.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

i thought this was clear enough

date ranges starts column c - 20/08/09
date ranges ends column d - 22/08/09

if this was repeated 3 times in the column I would want to see the following result

3 - 20/08
3 - 21/08
3 - 22/08

21/08 doesnt appears in cell but appears in a range,

how can i count number of instances of a date in column c and D



Hope this is of use, Rob.

 

[SkipVought]

I already gave you a valid answer.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

it is not showing the answer I am expecting.

see my previous post

Hope this is of use, Rob.

 

[SkipVought]

That is EXACTLY why I asked you to give me a concrete example and what you expected.

I STILL do not know EXACTLY what you expect.

Please be CLEAR, CONCISE and COMPLETE.

Here's what I got...
[tt]
8/20/2009 1
8/21/2009 1
8/22/2009 1
8/23/2009 0
8/24/2009 0
8/25/2009 0
[/tt]
Exactly what do you expect?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

try again

c7 - 20/08
d7 - 22/08
c8 - 20/08
d8 - 22/08
c9 - 20/08
d9 - 22/08

i then have in i7 20/08 j7 shows 3
in i8 i have 21/08 j8 shows 0
in i9 i have 22/08 shows 3

how can i get j8 to show 3 as this date is between the start and end dates that appear in columns c and D



Hope this is of use, Rob.

 

[SkipVought]

When I have 3 rows of data as you posted, my results are...
[tt]
8/19/2009 0
8/20/2009 3
8/21/2009 3
8/22/2009 3
8/23/2009 0
[/tt]
Is that not what you want?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

not working my end

range data

col c col d
20-Aug 20-Aug
20-Aug 25-Aug
20-Aug 21-Aug
20-Aug 20-Aug
21-Aug 24-Aug
21-Aug 21-Aug
21-Aug 21-Aug
21-Aug 25-Aug
21-Aug 23-Aug
21-Aug 23-Aug

results in column J

20-Aug 5
21-Aug 8

I would need to see on this range

20-Aug 4
21-Aug 8

looks like if start and end date are same it counts it twice, but nearly there.






Hope this is of use, Rob.

 

[SkipVought]

Please copy & paste 1) your formula and 2) post the cell it is copied from.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

oops i am being special the formula works that you posted, i just cant count upto five.

thanks for your help skip

I am having a special moment, glad i only have 1 hour to go.

rob.

Hope this is of use, Rob.

 

[SkipVought]

My results with your latest example
[tt]
8/19/2009 0
8/20/2009 4
8/21/2009 8
8/22/2009 5
8/23/2009 5
8/24/2009 3
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Rob,

FYI, this kind of post is AWFUL!, DIFFICULT! FRUSTRATING!
[tt]
c7 - 20/08
d7 - 22/08
c8 - 20/08
d8 - 22/08
c9 - 20/08
d9 - 22/08

i then have in i7 20/08 j7 shows 3
in i8 i have 21/08 j8 shows 0
in i9 i have 22/08 shows 3
[/tt]

This kind of post is GREAT! EASY to work with!
[tt]
range data

col c col d
20-Aug 20-Aug
20-Aug 25-Aug
20-Aug 21-Aug
20-Aug 20-Aug
21-Aug 24-Aug
21-Aug 21-Aug
21-Aug 21-Aug
21-Aug 25-Aug
21-Aug 23-Aug
21-Aug 23-Aug

results in column J

20-Aug 5
21-Aug 8

I would need to see on this range

20-Aug 4
21-Aug 8
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}