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]

Microsoft® Excel® for Microsoft 365 MSO (Version 2407) 64-bit

What version of Excel are you using? There are some new functions available in the newer versions that could make this a bit easier.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Joe4]

Thanks. It would be great if you updated your profile, as described in the link I provided in my previous post. Then people could always see it and not need to ask.

 

[Joe4]

See if this does what you want:

=IF(OR(S4=Logic!$B$2,S4=Logic!$C$2),
IF(OR(AND(AJ4="",AK4="",AL4=""),
AND(AJ4>0,AK4>0,AL4>0,NETWORKDAYS(AL4,TODAY())>4),
AND(AJ4>0,AK4>0,NETWORKDAYS(AK4,TODAY())>4),
AND(AJ4>0,NETWORKDAYS(AJ4,TODAY())>4)),1,0),"")

 

[VBABEGINER]

See if this does what you want:

=IF(OR(S4=Logic!$B$2,S4=Logic!$C$2),
IF(OR(AND(AJ4="",AK4="",AL4=""),
AND(AJ4>0,AK4>0,AL4>0,NETWORKDAYS(AL4,TODAY())>4),
AND(AJ4>0,AK4>0,NETWORKDAYS(AK4,TODAY())>4),
AND(AJ4>0,NETWORKDAYS(AJ4,TODAY())>4)),1,0),"")

Thank You Joe, for your kind help. I appreciate it. Working better for me now. Thanks for the help again.

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[VBABEGINER]

Hi Joe, would you be able to help me with one more query which I posted in blog. Subject name is "Macro help". Would highly appreciate if anything you can suggest me on the same.

 

[Joe4]

Hi Joe, would you be able to help me with one more query which I posted in blog. Subject name is "Macro help". Would highly appreciate if anything you can suggest me on the same.

It looks like you already got multiple people, including an MVP, trying to help you over there.
I really do not have the time right now to read through the 5 pages of posts on that question, so I don't think I can help you there.

 

[VBABEGINER]

See if this does what you want:

=IF(OR(S4=Logic!$B$2,S4=Logic!$C$2),
IF(OR(AND(AJ4="",AK4="",AL4=""),
AND(AJ4>0,AK4>0,AL4>0,NETWORKDAYS(AL4,TODAY())>4),
AND(AJ4>0,AK4>0,NETWORKDAYS(AK4,TODAY())>4),
AND(AJ4>0,NETWORKDAYS(AJ4,TODAY())>4)),1,0),"")

Hi Joe, This is not correct. I tested it.
Can you correct this please..

=IF(AND(OR(S5=Logic!$B$2,S5=Logic!$C$2),IF(AND(AJ5="",AK5="",AL5=""))),1,
IF(AND(OR(S5=Logic!$B$2,S5=Logic!$C$2),IF(AND(AJ5>0,AK5>0,AL5>0,NETWORKDAYS(AL5,TODAY())))>4),1,
IF(AND(OR(S5=Logic!$B$2,S5=Logic!$C$2),IF(AND(AJ5>0,AK5>0,AL5>0,NETWORKDAYS(AK5,TODAY())))>4),1,
IF(AND(OR(S5=Logic!$B$2,S5=Logic!$C$2),IF(AND(AJ5>0,AK5>0,AL5>0,NETWORKDAYS(AJ5,TODAY())))>4),1,
0))))

and, I want to add below formula in this as well..

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

 

[VBABEGINER]

attached is the logic page screenshot.
and sharing excel file xl2bb.

report.xlsx
	S
1	Policy Status
Raw Data

 

[Alex Blakenburg]

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.