Excel formula based on Date input

[crave4excel]

A	B	C	D	E	F	G
From	To	Jan 1	Feb 1	Mar 1	Apr 1	May 1
Feb 1	Mar 15	no	yes	yes	no	no
Feb 15	Apr 30	no	yes	yes	yes	no

<tbody>
</tbody>

Hello
I have a table similar to above. I want to come up with a formula to populate "yes" or "no" from column C3:G4, based on dates entered in column A:B (in red).

Any ideas?

Appreciated.

 

[jtakw]

Hi,

If the Year is Not a factor:

Excel 2010
	A	B	C	D	E	F	G
2	From	To	1-Jan	1-Feb	1-Mar	1-Apr	1-May
3	1-Feb	15-Mar	No	Yes	Yes	No	No
4	15-Feb	30-Apr	No	Yes	Yes	Yes	No
Sheet6

Cell Formulas
Range	Formula
C3	=IF(AND(MONTH(C$2)>=MONTH($A3),MONTH(C$2)<=MONTH($B3)),"Yes","No")

C3 formula copied down and across.

 

[JimboP77]

What about something like this in cell C2:

=IF(AND(C1>=$A2,C1<=$B2),"Yes","No")

 

[jtakw]

What about something like this in cell C2:

=IF(AND(C1>=$A2,C1<=$B2),"Yes","No")

That would fail for comparing D2 on Row 4, as both dates are in the Same month, but D2 is Feb-1, and A4 is Feb-15.
According to OP's sample, he/she is comparing the Month.

 

[crave4excel]

Hi,

If the Year is Not a factor:

Excel 2010

	A	B	C	D	E	F	G
2	From	To	1-Jan	1-Feb	1-Mar	1-Apr	1-May
3	1-Feb	15-Mar	No	Yes	Yes	No	No
4	15-Feb	30-Apr	No	Yes	Yes	Yes	No

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula C3 =IF(AND(MONTH(C$2)>=MONTH($A3),MONTH(C$2)<=MONTH($B3)),"Yes","No") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

C3 formula copied down and across.

Thanks. that didn't work but I think it's because my example was bad. Here's the modified example:

from	to	Apr 9	April 16	april 23	april 30	may 7
apr 17	apr 30	no	yes	yes	yes	no

<tbody>
</tbody>

 

[jtakw]

Thanks. that didn't work but I think it's because my example was bad. Here's the modified example:

from	to	Apr 9	April 16	april 23	april 30	may 7
apr 17	apr 30	no	yes	yes	yes	no

<tbody>
</tbody>

At first glance, I was going to say, "Then, use Jimbo's formula in Post #3 , since now you're comparing the actual Date, Not just the Month".
But, then I saw "Yes" under April 16, Why is that?

 

[crave4excel]

Thanks for the question. Because April 17 is between Apr 16 - Apr 23, so i'm essentially comparing "the week of".

So I want to show "yes" to wherever the set of dates fall under, hence, April 17 - 30 fall under the week of Apr 16 through week of April 30

 

[jtakw]

Now I'm confused, your "from" and "to" dates in your latest sample, are they From Apr 17 To Apr 30? or something else?

 

[crave4excel]

yes, from Apr 17 to APr 30. So i want to highlight the date range these dates fall under. So ..

Week of April 9 : no because the week ends on the 15th
Week of April 16: yes because april 17 falls under this week
Week of April 23: yes
Week of April 30: the end date of the range is the 30th, hence falls under this week
Week of May 7: falls outside the date range, so "no"

 

[jtakw]

yes, from Apr 17 to APr 30. So i want to highlight the date range these dates fall under. So ..

Week of April 9 : no because the week ends on the 15th
Week of April 16: yes because april 17 falls under this week
Week of April 23: yes
Week of April 30: the end date of the range is the 30th, hence falls under this week
Week of May 7: falls outside the date range, so "no"

If I understand correctly, Now you're comparing the Week?
This will work, I'm using the "Default", where Sunday is the beginning of each week.

Excel 2010
	A	B	C	D	E	F	G
1	from	to	9-Apr	16-Apr	23-Apr	30-Apr	7-May
2	17-Apr	30-Apr	No	Yes	Yes	Yes	No
Sheet6

Cell Formulas
Range	Formula
C2	=IF(AND(WEEKNUM(C$1)>=WEEKNUM($A2),WEEKNUM(C$1)<=WEEKNUM($B2)),"Yes","No")