# IF(AND) problem

#### Peesey

##### New Member
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).

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

##### MrExcel MVP
Are the dates in D2:J2 all dates in 2017?

#### Peesey

##### New Member
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

##### MrExcel MVP
Is this what you need?

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

Last edited:

#### Peesey

##### New Member
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

##### MrExcel MVP
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

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

You are welcome.

Replies
10
Views
308
Replies
9
Views
260
Replies
3
Views
762
Replies
2
Views
342
Replies
18
Views
1K

1,195,855
Messages
6,011,974
Members
441,660
Latest member
Neela_Kattappa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back