if statement ...issue

[bluepenink]

Hello

Having some problem disecting an if statement, below is my issue

Column N contains text = 3-5 days

Column J contains value = i.e. 8

3-5 days is budgeted, and 8 days is the actual

so i need an if statement to say,

-if column J > 3-5 days, then Yes, No

Similarly, i then need to calculate how many days its over by

so i.e. if the budgeted says "3-5 days" and actual was 8, therefore, 8-5 = 3

so how can i make the if statement in part two say that?

can someone pls help. thx you!

 

[repairman615]

Hello,

<TABLE style="WIDTH: 393pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=524><COLGROUP><COL style="WIDTH: 393pt; mso-width-source: userset; mso-width-alt: 9581" width=524><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 393pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=524>=IF(J1>MAX(LEFT(N1,FIND("-",N1,1)-1),MID(N1,FIND("-",N1,1)+1,1)),"Yes","No")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>=J1-MAX(LEFT(N1,FIND("-",N1,1)-1),MID(N1,FIND("-",N1,1)+1,1))</TD></TR></TBODY></TABLE>


The first formula for yes/no result
Second formula for difference.

The formula is expecting single digits and a format in cell N1 like "3-5 days"

-Jeff

 

[repairman615]

Here is an update to handle Larger than single digit numbers:

Excel 2007
<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH><TH>N</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Yes</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">21</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">3</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">3-5 Days</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">16</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>N1</TH><TD style="TEXT-ALIGN: left">=L1&"-"&M1&" Days"</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A1</TH><TD style="TEXT-ALIGN: left">=IF(J1>MAX(LEFT(N1,FIND("-",N1,1)-1),MID(N1,FIND("-",N1,1)+1,LEN(N1)-7)),"Yes","No")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A2</TH><TD style="TEXT-ALIGN: left">=IF(J1-MAX(LEFT(N1,FIND("-",N1,1)-1),MID(N1,FIND("-",N1,1)+1,LEN(N1)-7))<0,"Current",J1-MAX(LEFT(N1,FIND("-",N1,1)-1),MID(N1,FIND("-",N1,1)+1,LEN(N1)-7)))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[bluepenink]

hello

wow, thx you so much! ingenius!!!!

ur the best, thxs again!