Find how many weeks in a period

[tarynmahon]

My mind is going blank on this, Im sure it must be easy!

I have a period ie.

04/01/2013 - 02/02/2013


elsewhere in the spreadsheet I have;

WK 1

01/01/13

08/01/13


WK 2

09/01/13

16/01/13


I need a formula that looks at the period and puts a "1" in every week that the period covers, what I have so far is;

=IF(AND(PERIOD FROM DATE>=WEEK1 START DATE,PERIOD FROM DATE<=WEEK1 END DATE),1,"")


This sort of works but when we get onto week 2 it doesnt include any of the periods that started in week 1, apologies if this doesnt really make sense, Im not sure how else to put it.


For the period 04/01/13-18/01/13 I would want the answer to read

WK1, WK2, WK3, WK4, WK5,

01/01/13,09/01/13,17/01/13,25/01/13,02/02/13,

08/01/13,16/01/13,24/01/13,01/02/13,09/02/13,

1,1,1, , ,


I AM UNABLE TO POST A SAMPLE FILE DUE TO COMPANY RESTRICTIONS

 

[Debraj]

Hi tarynmahon!!


Can you please check below post..


Can I guess.. your requirement is somehow look-a-like..


http://www.get-digital-help.com/2013/03/05/heat-map-calendar/


Regards,

Deb

 

[tarynmahon]

Im not sure how that relates to my question?

 

[Debraj]

Hi tarynmahon!!


Please check the below..

=IF(ISNUMBER(MATCH(1,MATCH(ROW(INDIRECT(A$2&":"&A$3)),ROW(INDIRECT(StartDate&":"&EndDate)),0)^0,0)),"1","")

where A2 & A3 are location for Date Denote..


https://dl.dropbox.com/u/78831150/Excel/Find%20how%20many%20weeks%20in%20a%20period.xlsx


Sorry, lil bit tired.. so suggested above.. and this time Formula lil bit longer..


I will try later to Reduce.. if required..


Regards,

Deb

 

[tarynmahon]

Perfect, thank you!!!!