I am trying to combine 2 if(or) functions but can't seem to figure it out. I currently have them on 2 different columns but need to combine the formulas to one column so I can then use an autofilter in a macro to filter out all true results.

Here are the 2 formulas:

#1 =IF(OR(J7<NOW()+13,E7="PENDDECL",E7="NOIA",E7="PENDCI",K7="",R7=""),AND(W7="Purchase"))#2 =IF(OR(L7>45,E7="PENDDECL",E7="NOIA",E7="PENDCI",K7="",R7=""),AND(W7="Refinance"))

any help would be greatly appreciated :-)

✔ Best Answer

The WORKDAY() function should work. Something like: =OR(AND(OR(J8<

WORKDAY(TODAY(),16),E8={"PENDDECL","NOIA","PENDCI"},K8="",R8=""),W8="Purchase"),AND(OR(L8>44,E8={"PENDDECL","NOIA","PENDCI"},K8="",R8=""),W8="Refinance"))If you want to include Holidays,just add the Holiday range

to the end:=WORKDAY(TODAY(),16,Holidays)

MIKE

Just a quick guess, but try this: =IF(OR(J7<NOW()+13,L7>45,E7="PENDDECL",E7="NOIA",E7="PENDCI",K7="",R7=""),AND(OR(W7="Purchase",W7="Refinance")))

MIKE

This almost worked except for the 2 mathematical parts as they are contingent on whether or not it is a purchase or a refinance. if it is a purchase I have to evaluate J7<NOW()+13 but L7>45 does not apply

if it is a refinance I have to evaluate L7>45 but J7<NOW()+13 does not applyall other logical tests do apply to both purchase and refinance.

Try combining the J7<NOW()+13 and the W7="Purchase" in an AND()

then the L7>45 and the W7="Refinance" in another AND()

then OR() the two sectionsSomething like:

=IF(AND(E7="PENDDECL",E7="NOIA",E7="PENDCI",K7="",R7=""),AND(OR(AND(J7<NOW()+13,W7="Purchase"),AND(,L7>45,W7="Refinance"))))

MIKE

message edited by mmcconaghy

I got it to work using this formula: =OR(AND(OR(J7<NOW()+13,E7={"PENDDECL","NOIA","PENDCI"},K7="",R7=""),W7="Purchase"),AND(OR(L7>45,E7={"PENDDECL","NOIA","PENDCI"},K7="",R7=""),W7="Refinance"))

Glad you got it solved, here is another try that is similar to your solution,

but it checks the E7 cell only once.=IF(OR(AND(J7<NOW()+13,W7="Purchase"),AND(,L7>45,W7="Refinance")),AND(E7="PENDDECL",E7="NOIA",E7="PENDCI",K7="",R7=""))

Getting all the ANDs and ORs in the correct place is a pain.

MIKE

message edited by mmcconaghy

I was able to use a formula that worked but just realized that I need to count business days only. Here is my formula:

=OR(AND(OR(J8<NOW()+16,E8={"PENDDECL","NOIA","PENDCI"},K8="",R8=""),W8="Purchase"),AND(OR(L8>44,E8={"PENDDECL","NOIA","PENDCI"},K8="",R8=""),W8="Refinance"))Is there a way to make the J8<NOW()+16 only count business days?

The WORKDAY() function should work. Something like: =OR(AND(OR(J8<

WORKDAY(TODAY(),16),E8={"PENDDECL","NOIA","PENDCI"},K8="",R8=""),W8="Purchase"),AND(OR(L8>44,E8={"PENDDECL","NOIA","PENDCI"},K8="",R8=""),W8="Refinance"))If you want to include Holidays,just add the Holiday range

to the end:=WORKDAY(TODAY(),16,Holidays)

MIKE

Thanks, final working formula: =OR(AND(OR(J7<WORKDAY(TODAY(),16,Validation!F12:F22),E7={"PENDDECL","NOIA","PENDCI"},K7="",R7=""),W7="Purchase"),AND(OR(L7>44,E7={"PENDDECL","NOIA","PENDCI"},K7="",R7=""),W7="Refinance"))

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History