Sumproduct - count dates with date range

[sazza]

Hi Guys,

New - Stuck and hope someone can help me ..

I have a date range which i want to check if it falls within another date range and if it does count how many day in the first date range fall withing the second date range

I Want to check if this date range (1)
C6 : 28/08/10
C7 : 26/10/10

falls within date range (2)
01/03/yy
31/10/yy

so from the above example i can see the first range is completely within the second range and would hopefulyl give a result of 59 days ( 26/10 - 28/08 = 59 days)


Hope this makes sense ..
Can anyone help me ?

Thanks,
Sazza

 

[cornflakegirl]

Welcome to the board.

Try:

=IF(OR(C7<=C9,C6>=C10),0,MIN(C7,C10)-MAX(C6,C9))

This assumes that your date range (2) is in C9:C10. It gives the answer of 59 that you wanted - however, are you sure that you don't want the answer to be 60? Eg if the date range was 01/03/10 - 02/03/10 this would give the answer 1 day - would you want it to be 2? If you would, just stick +1 at the end before the second ).

 

[MisterBate5]

Can you post a sample of your data? Would need to know if your second range has "yy" as the year, or an actual year. Are you looking only at days/months, or are you looking to know how many days overlap between two actual date ranges?

 

[sazza]

Thanks cornflakegirl ... this works great ..

MisterBate5 - the date looks like this below - my date range 2 can be any year but for simplicity i have expanded it - if you can suggest a better way i would appreciate it


B C D
.
.

6 Period Start Date 28/08/2010
7 Period End Date 26/10/2010
8
9 Summer 01/03/2009 31/10/2009
10 Summer 01/03/2010 31/10/2010
11 Summer 01/03/2011 31/10/2011
12
13 Winter 01/11/2009 28/02/2010
14 Winter 01/11/2010 28/02/2011
15 Winter 01/11/2011 28/02/2012
16


so if C6 and C7 fall in summer months how many days in summer ?
and if C6 and C7 fall in winter months how many days in winter


Thanks ...

 

[sazza]

sorry dont know how to add screenshot - that look bad above

 

[sazza]

---------- B ----------------------------C ------------------------------------D
.
.

6 -- Period Start Date --------------28/08/2010
7 -- Period End Date ---------------26/10/2010
8
9 -- Summer -----------------------01/03/2009 -----------------------31/10/2009
10 --Summer ---------------------- 01/03/2010----------------------- 31/10/2010
11 --Summer ---------------------- 01/03/2011 -----------------------31/10/2011
12
13 --Winter ----------------------- 01/11/2009 -----------------------28/02/2010
14 --Winter ----------------------- 01/11/2010 -----------------------28/02/2011
15 --Winter ----------------------- 01/11/2011 -----------------------28/02/2012
16

 

[MisterBate5]

Cornflakegirl's formula is the one to use.

Question for you is how you want to get the results - one result per year? Or one overall result based on the year of the period (and looking up the year in the relevant row)?

If the former, then add the formula to the end of each row. If the latter, then put the formula at the top and use sumproduct or similar to pick out the relevant summer/winter entry.

BTW, to post a screenshot, use one of the liks in my .sig - I use HTML Maker

 

[sazza]

Thanks a mill guys - i have that working now - appreciate your help you saved me hours tinkering around with it ...