formula help

[VBABEGINER]

Hi All, I need formula help. I'm sharing an excel file here and it contains 2 sheets. on one sheet i wrote down the logic and in 2nd sheet i need formula i.e. on cell BA4. Kindly guide me, it is not giving me output as per logic sheet. Can anyone pls help.

 

[VBABEGINER]

Why have you not used the formula provided by Joe ?
What is the order of the logic. ie
Do only test for Y5="" --> NA if all the other logic is false or if Y5 = "" regardless of the other criteria the answer should be NA ?
Same for Z5<>"" and Network days, is this meant to have the 0,1 reversed ? and if so should this be 0 even if all the other logic says it should be 1.

Hi Alex, Thank You for your reply. Yes, I used Joe formula first. but when I tested it, it didn't work as per my requirement. I share logic screenshot as well. Hence I thought now, instead of using today() in formula, I have now provided one date in cell D1. Let me explain, what I want.

My parent 1 condition is
- Im checking values "Obtaining Corrections (Not Sent)" and "Obtaining Corrections (Sent To Client)".
If any one value from this present in Col S cell then,

it will check 2nd sub-condition,
-if all the date cells i.e. AJ AK AL are blanks then print 1 (it mean, team needs to follow up that case)

- if all date are there then I will check one-one dates.
Like if cell AL date is close to D1 date
(on logic sheet or may be I can put that date in current sheet as well somewhere at the end column may b BZ cell)
now close means, cell AL date should not be more than 4 days from D1 date.
example - D1 will always be month end date (like either 30 or 31st) in this case 30 Sept.
If AL is 27th sept then in my answer would be 0 means I dont need a follow up. Since its less than 4 working days.
If AL is 23rd sept then it is not close to 30 sept date (more than 4 working days now) it needs follow up, hence answer is 1.

Like for other 2 column as well i.e. Col AK and Col AJ. Additionally, I'm also checking, If Col S cell contains "Query with Broker" then I shared formula separately for that. I want to include that as well in above formula/logic.

I believe now, I'm able to clear with requirement. Could you please help me on this.. Many Thanks!

 

[Alex Blakenburg]

I can't see this in what you have provided:

If Col S cell contains "Query with Broker"

 

[VBABEGINER]

I can't see this in what you have provided:

This formula

=IF(Y5="","NA",
IF(Z5="",1,
IF(AND(Z5<>"",NETWORKDAYS(Z5,D1)>4),0,1)))

 

[Alex Blakenburg]

Has no test relating to Column S

 

[VBABEGINER]

Thanks for kindness.. I will manage my own

 

[Alex Blakenburg]

I would expect it to look something like this:

=IF(OR(S4=Logic!$B$2,S4=Logic!$C$2),
    IF(AND(AJ4="",AK4="",AL4=""),
       1,
       IF(AND(
              AJ4>0,AK4>0,AL4>0,
              OR(NETWORKDAYS(AL4,TODAY())>4,
                 NETWORKDAYS(AK4,TODAY())>4,
                 NETWORKDAYS(AJ4,TODAY())>4)),
          1,
          0)),
    IF(S4="Query with Broker",
       IF(Y5="",
          "NA",
          IF(Z5="",
             1,
             IF(NETWORKDAYS(Z5,D1)>4,
                0,
                1))),
        0))