Basic Formula not working

[BradleyS]

I'm a little confused here as I have a very basic IF formula that just looks at 2 dates and if one is less than the other it shows some text, if not it should show nothing.

However, maybe it doesn't work because its calculating dates from two array formulas (just in case this makes a difference)

Cell T3 is correct, but the others shouldn't show any text, because there is no date in column U.

I tried using ISBLANK but that doesn't work either.

Could someone point out my mistake or provide me a solution.

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>S</th><th>T</th><th>U</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">H33</td><td style="text-align: right;;"></td><td style="text-align: right;;">212</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">DX_Date</td><td style="font-weight: bold;;">?_RESOLVED</td><td style="font-weight: bold;;">RESOLVE_DATE</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">26/11/2006 </td><td style=";">NOT Resolved</td><td style="text-align: right;;">22/09/2006 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">21/06/1995 </td><td style=";">NOT Resolved</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">10/05/1999 </td><td style=";">NOT Resolved</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">18/10/1970 </td><td style=";">NOT Resolved</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;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">Sheet1</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">T3</th><td style="text-align:left">=IF(<font color="Blue">U3<=S3,"NOT Resolved",""</font>)</td></tr></tbody></table></td></tr></table><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>Array 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">S3</th><td style="text-align:left">{=LOOKUP(<font color="Blue">9.99999999999999E+307,CHOOSE(<font color="Red">{1,2},0,INDEX(<font color="Green">general!$F$2:$F$364,MATCH(<font color="Purple">1,IF(<font color="Teal">general!$A$2:$A$364=$A3,IF(<font color="#FF00FF">ISNUMBER(<font color="Navy">SEARCH(<font color="Blue">S$1,general!$M$2:$M$364</font>)</font>),1</font>)</font>)</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">U3</th><td style="text-align:left">{=LOOKUP(<font color="Blue">9.99999999999999E+307,CHOOSE(<font color="Red">{1,2},0,INDEX(<font color="Green">general!$F$2:$F$364,MATCH(<font color="Purple">1,IF(<font color="Teal">general!$A$2:$A$364=$A3,IF(<font color="#FF00FF">ISNUMBER(<font color="Navy">SEARCH(<font color="Blue">U$1,general!$M$2:$M$364</font>)</font>),1</font>)</font>)</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[VoG]

Try

=IF(AND(U3<>"",U3<=S3),"NOT Resolved","")

 

[Aladin Akyurek]

A bit confused here. Do you wnat to say that all cells in T4:T6 must house and display a blank? If so:

Try...

