Nesting If Statements and Dates
Hi,
I am trying to create a status column that will populate a field with the parameters below:
- "Yes" if all of the cells in the row have "Yes" selected.
- "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.
- 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
Answers
-
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
-
Thanks Kelly. It says the formula is #UNPARSEABLE.
Dee
-
=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
-
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
-
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"))
-
That worked! Thanks so much Kelly.
Dee
-
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
-
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"))
-
It makes the status "No", but it should be "Yes" because the "N/A" shouldn't have an impact on the status.
-
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"))
-
or could they also be N/A there too?
-
The statuses are "Yes", "No", "At Risk", or "Late".
-
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")))
-
=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")))
-
It doesn't work. Should we change the order of the statements in the formula?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!