Formula to output "X" if date falls within one of several different date ranges

[mick0005]

Hi all -

So what I have is a column full of dates (Column G). Then I have 4 additional SETS of columns to the right with a start date and an end date representing a date range.

What I want to do is take the primary date (column G) and bounce it against the 4 sets of date ranges (columns L,M P,Q T,U and X,Y). If it falls within one or several of those date ranges, then it would output an "X" or some other value to be defined by the user.

I'd like to do this with just a formula. Any ideas?

Here is the sheet.

Excel Workbook
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
2	Date	Units	*	REGIONAL COMMENTS	*	Start Date 0	End Date 0	Program	*	Start Date 1	End Date 1	*	*	Start Date 2	End Date 2	*	*	Start Date 3	End Date 3	*
3	1/24/2012	2012	*	>= 4 days; include entire week of 11/29 (6 days req'd)	Cold Cups 32oz FoamCHICAGO REGION-0150430000CHICAGO IL CP-000796	1/1/2012	1/15/2012	#N/A	*	1/16/2012	1/30/2012	#N/A	*	2/12/2012	2/15/2012	#N/A	*	2/19/2012	2/19/2012	#N/A
SUPPLY PLAN INPUT

 

[mick0005]

By the way, I'd put the formula in Column I

 

[snoopyhr]

Is this what you are after ?

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 75px"><COL style="WIDTH: 40px"><COL style="WIDTH: 41px"><COL style="WIDTH: 84px"><COL style="WIDTH: 107px"><COL style="WIDTH: 90px"><COL style="WIDTH: 80px"><COL style="WIDTH: 60px"><COL style="WIDTH: 20px"><COL style="WIDTH: 90px"><COL style="WIDTH: 80px"><COL style="WIDTH: 41px"><COL style="WIDTH: 18px"><COL style="WIDTH: 90px"><COL style="WIDTH: 80px"><COL style="WIDTH: 41px"><COL style="WIDTH: 23px"><COL style="WIDTH: 90px"><COL style="WIDTH: 80px"><COL style="WIDTH: 41px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana">Date</TD><TD style="FONT-FAMILY: Verdana">Units</TD><TD style="FONT-FAMILY: Verdana">*</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold">REGIONAL COMMENTS</TD><TD style="FONT-FAMILY: Verdana">*</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">Start Date 0</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">End Date 0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">Program</TD><TD style="BACKGROUND-COLOR: #808080; FONT-FAMILY: Verdana">*</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">Start Date 1</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">End Date 1</TD><TD style="BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">*</TD><TD style="BACKGROUND-COLOR: #808080; FONT-FAMILY: Verdana">*</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">Start Date 2</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">End Date 2</TD><TD style="BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">*</TD><TD style="BACKGROUND-COLOR: #808080; FONT-FAMILY: Verdana">*</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">Start Date 3</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">End Date 3</TD><TD style="BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">*</TD></TR><TR style="HEIGHT: 120px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana">1/1/2012</TD><TD style="FONT-FAMILY: Verdana">2012</TD><TD>x</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Verdana; COLOR: #ff0000">>= 4 days; include entire week of 11/29 (6 days req'd)</TD><TD style="FONT-FAMILY: Verdana">Cold Cups 32oz FoamCHICAGO REGION-0150430000CHICAGO IL CP-000796</TD><TD style="FONT-FAMILY: Verdana">1/1/2012</TD><TD style="FONT-FAMILY: Verdana">1/15/2012</TD><TD style="FONT-FAMILY: Verdana">#N/A</TD><TD style="BACKGROUND-COLOR: #808080; FONT-FAMILY: Verdana">*</TD><TD style="FONT-FAMILY: Verdana">1/16/2012</TD><TD style="FONT-FAMILY: Verdana">1/31/2012</TD><TD style="FONT-FAMILY: Verdana">#N/A</TD><TD style="BACKGROUND-COLOR: #808080; FONT-FAMILY: Verdana">*</TD><TD style="FONT-FAMILY: Verdana">2/12/2012</TD><TD style="FONT-FAMILY: Verdana">2/15/2012</TD><TD style="FONT-FAMILY: Verdana">#N/A</TD><TD style="BACKGROUND-COLOR: #808080; FONT-FAMILY: Verdana">*</TD><TD style="FONT-FAMILY: Verdana">2/19/2012</TD><TD style="FONT-FAMILY: Verdana">2/19/2012</TD><TD style="FONT-FAMILY: Verdana">#N/A</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D4</TD><TD>=IF(ISNUMBER(MATCH(B4,G4:U4,-1)),"x","")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[snoopyhr]

or maybe this, just a different approach


=IF(--(B4<=MAX(G4:U4))*--(B4>=MIN(G4:U4))=0,"","X")

By the way please adjust the range accordingly I started with column A , your was started on column G my D is your I sorry about that

 

[barry houdini]

In I3 try this formula

=IF(OR(AND(G3>=L3,G3<=M3),AND(G3>=P3,G3<=Q3),AND(G3>=T3,G3<=U3),AND(G3>=X3,G3<=Y3)),"x","")

 

[mick0005]

Is this what you are after ?

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 75px"><COL style="WIDTH: 40px"><COL style="WIDTH: 41px"><COL style="WIDTH: 84px"><COL style="WIDTH: 107px"><COL style="WIDTH: 90px"><COL style="WIDTH: 80px"><COL style="WIDTH: 60px"><COL style="WIDTH: 20px"><COL style="WIDTH: 90px"><COL style="WIDTH: 80px"><COL style="WIDTH: 41px"><COL style="WIDTH: 18px"><COL style="WIDTH: 90px"><COL style="WIDTH: 80px"><COL style="WIDTH: 41px"><COL style="WIDTH: 23px"><COL style="WIDTH: 90px"><COL style="WIDTH: 80px"><COL style="WIDTH: 41px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana">Date</TD><TD style="FONT-FAMILY: Verdana">Units</TD><TD style="FONT-FAMILY: Verdana">*</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold">REGIONAL COMMENTS</TD><TD style="FONT-FAMILY: Verdana">*</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">Start Date 0</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">End Date 0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">Program</TD><TD style="BACKGROUND-COLOR: #808080; FONT-FAMILY: Verdana">*</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">Start Date 1</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">End Date 1</TD><TD style="BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">*</TD><TD style="BACKGROUND-COLOR: #808080; FONT-FAMILY: Verdana">*</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">Start Date 2</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">End Date 2</TD><TD style="BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">*</TD><TD style="BACKGROUND-COLOR: #808080; FONT-FAMILY: Verdana">*</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">Start Date 3</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">End Date 3</TD><TD style="BACKGROUND-COLOR: #993366; FONT-FAMILY: Verdana; COLOR: #ffffff">*</TD></TR><TR style="HEIGHT: 120px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana">1/1/2012</TD><TD style="FONT-FAMILY: Verdana">2012</TD><TD>x</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Verdana; COLOR: #ff0000">>= 4 days; include entire week of 11/29 (6 days req'd)</TD><TD style="FONT-FAMILY: Verdana">Cold Cups 32oz FoamCHICAGO REGION-0150430000CHICAGO IL CP-000796</TD><TD style="FONT-FAMILY: Verdana">1/1/2012</TD><TD style="FONT-FAMILY: Verdana">1/15/2012</TD><TD style="FONT-FAMILY: Verdana">#N/A</TD><TD style="BACKGROUND-COLOR: #808080; FONT-FAMILY: Verdana">*</TD><TD style="FONT-FAMILY: Verdana">1/16/2012</TD><TD style="FONT-FAMILY: Verdana">1/31/2012</TD><TD style="FONT-FAMILY: Verdana">#N/A</TD><TD style="BACKGROUND-COLOR: #808080; FONT-FAMILY: Verdana">*</TD><TD style="FONT-FAMILY: Verdana">2/12/2012</TD><TD style="FONT-FAMILY: Verdana">2/15/2012</TD><TD style="FONT-FAMILY: Verdana">#N/A</TD><TD style="BACKGROUND-COLOR: #808080; FONT-FAMILY: Verdana">*</TD><TD style="FONT-FAMILY: Verdana">2/19/2012</TD><TD style="FONT-FAMILY: Verdana">2/19/2012</TD><TD style="FONT-FAMILY: Verdana">#N/A</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D4</TD><TD>=IF(ISNUMBER(MATCH(B4,G4:U4,-1)),"x","")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

I came up with this formula - although I doubt it is the most efficient, it does seem to be working.

=IF(AND(G4>=M4,G4<=N4),"DELETE",IF(AND(G4>=Q4,G4<=R4),"DELETE",IF(AND(G4>=U4,G4<=V4),"DELETE",IF(AND(G4>=Y4,G4<=Z4),"DELETE",""))))

 

[barry houdini]

That's similar to the formula I suggested just a minute or two before.......shorten slightly by using OR, i.e.

=IF(OR(AND(G4>=M4,G4<=N4),AND(G4>=Q4,G4<=R4),AND(G4>=U4,G4<=V4),AND(G4>=Y4,G4<=Z4)),"DELETE","")

 

[mick0005]

That's similar to the formula I suggested just a minute or two before.......shorten slightly by using OR, i.e.

=IF(OR(AND(G4>=M4,G4<=N4),AND(G4>=Q4,G4<=R4),AND(G4>=U4,G4<=V4),AND(G4>=Y4,G4<=Z4)),"DELETE","")

Thanks Barry! I am curious, does using the OR function in replacement of the way I did it help to increase the efficiency of the workbook at all? Will calculations run quicker?

 

[barry houdini]

I doubt if there's much noticeable difference.....possibly a microsecond or two

 

[mick0005]

I doubt if there's much noticeable difference.....possibly a microsecond or two

Thanks a lot! Thanks again for the help... I'll have to check out that OR possibility. I can't seem to figure out OR as well as I can the nested AND way.