Health Status Formula

Carrie Lucas

I need some help with writing a formula to provide health status based on 2 dates as follows:

"Estimated Completion Date" is greater than today and "Actual Completion Date" is blank - Blue

"Estimated Completion Date" is less than today and "Actual Completion Date" is blank - Red

"Actual Completion Date" is <=> today - Grey

Also, if I change the "Health Status" column to Symbols will the colors actually show as such instead of text?

Find more posts tagged with

Accepted answers

All comments

Scott Peters

Hello @Carrie Lucas - Try this:

=if(and([Estimated Completion Date]@row>today(), [Actual Completion Date]@row=""), "Blue", if(and([Estimated Completion Date]@row<today(), [Actual Completion Date]@row=""), "Red", "Grey")))

This does not account for scenarios where Estimated Completion Date is blank, but hopefully it gets you started.

Nic Larsen

Try:

=IF(AND([Estimated Completion Date]@row > TODAY(), ISBLANK([Actual Completion Date]@row)), "Blue", IF(AND([Estimated Completion Date]@row < TODAY(), ISBLANK([Actual Completion Date]@row)), "Red", IF([Actual Completion Date]@row = TODAY(), "Gray", "")))

Carrie Lucas

Discovered this had one extra parenthesis on the end but once removed it works. Greatly appreciate the help. Thanks!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.