IF Statement needs to ignore blank cell sometimes

Charles_M

Sometimes there are two approvers, sometimes only one. If there is only one and they "Confirm", I record a date column otherwise not and my workflow runs. Sometimes there are two, if they both need to "Confirm" and then I record the date column. If either rejects I don't record the date and run a workflow. The problem I am having is ignoring the second approver when not required.

Paul Newcome

What is your existing formula, how is it supposed to function?

Charles_M

@Paul Newcome I got it to work 90% how I need it too with this formula: IF([COMPLIANCE DATE ACCEPTED TENANT 1]@row = "Confirmed", IF([COMPLIANCE DATE ACCEPTED TENANT 2]@row = "Confirmed", [COMPLIANCE INSPECTION OPENING]@row, IF([COMPLIANCE DATE ACCEPTED TENANT 2]@row = "", [COMPLIANCE INSPECTION OPENING]@row)))

The only problem I have is that if Compliance Date Accepted Tenant 2 is Confirmed but Compliance Date Accepted Tenant 1 is blank it should not really return the Compliance Inspection Opening as value if true. But it's actually sort of okay because although tenant 2 should not exist if tenant 1 doesn't, it's possible there would be user error with they put the single person in as tenant 2. So the formula would work out in that case. Not sure if you know what I'm saying, sounds confusing.....

sharkasits

@Charles_M do you have other fields that would be filled in or blank based on if there are 1 or 2 tenants? If yes, I would capture if there are multiple tenants in a checkbox and then use the following:

IF([Multiple tenants]=1, IF(AND([COMPLIANCE DATE ACCEPTED TENANT 1]@row = "Confirmed", [COMPLIANCE DATE ACCEPTED TENANT 2]@row = "Confirmed"),  [COMPLIANCE INSPECTION OPENING]@row , ""), IF(OR([COMPLIANCE DATE ACCEPTED TENANT 1]@row = "Confirmed", [COMPLIANCE DATE ACCEPTED TENANT 2]@row = "Confirmed"),  [COMPLIANCE INSPECTION OPENING]@row , ""))