Excel 2007 : Date ranges withing date ranges

[Sazza]

Hi Guys,

I'm Stuck ....

I have a date range with start date and end date
INFO!D5 = start date
INFO!D6 = end date

I need to find out in this range ( start and end) how many days fall :

1. Days Before 01/03/11
2. Days Between 01/03/11 + 01/06/11
3. Days after 01/06/11

i have the before and after dates working but its where have dates running between i get stuck

if i have a date that run like
26/02/2011
27/03/2011

I need to show - 2 before 01/03/11 and 27 after 01/03/11

or if a date runs like
26/05/2011
27/06/2011

I need to show - 5 between 01/03/11 + 01/06/11 and 27 after 01/06/11

Any ideas ?

Thanks,
Saz

[NBVC]

Assuming you used COUNTIF for before and after, then for between use COUNTIFS

e.g.

=COUNTIFS(range,">="&DATE(2011,3,1),range,"<="&DATE(2011,6,1))

[Sazza]

I havent used COUNTIFS instead using something way more time consuming

1 ... Days Before 01/03/11

=IF(AND(Info!D5<DATE(2011,3,1),Info!D6<DATE(2011,3,1)),(Info!D6-Info!D5),IF(AND(Info!D5<DATE(2011,3,1),Info!D6>DATE(2011,3,1)),DATE(2011,3,1)-Info!D5,0))


2 ....Days After 01/06/11

=IF(AND(Info!D5>DATE(2011,6,1),Info!D6>DATE(2011,6,1)),(Info!D6-Info!D5),IF(AND(Info!D5<DATE(2011,6,1),Info!D6>DATE(2011,6,1)),Info!D6-DATE(2011,6,1),0))


3 ... I think i have the between 01/03/11 and 01/06/11 with overlapping dates working my way too

=IF(AND(Info!D5>DATE(2011,3,1),Info!D6<DATE(2011,6,1)),(Info!D6-Info!D5),IF(AND(Info!D5<=DATE(2011,3,1),Info!D6>=DATE(2011,3,1)),Info!D6-DATE(2011,3,1),IF(AND(Info!D5>DATE(2011,3,1),Info!D6>DATE(2011,6,1)),DATE(2011,6,1)-Info!D5+1,0)))


But your way looks to be way more efficient, would that way work if i had overlapping dates before and after 01/03/11 ( or 01/06/11) ?

Thanks,
Saz

[NBVC]

My formula looks at a range and counts number of matches... since you are looking at only 2 date cells, D5 and D6, then not sure if you need to use COUNTIF or COUNTIFS...

You could put instead of the DATE() functions in the formulas, put the dates in 2 other cells and reference those in the formulas so that the DATE() function isn't needlessly getting recalculated everytime reducing the formula efficiency.

[Sazza]

Cool - Thanks for your help ...