Redefine a formula for status and date

Changes needed to my current formula. There is just too much going on, I want to simplify this. My current formula in the "Certification Complete" is below. Now I also wish to change these colors to words:

Green - Complete

Yellow - In Progress

Red - Not Started

In addition I want the Certificate date field to be automated based on when the Certification Complete field is changed to Complete. Otherwise the date field will remain blank. Now the IF statements referencing the other fields must remain, as they will indicate "In Progress" or "Not Started" for the record.

=IF(Parent@row, IF(COUNTIF(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", "Yellow")), IF(AND(ISBLANK(Certify@row), ISBLANK(Group@row)), "Red", IF(AND(Certify@row = "Revoke", OR(ISBLANK(Comments@row), ISBLANK(Group@row))), "Yellow", IF(OR(ISBLANK(Certify@row), ISBLANK(Group@row)), "Yellow", "Green"))))

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

To change the colors to words, all you should need to do is replace each color's text in the formula with the word you want output.

The [Certification Date] can be automated using a Record a Date automation.

All comments

Paul Newcome

To change the colors to words, all you should need to do is replace each color's text in the formula with the word you want output.

The [Certification Date] can be automated using a Record a Date automation.

SherryFox

@Paul Newcome ,

Yes, Exactly what I needed. I was trying to overcomplicate it. Date workflow is set-up. Thanks!

Paul Newcome

Happy to help. 👍️

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.