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

mick0005

Active Member
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
GHIJKLMNOPQRSTUVWXYZ
1********************
2DateUnits*REGIONAL COMMENTS*Start Date 0End Date 0Program*Start Date 1End Date 1**Start Date 2End Date 2**Start Date 3End Date 3*
31/24/20122012*>= 4 days; include entire week of 11/29 (6 days req'd)Cold Cups 32oz FoamCHICAGO REGION-0150430000CHICAGO IL CP-0007961/1/20121/15/2012#N/A*1/16/20121/30/2012#N/A*2/12/20122/15/2012#N/A*2/19/20122/19/2012#N/A
SUPPLY PLAN INPUT

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
By the way, I'd put the formula in Column I

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

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

Last edited:
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","")

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

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

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?

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

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.

Replies
4
Views
118
Replies
1
Views
98
Replies
4
Views
392
Replies
6
Views
404
Replies
1
Views
380

1,202,987
Messages
6,052,939
Members
444,617
Latest member
Rush1984

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.

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

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