Formula for "contained in date range"

[mick0005]

Hi all, here is what I am trying to do:

Please look at the image provided. I want to write a formula where in columns R through AA there is an output of "7" (days) that populates IF the dates in AB and AC are contained in that week.

So, for example in the first row of data, in week 10/25/11 for the dates of 7/3/11 through 12/31/11, I would need the cell to populate with a "7" since the week of 10/25 (10/25 -10/31) does fall within the 7/3/11 through 12/31/11 range. If it does NOT fall within the range, then I would want to know how many days were cut off in that week. For example, if the range of dates provided was 7/3/11 through 10/29/11 then the output would be "2" since it is cut off 2 days short of 10/31.

I am fairly certain this can all be done with a formula, I just can't get my head around the logic.

Hopefully this makes sense.

 

[wigi]

Hello,

This problem has been dealt with before. See on the site of Chip Pearson for the logic and formulas:

http://www.cpearson.com/excel/distribdates.htm

 

[asad]

Is this what you wanted?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	25/10/2011	1/11/2011	8/11/2011	15/11/2011	22/11/2011	29/11/2011	6/12/2011	13/12/2011	20/12/2011	27/12/2011
2
3	7											7/03/2011	31/12/2011
4	2											7/03/2011	29/10/2011
5
6
Sheet1

 

[mick0005]

Is this what you wanted?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	25/10/2011	1/11/2011	8/11/2011	15/11/2011	22/11/2011	29/11/2011	6/12/2011	13/12/2011	20/12/2011	27/12/2011
2
3	7											7/03/2011	31/12/2011
4	2											7/03/2011	29/10/2011
5
6
Sheet1

That is EXACTLY what I need!!!! Oh my thank you so much. EXCEPT I am putting in the same formula (but changing for my cell locations) and getting the #REF error. Can you help?

I put this in as the formula:

=IF(AND($AB3(R$2+7)),7,6-(MAX(R$2,$AC3)-MAX(R$2,$AB3)))

What I did not show in my image was the the 10/25/11 week shown in my picture is in row 2, column R. (R2).. the week dates go across through AA2

 

[mick0005]

Here is the HTML add in code:

Excel Workbook
	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	week start	week start	week start	week start	week start	week start	week start	week start	week start	week start
2	10/25/2011	11/1/2011	11/8/2011	11/15/2011	11/22/2011	11/29/2011	12/6/2011	12/13/2011	12/20/2011	12/27/2011
3	#REF!										7/3/2011	12/31/2011
4	#REF!										7/3/2011	10/29/2011
32oz Foam Data

That is EXACTLY what I need!!!! Oh my thank you so much. EXCEPT I am putting in the same formula (but changing for my cell locations) and getting the #REF error. Can you help?

I put this in as the formula:

=IF(AND($AB3(R$2+7)),7,6-(MAX(R$2,$AC3)-MAX(R$2,$AB3)))

What I did not show in my image was the the 10/25/11 week shown in my picture is in row 2, column R. (R2).. the week dates go across through AA2

 

[asad]

Sorry Mick,

I forgot that "<" and ">" symbols always give a problem .

Here is the correct formula:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	25/10/2011	1/11/2011	8/11/2011	15/11/2011	22/11/2011	29/11/2011	6/12/2011	13/12/2011	20/12/2011	27/12/2011
2
3	7											7/03/2011	31/12/2011
4	2											7/03/2011	29/10/2011
Sheet1

Asad

 

[asad]

And your formula should look like this

IF(AND($AB3 < R$2,$AC3 > (R$2+7)),7,6-(MAX(R$2,$AC3)-MAX(R$2,$AB3)))

Asad

 

[mick0005]

And your formula should look like this

IF(AND($AB3 < R$2,$AC3 > (R$2+7)),7,6-(MAX(R$2,$AC3)-MAX(R$2,$AB3)))

Asad

ASAD! You are the best. I wish you were local, I'd buy you a beer!

One more thing, if you have the time and initiative... the formula is currently outputting a negative number for dates that are BEYOND the start date of the week in AB3... is there an adjustment we can make to the formula that says if the result would be negative then output "0" instead?

Thanks again so much!

 

[mick0005]

ASAD! You are the best. I wish you were local, I'd buy you a beer!

One more thing, if you have the time and initiative... the formula is currently outputting a negative number for dates that are BEYOND the start date of the week in AB3... is there an adjustment we can make to the formula that says if the result would be negative then output "0" instead?

Thanks again so much!

Shoot - so now I am looking at it and wondering if I screwed up with explaining this. Let me explain what the spreadsheet does and maybe you can help.

The "week start" fields represent (in another spreadsheet) weeks of forecasts that my forecasting team puts together. The dates on the right hand side in AB and AC represent start and stop dates of individual markets that are "opting out" of getting a forecast during that time period (they don't need one because they are participating in a different program and getting a different forecast for that program). What I am hoping to do in the cells R3 to AA3 (and for the rest of the rows below them) is output the number of days that those individual markets (1 per row) will NEED a forecast in that given week. If the week in R2 is before or after the time frame indicated in AB, then we want a result of how many days in that week it needs a forecast for.

Does this make sense? I tested our current formula and it isn't giving me the right values. I don't know if it is a simple fix or not.

 

[mick0005]

Excel Workbook
	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	week start	week start	week start	week start	week start	week start	week start	week start	week start	week start
2	10/25/2011	11/1/2011	11/8/2011	11/15/2011	11/22/2011	11/29/2011	12/6/2011	12/13/2011	12/20/2011	12/27/2011
3	7	7	7	7	7	7	7	7	7	2	7/3/2011	12/31/2011
4	7	7	7	7	7	7	7	7	7	2	7/3/2011	12/31/2011
5	7	7	7	7	7	7	7	7	7	2	7/3/2011	12/31/2011
6	7	7	7	7	7	7	7	7	7	2	7/3/2011	12/31/2011
7	-116	-116	-116	7	7	7	7	7	7	7	11/14/2011	3/15/2012
8	-116	-116	-116	7	7	7	7	7	7	7	11/14/2011	3/15/2012
32oz Foam Data

In this above example, since the markets are "opting out" from 7/3 to 12/31, they do not need a forecast for any week until the week of 12/27 in which they would need 2 days of forecast (the last two, 1/1 and 1/2). All other week's results should be 0.

For the week start of 11/14, they would need an output (require a forecast) of 6 days for week start 11/8 and 7 days for the 2 weeks prior.

I think I basically had you do the opposite of what I originally needed. Even I am confused at this point, so I hope you can help.