Excel formula based on Date input

[crave4excel]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]From[/TD]
[TD]To[/TD]
[TD]Jan 1[/TD]
[TD]Feb 1[/TD]
[TD]Mar 1[/TD]
[TD]Apr 1[/TD]
[TD]May 1[/TD]
[/TR]
[TR]
[TD]Feb 1[/TD]
[TD]Mar 15[/TD]
[TD]no[/TD]
[TD]yes[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]Feb 15[/TD]
[TD]Apr 30[/TD]
[TD]no[/TD]
[TD]yes[/TD]
[TD]yes[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[/TR]
</tbody>[/TABLE]

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
From	To
No	Yes	Yes	No	No
No	Yes	Yes	Yes	No

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"]1-Jan[/TD]
[TD="align: right"]1-Feb[/TD]
[TD="align: right"]1-Mar[/TD]
[TD="align: right"]1-Apr[/TD]
[TD="align: right"]1-May[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1-Feb[/TD]
[TD="align: right"]15-Mar[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]15-Feb[/TD]
[TD="align: right"]30-Apr[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=IF(AND(MONTH(C$2)>=MONTH($A3),MONTH(C$2)<=MONTH($B3)),"Yes","No")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]from[/TD]
[TD]to[/TD]
[TD]Apr 9[/TD]
[TD]April 16[/TD]
[TD]april 23[/TD]
[TD]april 30[/TD]
[TD]may 7[/TD]
[/TR]
[TR]
[TD]apr 17[/TD]
[TD]apr 30[/TD]
[TD]no[/TD]
[TD]yes[/TD]
[TD]yes[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Thanks. that didn't work but I think it's because my example was bad. Here's the modified example:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]from
[/TD]
[TD]to
[/TD]
[TD]Apr 9
[/TD]
[TD]April 16
[/TD]
[TD]april 23
[/TD]
[TD]april 30
[/TD]
[TD]may 7
[/TD]
[/TR]
[TR]
[TD]apr 17
[/TD]
[TD]apr 30
[/TD]
[TD]no
[/TD]
[TD]yes
[/TD]
[TD]yes
[/TD]
[TD]yes
[/TD]
[TD]no
[/TD]
[/TR]
</tbody>[/TABLE]

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")