Help with conditional formula

Hi i am asking your help to replicate the following formula that i have in excel to SmartSheet

=IF(AND(Q2<>P2,R2="No- implementation date is at risk without external (to the project team) involvement"),"Red - Off Track",(IF(AND(Q2<>P2,R2="Yes- implementation date is still achievable"),"Yellow - At Risk",(IF(Q2<=P2,"Green - On Track",(IF(AND(Q2<>P2,OR(R2=0,R2="N/A")),"Red - Off Track","Gray - Not Started")))))))

Formula it is working on column in green. I have the same column in the SmartSheet cause this is excel in the first moment it is a pull from the data in SmartSheet but after that somebody create this column in excel and i need to have it directly in the SmartSheet. That will going to help to automate a Dashboad in PowerBI from SmartSheet. In advance i appreciate.

Find more posts tagged with

Formulas

Accepted answers

All comments

Jeff Reisman

I think you're on the right track here. Make sure your Date columns are actual date-type columns with true date values in them. Add a space after each comma, and spaces before and after operators. You probably only need 4 end-parentheses at the end of your formula. Smartsheet's color-coding of parentheses can help you make sure all interior statements are closed off as well as the exterior statements.

Replace Q2, P2, and R2 with the names of your columns, and reference @row as follows:

[Project Plan Start Date]@row

[Project Actual Start Date]@row

[Path to Green for Project Start Date?]@row

=IF(AND(Q2 <> P2, R2 = "No- implementation date is at risk without external (to the project team) involvement"), "Red - Off Track", (IF(AND(Q2 <> P2, R2 = "Yes- implementation date is still achievable"), "Yellow - At Risk", (IF(Q2 <= P2, "Green - On Track", (IF(AND(Q2 <> P2, OR(R2 = 0, R2 = "N/A")), "Red - Off Track", "Gray - Not Started"))))

Raul Cabrera

@Jeff Reisman i got "UNPARSEABLE" error, would you mind take a look below?

=IF(AND([Project Plan Start Date]@row<>[Project Actual Start Date]@row, [Path to Green for Project Start Date?]@row = "No-Implementation date is at risk without external (to project team) involvement"), "Red - Off Track", IF(AND([Project Plan Start Date]@row<>[Project Actual Start Date]@row, [Path to Green for Project Start Date?]@row = "Yes- implementation date is still achievable"), "Yellow - At Risk", IF([Project Plan Start Date]@row<=[Project Actual Start Date]@row), "Green - On Track", IF(AND([Project Plan Start Date]@row<>[Project Actual Start Date]@row, OR ([Path to Green for Project Start Date?]@row = 0, [Path to Green for Project Start Date?]@row = "N/A")), "Red - Off Track", "Gray - Not Started")))

Jeff Reisman

I think you need a forth end parentheses at the end of the formula, and maybe spaces on either side of each instance of "<>" or "<=". You may be running into trouble with your "OR" inside and embedded "AND"; I've never tried that before. There's also an extraneous ")"before "Green - On Track":

<= [Project Actual Start Date]@row), "Green - On Track",

See changes in BOLD below. If you can share a screenshot of your formula from within Smartsheet, the color-coding Smartsheet uses in formulas can help troubleshoot.

=IF(AND([Project Plan Start Date]@row <> [Project Actual Start Date]@row, [Path to Green for Project Start Date?]@row = "No-Implementation date is at risk without external (to project team) involvement"), "Red - Off Track", IF(AND([Project Plan Start Date]@row <> [Project Actual Start Date]@row, [Path to Green for Project Start Date?]@row = "Yes- implementation date is still achievable"), "Yellow - At Risk", IF([Project Plan Start Date]@row <= [Project Actual Start Date]@row, "Green - On Track", IF(AND([Project Plan Start Date]@row <> [Project Actual Start Date]@row, OR ([Path to Green for Project Start Date?]@row = 0, [Path to Green for Project Start Date?]@row = "N/A")), "Red - Off Track", "Gray - Not Started"))))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.