Nesting If Statements and Dates

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(), [First Run Date]@row < TODAY(7)), "At Risk", 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

This formula also gives "Yes" status for rows that should be "At Risk".

Can you help me understand why it may not be working and the logic for the formula?

Thanks

Dee

Kelly Moore

Hey

I'm assuming the Yes came because the first criteria, based on your original criteria, only looks at Yes's, and doesn't take into account dates. Is that the reason? If you can show a screenshot of what you got with what you expected, it will help me adjust for the new criteria you added.

Currently, the At Risk looks to see if you have at least one No and the date is within the next 7days

DStone

Yes, that is correct. The "Yes" status is not associated with a date. I would like Risk to appear if all of the cells haven't been updated in the row within seven days of the due date.

What I got:

What I expected:

DStone

Can we use a statement that says is none of the cells in the row don't have "N/A" or "Yes" within 7 days of the due date, then the status is "At Risk"?

DStone

@Kelly Moore Is it possible to use <> in the statement?

Paul Newcome

If this was working:

=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"))

and you just wanted to keep "N/A" from affecting the outputs, then this should work...

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

It isn't so much excluding "N/A". It is actually treating the "N/A" as if it is a "Yes", but it should work just the same.

DStone

Thanks @Paul Newcome. How can I add the statement to change the status to "At Risk" if the cells in the row don't have "N/A" or "Yes" within 7 days of the due date?

I used this formula and now it's working:

=IF(COUNTIFS([Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row,OR(@cell = "Yes", @cell = "N/A"))=6, "Yes", IF(AND(COUNTIFS([Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row,OR(@cell = "Yes", @cell = "N/A"))<6, 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", [Completed Scenario Enhancement?]@row:[Connectivity for SIT Tenant Tested]@row, "N/A") < 6, ISDATE([First Run Date]@row), [First Run Date]@row <= TODAY(7)), "At Risk", "No")))

Thanks for your help @Kelly Moore and @Paul Newcome !

Paul Newcome

@DStone Do you mean if none of the cells do contain either of those and we are within 7 days of the due date?

If so, that argument would look like this:

IF(AND(COUNTIFS(.......) = 0, [Due Date]@row<= TODAY(7)), "At Risk"

DStone

@Paul Newcome I tried this, but it's showing "No" instead of At Risk. The formula I used above works, but I don't understand why.

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