Sheet Formula_ Heath symbol

Jermaine Johnson

Hello everyone,

I know I am close, but looking for some help. I am trying to create a formula to calculate

If start date is less than today, health symbol is Green

If start (maybe should be end date) is within (xx) days, health symbol turns yellow

If start date (maybe should be end date) is greater than today, turns Red

If % of completion is 100%, health symbol turns blue

Examples

=IF([Start Date]@row < TODAY(), "Green", IF(AND([Start Date]@row >= TODAY(), [Start Date]@row < TODAY(+7)), "Yellow", "Red"))

=IF([% Complete]@row, =100, "Blue", IF([Start Date]@row < TODAY(), "Green", IF(AND([Start Date]@row >= TODAY(), [Start Date]@row < TODAY(+7)), "Yellow", "Red")))

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

hollyconradsmith

It looks to me like you will only have "green" projects if they are prior to starting. Is that correct? Or should you base it off the finish date?

What is the issue or error you are running into with the formulas you posted?

Jermaine Johnson

@hollyconradsmith - I believe that is working correctly, however the formula for turning health symbol to blue with % of complete is 100% I am having a hard time figuring out

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.