=IF(COUNT(S3,T3) = 2, IF(U3 < T3, "Not Resolved", "")

 

[BradleyS]

Thank you but neither of these work

This...=IF(AND(U3<>"",U3<=S3),"NOT Resolved","") still shows all as Not Resolved

The other just shows a 0 value regardless.

If a cell in column U doesn't contain a formula everything works, as shown below in cell T6.
It seems to see the array formula as a valid date value, even though it is blank.

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>S</th><th>T</th><th>U</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">H33</td><td style="text-align: right;;"></td><td style="text-align: right;;">212</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">DX_Date</td><td style="font-weight: bold;;">?_RESOLVED</td><td style="font-weight: bold;;">RESOLVE_DATE</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">26/11/2006 </td><td style=";">NOT Resolved</td><td style="text-align: right;;">22/09/2006 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">21/06/1995 </td><td style=";">NOT Resolved</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">10/05/1999 </td><td style=";">NOT Resolved</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">18/10/1970 </td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">22/05/2003 </td><td style=";">NOT Resolved</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;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">Sheet1</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">T3</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">U3<>"",U3<=S3</font>),"NOT Resolved",""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">T4</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">U4<>"",U4<=S4</font>),"NOT Resolved",""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">T5</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">U5<>"",U5<=S5</font>),"NOT Resolved",""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">T6</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">U6<>"",U6<=S6</font>),"NOT Resolved",""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">T7</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">U7<>"",U7<=S7</font>),"NOT Resolved",""</font>)</td></tr></tbody></table></td></tr></table><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>Array 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">S3</th><td style="text-align:left">{=LOOKUP(<font color="Blue">9.99999999999999E+307,CHOOSE(<font color="Red">{1,2},0,INDEX(<font color="Green">general!$F$2:$F$364,MATCH(<font color="Purple">1,IF(<font color="Teal">general!$A$2:$A$364=$A3,IF(<font color="#FF00FF">ISNUMBER(<font color="Navy">SEARCH(<font color="Blue">S$1,general!$M$2:$M$364</font>)</font>),1</font>)</font>)</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">S4</th><td style="text-align:left">{=LOOKUP(<font color="Blue">9.99999999999999E+307,CHOOSE(<font color="Red">{1,2},0,INDEX(<font color="Green">general!$F$2:$F$364,MATCH(<font color="Purple">1,IF(<font color="Teal">general!$A$2:$A$364=$A4,IF(<font color="#FF00FF">ISNUMBER(<font color="Navy">SEARCH(<font color="Blue">S$1,general!$M$2:$M$364</font>)</font>),1</font>)</font>)</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">S5</th><td style="text-align:left">{=LOOKUP(<font color="Blue">9.99999999999999E+307,CHOOSE(<font color="Red">{1,2},0,INDEX(<font color="Green">general!$F$2:$F$364,MATCH(<font color="Purple">1,IF(<font color="Teal">general!$A$2:$A$364=$A5,IF(<font color="#FF00FF">ISNUMBER(<font color="Navy">SEARCH(<font color="Blue">S$1,general!$M$2:$M$364</font>)</font>),1</font>)</font>)</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">U3</th><td style="text-align:left">{=LOOKUP(<font color="Blue">9.99999999999999E+307,CHOOSE(<font color="Red">{1,2},0,INDEX(<font color="Green">general!$F$2:$F$364,MATCH(<font color="Purple">1,IF(<font color="Teal">general!$A$2:$A$364=$A3,IF(<font color="#FF00FF">ISNUMBER(<font color="Navy">SEARCH(<font color="Blue">U$1,general!$M$2:$M$364</font>)</font>),1</font>)</font>)</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">U4</th><td style="text-align:left">{=LOOKUP(<font color="Blue">9.99999999999999E+307,CHOOSE(<font color="Red">{1,2},0,INDEX(<font color="Green">general!$F$2:$F$364,MATCH(<font color="Purple">1,IF(<font color="Teal">general!$A$2:$A$364=$A4,IF(<font color="#FF00FF">ISNUMBER(<font color="Navy">SEARCH(<font color="Blue">U$1,general!$M$2:$M$364</font>)</font>),1</font>)</font>)</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">U5</th><td style="text-align:left">{=LOOKUP(<font color="Blue">9.99999999999999E+307,CHOOSE(<font color="Red">{1,2},0,INDEX(<font color="Green">general!$F$2:$F$364,MATCH(<font color="Purple">1,IF(<font color="Teal">general!$A$2:$A$364=$A5,IF(<font color="#FF00FF">ISNUMBER(<font color="Navy">SEARCH(<font color="Blue">U$1,general!$M$2:$M$364</font>)</font>),1</font>)</font>)</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">S7</th><td style="text-align:left">{=LOOKUP(<font color="Blue">9.99999999999999E+307,CHOOSE(<font color="Red">{1,2},0,INDEX(<font color="Green">general!$F$2:$F$364,MATCH(<font color="Purple">1,IF(<font color="Teal">general!$A$2:$A$364=$A7,IF(<font color="#FF00FF">ISNUMBER(<font color="Navy">SEARCH(<font color="Blue">S$1,general!$M$2:$M$364</font>)</font>),1</font>)</font>)</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">U7</th><td style="text-align:left">{=LOOKUP(<font color="Blue">9.99999999999999E+307,CHOOSE(<font color="Red">{1,2},0,INDEX(<font color="Green">general!$F$2:$F$364,MATCH(<font color="Purple">1,IF(<font color="Teal">general!$A$2:$A$364=$A7,IF(<font color="#FF00FF">ISNUMBER(<font color="Navy">SEARCH(<font color="Blue">U$1,general!$M$2:$M$364</font>)</font>),1</font>)</font>)</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[BradleyS]

OK worked it out, the array formula in column U still produced a date value of 00/01/1900 which is also a zero date value.

Therefore I changed the formula to =IF(AND(U4<>0,U4<=S4),"NOT Resolved","") which has fixed it.

Thank you all for your assistance.