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:
<tbody>
</tbody>
Table showing the result I get:
<tbody>
</tbody>
Table showing the result I want:
<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!!!
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!!!