If / AND help

Joneye

Joneye

Well-known Member
Joined
May 28, 2010
Messages
785
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
My objective here is to have an IF / AND statement report back the term in months by looking at Cell G for out or in...

The answer if correct should be 75 if wrong "no". (at present im getting "NO" as the answer - no idea why)

I hope I have explained the above ok?

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">13</td><td style="background-color: #FFFF00;;">Term in Storage</td><td style=";">Building</td><td style=";">Description</td><td style=";">Account Code</td><td style=";">Code</td><td style=";">Alternate Code</td><td style=";">Item Status</td><td style=";">Add Date</td><td style=";">Status Date</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="background-color: #FFFF00;;">no</td><td style=";">ENF</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">6695250</td><td style="text-align: right;;"></td><td style=";">Out</td><td style="text-align: right;;">02/03/2005</td><td style="text-align: right;;">03/03/2005</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="background-color: #FFFF00;;">no</td><td style=";">ENF</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">6695254</td><td style="text-align: right;;"></td><td style=";">In</td><td style="text-align: right;;">02/03/2005</td><td style="text-align: right;;">03/03/2005</td></tr></tbody></table><p style="width:10.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">2. Crown Inventory</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A14</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">G14="Out",G14="In",0</font>),DATEDIF(<font color="Red">H14,NOW(<font color="Green"></font>),"M"</font>),"no"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A15</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">G15="Out",G15="In",0</font>),DATEDIF(<font color="Red">H15,NOW(<font color="Green"></font>),"M"</font>),"no"</font>)</td></tr></tbody></table></td></tr></table><br />
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
How can G14 be equal to "Out" and "In"? Maybe something like this:
=IF(OR(G14="Out",G14="In"),DATEDIF(H14,NOW(),"M"),"no")
 
Upvote 0
Joneye said:
My objective here is to have an IF / AND statement report back the term in months by looking at Cell G for out or in...

The answer if correct should be 75 if wrong "no". (at present im getting "NO" as the answer - no idea why)

I hope I have explained the above ok?

Excel 2003<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></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></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="BACKGROUND-COLOR: #ffff00">Term in Storage</TD><TD>Building</TD><TD>Description</TD><TD>Account Code</TD><TD>Code</TD><TD>Alternate Code</TD><TD>Item Status</TD><TD>Add Date</TD><TD>Status Date</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="BACKGROUND-COLOR: #ffff00">no</TD><TD>ENF</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">6695250</TD><TD style="TEXT-ALIGN: right"></TD><TD>Out</TD><TD style="TEXT-ALIGN: right">02/03/2005</TD><TD style="TEXT-ALIGN: right">03/03/2005</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="BACKGROUND-COLOR: #ffff00">no</TD><TD>ENF</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">6695254</TD><TD style="TEXT-ALIGN: right"></TD><TD>In</TD><TD style="TEXT-ALIGN: right">02/03/2005</TD><TD style="TEXT-ALIGN: right">03/03/2005</TD></TR></TBODY></TABLE>
2. Crown Inventory​


<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>A14</TH><TD style="TEXT-ALIGN: left">=IF(AND(G14="Out",G14="In",0),DATEDIF(H14,NOW(),"M"),"no")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A15</TH><TD style="TEXT-ALIGN: left">=IF(AND(G15="Out",G15="In",0),DATEDIF(H15,NOW(),"M"),"no")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Click to expand...


Are you sure it's IF(AND you're after? because the above formula says IF G14= "Out" AND G14="In"... surely G14 can't have both results at the same time so your answer will be No... wouldn't IF(OR make more sense?

:)
 
Upvote 0
Correct guys, but note G:15 has out so I need a formula to copy down a work book that looks for both in & out and then selects the datefunction. Note the third option is destroyed but this reports as no.
 
Upvote 0
Its the OR part i needed instead of AND....

Guys many thanks for the help...
 
Upvote 0
An extention to my question, I seem to have lost the [value_if_false] option to my formula, any advice here welcome?


<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">13</td><td style="background-color: #FFFF00;;">Term in Storage</td><td style=";">Building</td><td style=";">Description</td><td style=";">Account Code</td><td style=";">Code</td><td style=";">Alternate Code</td><td style=";">Item Status</td><td style=";">Add Date</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="background-color: #FFFF00;;">6 Years and 3 Months</td><td style=";">ENF</td><td style="text-align: right;;"></td><td style=";">REDROWHO\RED608</td><td style="text-align: right;;">6695250</td><td style="text-align: right;;"></td><td style=";">In</td><td style="text-align: right;;">02/03/2005</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;background-color: #FFFF00;;">FALSE</td><td style=";">ENF</td><td style="text-align: right;;"></td><td style=";">REDROWHO\RED608</td><td style="text-align: right;;">6695254</td><td style="text-align: right;;"></td><td style=";">Destroyed</td><td style="text-align: right;;">02/03/2005</td></tr></tbody></table><p style="width:10.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">2. Crown Inventory</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A14</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">G14="Out",G14="In"</font>),DATEDIF(<font color="Red">H14,NOW(<font color="Green"></font>),"Y"</font>)&" Years and "&DATEDIF(<font color="Red">H14,NOW(<font color="Green"></font>),"YM"</font>)&" Months"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A15</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">G15="Out",G15="In"</font>),DATEDIF(<font color="Red">H15,NOW(<font color="Green"></font>),"Y"</font>)&" Years and "&DATEDIF(<font color="Red">H15,NOW(<font color="Green"></font>),"YM"</font>)&" Months"</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
Try

=IF(OR(G14="Out",G14="In"),DATEDIF(H14,NOW(),"Y")&" Years and "&DATEDIF(H14,NOW(),"YM")&" Months",ValueIfFalseHere)
 
Upvote 0
jonmo1

Perfect - sorry having a brain block day - thanks guys for the speedy help all of you....
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
572
mumps
mumps
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
613
smozgur
smozgur
C
  • Question Question
Picture won't paste in cell overlay
Replies
0
Views
249
Cerka
C
E
  • Question Question
Adding a custom message.
Replies
1
Views
658
Micron
M
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
88
Dazzybeeguy
D

Forum statistics

Threads
1,224,503
Messages
6,179,134
Members
452,890
Latest member
Nikhil Ramesh

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top