Multiple nested IF/and to show condition Met or Not Met

**suchetherrah** · 09-17-2017, 02:48 PM

Hi,

Based on the below formula, if Status = Success, SchedFinish is 24/3/16, ActDate is 14/9/17 (more that 14 days) result is show correctly as "Not Met".
But if SchedFinish is 15/9/17 and ActDate is 14/9/17 or blank (less than 14 days) result should show as "Met".
However, it still shows as "Not Met" which is not right.

=IF([@XCRSTAGE]="IMPLEMENTATION",IF([@Status]<>"",IF(OR(NETWORKDAYS([@SCHEDFINISH],[@ActualDate])>=14,NETWORKDAYS([@SCHEDFINISH],TODAY())>=14),"Not Met","Met")))

Based on this formula, please anyone can advise how should I correct this formula. Thank you.

Regards
Chitra

**gmr4evr1** · 09-17-2017, 03:07 PM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**suchetherrah** · 09-17-2017, 10:43 PM

Hi,

Oops Sorry, below is the revised formula set in excel which is not working.

=IF([@XCRSTAGE]="IMPLEMENTATION",IF(OR(IF(AND([@[Status_Imp]]="",NETWORKDAYS(TODAY(),[@SCHEDFINISH])>=14),AND([@[Status_Imp]]<>"",NETWORKDAYS([@ActDate],[@SCHEDFINISH])>=14))),"Met","Not Met"),"")

As requested, attached is the excel sheet.

**FlameRetired** · 09-17-2017, 11:12 PM

suchetherrah

I am confused. Upload says rows 5 and 7 "Should be Not Me". That is what the formula is returning in F5 and F7.

PS Also the formula states IF(AND([@[Status_Imp]]="",NETWORKDAYS(TODAY(),[@SCHEDFINISH])>=14). Column D ([@[Status_Imp]]) has ' (single quote characters) in all the cells. That AND function will never be TRUE.

PSS NETWORKDAYS(TODAY(),[@SCHEDFINISH])>=14 is never TRUE. NETWORKDAYS(TODAY(),[@SCHEDFINISH]) is returning all negative numbers.

**suchetherrah** · 09-18-2017, 06:54 AM

Sorry for the confusion.

Yes, it should be "Met". Reattached the file to avoid confusion.
Eventhough, I have removed the Status_IMP = ', it's still not working.

**FlameRetired** · 09-18-2017, 03:07 PM

suchetherrah I am having great difficulty understanding the intended logic in the formula under ClosureSLA.

I've tried guessing and some trial and error "fixes" to duplicate what you intend. They have not worked.

All I have now are some observations regarding details. These need to be addressed. They are causing difficulties in your formula.

This is what I observe:

ActDate returns a #NAME? error. I should be [@ActDate].
In the event [@ActDate] is blank (which is usually the case) NETWORKDAYS returns a #VALUE! error. [@ActDate] needs to be a number. N([@ActDate]) will coerce that value into a 0 where there are blanks or text.
NETWORKDAYS(TODAY(),[@SCHEDFINISH]) always returns a negative value. Perhaps it should be NETWORKDAYS([@SCHEDFINISH],TODAY())???
This IF segment doesn't appear to do anything ... IF(AND([Status_Imp]="",NETWORKDAYS(TODAY(),[@SCHEDFINISH])>=14),AND([Status_Imp]<>"",NETWORKDAYS(ActDate,[@SCHEDFINISH])>=14))) ...

Beyond that I am unable to "help". Sorry.

**suchetherrah** · 09-19-2017, 07:00 AM

Hi,

After your have highlighted, just realized my logic of the formula is not correct. So separate both formulas in different senarios. Trying to achieved by combining both the formulas in 1 cell.

=IF([@XCRSTAGE]="IMPLEMENTATION",IF([@ActDate]="",IF(NETWORKDAYS([@SCHEDFINISH],TODAY())>=14,"Not Met","Met")))
=IF([@XCRSTAGE]="IMPLEMENTATION",IF([@ActDate]<>"",IF(NETWORKDAYS([@SCHEDFINISH],[@ActDate])>=14,"Not Met","Met")))

Tried number of times combining but didn't work. Please help is really appreciated.

Thank you.

**JeteMc** · 09-19-2017, 07:54 PM

As to combining the formulas that could be:

Formula:

Please Login or Register  to view this content.

As to cell F5 still displaying 'Not Met' that is the logical result because E5 is blank, and the number of net work days between Feb. 13th, 2016 (A5) and Sept. 19th, 2017 (TODAY()) is 417.
As to cell F7 still displaying 'Not Met' that is the logical result because E7 isn't blank and the number of net work between Feb. 26th, 2016 (A7) and Sept. 15th, 2017 (E7) is 406.
I feel like the logic of displaying 'Met' or 'Not Met' needs to be reevaluated.
Let us know if you have any questions.

Multiple nested IF/and to show condition Met or Not Met

LinkBack

Thread Tools

Rate This Thread

Display

Multiple nested IF/and to show condition Met or Not Met

Re: Multiple nested IF/and to show condition Met or Not Met

Re: Multiple nested IF/and to show condition Met or Not Met

Re: Multiple nested IF/and to show condition Met or Not Met

Re: Multiple nested IF/and to show condition Met or Not Met

Re: Multiple nested IF/and to show condition Met or Not Met

Re: Multiple nested IF/and to show condition Met or Not Met

Re: Multiple nested IF/and to show condition Met or Not Met

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Multiple IF Statements (Nested If???) to show different outcomes

Nested IF/Multiple Condition IF

Complex Nested Condition

[SOLVED] Nested If condition

[SOLVED] Nested If condition

[SOLVED] Nested IF Condition

[SOLVED] Need Help with Multiple Condition Nested Logical Functions

Bookmarks

Bookmarks

Posting Permissions