IF(AND) problem

[Peesey]

I am trying to use an IF(AND) to return a value if a cell on a calendar falls between two dates. The formula I'm using is: =IF(AND(D$1 >= $B2,D$1 <= $C2),$A2,"").

In the table below I want D2 to have the Ref number from A2 in it as the launch date in B2 falls in that week. However, it is coming back blank. If I change the launch date in B2 to match D1 (i.e. to 02/01/2017) it populates D2.

I've shown examples below to show what I mean:

Table showing the formulas used:

	A	B	C	D	E	F	G	H	I	J
1	Ref number	Launch date	End date	02-Jan	09-Jan	16-Jan	23-Jan	30-Jan	06-Feb	13-Feb
2	ABC001	03/01/2017	31/01/2017	=IF(AND(D$1 >= $B2,D$1 <= $C2),$A2,"")	=IF(AND(E$1 >= $B2,E$1 <= $C2),$A2,"")	=IF(AND(F$1 >= $B2,F$1 <= $C2),$A2,"")	=IF(AND(G$1 >= $B2,G$1 <= $C2),$A2,"")	=IF(AND(H$1 >= $B2,H$1 <= $C2),$A2,"")	=IF(AND(I$1 >= $B2,I$1 <= $C2),$A2,"")	=IF(AND(J$1 >= $B2,J$1 <= $C2),$A2,"")

<tbody>
</tbody>

Table showing the result I get:

	A	B	C	D	E	F	G	H	I	J
1	Ref number	Launch date	End date	02-Jan	09-Jan	16-Jan	23-Jan	30-Jan	06-Feb	13-Feb
2	ABC001	03/01/2017	31/01/2017		ABC001	ABC001	ABC001	ABC001

<tbody>
</tbody>

Table showing the result I want:

	A	B	C	D	E	F	G	H	I	J
1	Ref number	Launch date	End date	02-Jan	09-Jan	16-Jan	23-Jan	30-Jan	06-Feb	13-Feb
2	ABC001	03/01/2017	31/01/2017	ABC001	ABC001	ABC001	ABC001	ABC001

<tbody>
</tbody>

Basically, if the launch date is in a week I want the cell that corresponds to that week to be populated with the Ref number as well (regardless of the day in the week it launches - NOT just if it launched on the first day of the week).

Please help!!!

 

[Aladin Akyurek]

Are the dates in D2:J2 all dates in 2017?

 

[Peesey]

Sorry - should have formatted them in the same way to make it clear but, yes they are all in 2017 - it is the week commencing date.

 

[Tetra201]

Is this what you need?

=IF(AND(D$1>=$B2+1-WEEKDAY($B2,2),D$1<=$C2+7-WEEKDAY($C2,2)),$A2,"")

 

[Peesey]

Thanks Tetra201 but still not quite working. It solved my initial problem but the cell now remains populated if B2 is the start date of the following week (e.g. if I set B2 as 09/01/2017 cell D2 contains the Ref number when is should now be blank - this stops if I set B2 to 10/01/2017).

 

[Tetra201]

The formula seems to work as posted.

Note: it was corrected within a few minutes of the initial posting -- I inadvertently copied a non-final formula from my test sheet. Please make sure you are using the current version.

 

[Peesey]

That's great - all working as it should. Thanks for the help.

 

[Tetra201]

You are welcome.