Nesting If Statements and Dates

DStone

Hi,

I am trying to create a status column that will populate a field with the parameters below:

1. "Yes" if all of the cells in the row have "Yes" selected.
2. "Late" if the today's date is greater than the due date and all of the cells in the row don't have "Yes" selected.
3. Or else "No".

I have tried several different formulas. Some fields are populating with "Late" if the due date is blank or populating with "No" when they should actually be "Late.

This is the formula that has gotten the best results:

=IF(AND([Completed Scenario Enhancement?]@row = "Yes", [Business Testers Confirmed (Inbound Integrations)?]@row = "Yes", [Is Build Complete for Testing?]@row = "Yes", [SIT Timeline Confirmed by Vendor]@row = "Yes", [Smoke Testing Completed]@row = "Yes", [Connectivity for SIT Tenant Tested]@row = "Yes", [First Run Date]@row > TODAY()), "Yes", IF(AND([First Run Date]@row < TODAY(), NOT(CONTAINS("Yes", [Completed Scenario Enhancement?]@row)), NOT(CONTAINS("Yes", [Business Testers Confirmed (Inbound Integrations)?]@row)), NOT(CONTAINS("Yes", [Is Build Complete for Testing?]@row)), NOT(CONTAINS("Yes", [SIT Timeline Confirmed by Vendor]@row)), NOT(CONTAINS("Yes", [Smoke Testing Completed]@row)), NOT(CONTAINS("Yes", [Connectivity for SIT Tenant Tested]@row))), "Late", "No"))

Any insight would be helpful.

Dee

Kelly Moore

Hey @DStone

Before offering a nested IF solution, let's see if a COUNTIFS will work for you. This solution becomes very straight forward if the columns you need to evaluate are all contiguous and the COUNTIFS range can be written as [name of first column]@row:[name of last column]@row. By using [name of first column]@row:[name of last column]@row the range stretches across cells within a row rather than the usual cells down a column.

I'll assume the columns are contiguous.

=IF(COUNTIFS(Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row,"Yes")=6, "Yes", IF(AND(COUNTIFS(Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row,"Yes")<6, [First Run Date]@row < TODAY()), "Late", "No"))

We could make the count (=6) dynamic if desired

Does this work for you?

Kelly

DStone

Thanks Kelly. It says the formula is #UNPARSEABLE.

Dee

Kelly Moore

=IF(COUNTIFS([Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row,"Yes")=6, "Yes", IF(AND(COUNTIFS([Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row,"Yes")<6, [First Run Date]@row < TODAY()), "Late", "No"))

Unparseables typically come from parentheses, commas and wrong column names/missing brackets.

Try the above

Kelly

DStone

The formula works, but if the date field is blank, it marks the status as late. Is there a way for it to mark the status as "No" if there isn't a date?

Thanks

Dee

Kelly Moore

Will this do it?

=IF(COUNTIFS([Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row,"Yes")=6, "Yes", IF(AND(COUNTIFS([Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row,"Yes")<6, ISDATE([First Run Date]@row), [First Run Date]@row < TODAY()), "Late", "No"))

DStone

That worked! Thanks so much Kelly.

Dee

DStone

Hi Kelly,

I would like to update the formula to ignore the cells with "N/A" when it gives the status of "Yes", "No", or "Late". Is this possible?

Thanks

Dee

Kelly Moore

Will this work?

=IF(COUNTIFS([Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row,"Yes")=6, "Yes", IF(AND(COUNTIFS([Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row,"No")>0, ISDATE([First Run Date]@row), [First Run Date]@row < TODAY()), "Late", "No"))

DStone

It makes the status "No", but it should be "Yes" because the "N/A" shouldn't have an impact on the status.

Kelly Moore

aaah, gotcha

=IF(COUNTIFS([Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row,"No")=0, "Yes", IF(AND(COUNTIFS([Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row,"No")>0, ISDATE([First Run Date]@row), [First Run Date]@row < TODAY()), "Late", "No"))

Kelly Moore

or could they also be N/A there too?

DStone

The statuses are "Yes", "No", "At Risk", or "Late".

DStone

It worked for, but when I tried the complete formula below, the "At Risk" statuses became "Yes".

=IF(COUNTIFS([Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row,"No")=0, "Yes", IF(AND(COUNTIFS([Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row,"No")>0, ISDATE([First Run Date]@row), [First Run Date]@row < TODAY()), "Late", IF(AND(COUNTIFS([Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row, "Yes") < 6, ISDATE([First Run Date]@row), [First Run Date]@row <= TODAY(7)), "At Risk", "No")))

Kelly Moore

=IF(COUNTIFS([Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row,"No")=0, "Yes", IF(AND(COUNTIFS([Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row,"No")>0, ISDATE([First Run Date]@row), [First Run Date]@row < TODAY()), "Late", IF(AND(COUNTIFS([Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row, "No") >0, ISDATE([First Run Date]@row), [First Run Date]@row <= TODAY(7)), "At Risk", "No")))

DStone

It doesn't work. Should we change the order of the statements in the formula?