Health column?

Hello,

I'm attempting to create a function in my "Health" column that will return Red, Yellow, or Green based on:

1) Status (dropdown input by sheet user)

2) Dates in the date columns to the right, broken down by step

We are attempting to track communications through various review processes. Some require legal review, and some do not. How do I make the health column reflect:

Green: Status Column is Not Started + Draft Due date is more than 7 days from today

Green: Status Column is Draft in Progress + Draft Due date is not more than 7 days from today

Green: Status Column is Fx Review in Progress + Fx Review Due date is in the future

Green: Status Column is Legal Review in Progress + Legal Review Due is in the future

Green: Status Column is Awaiting Distribution OR Distribution Complete + Distribution Date is in the future

Green: Status Column is Distribution Complete

Yellow: Status Column is Not Started + Draft Due date is 7 or fewer days from today

Red: Status Column is Not Started + Draft Due date is in the past

Red: Status Column is NOT Distribution Complete + Distribution Date is in the past

Yellow: Status Column is Draft in Progress + Draft Due date is in the past

Yellow: Status Column is Fx Review in Progress + Fx Review Due date is in the past

Yellow: Status Column is Legal Review in Progress + Legal Review Due is in the past

Yellow: Status Column is Awaiting Distribution + Distribution Date is in the past

Image 1-29-21 at 10.18 AM.jpeg

Thank you,

Bridget

Find more posts tagged with

Accepted answers

Bridget Jones

IT WORKED! Thank you :)

All comments

Debbie Sawyer

Glad we were able to get your sorted :)

Have a great day!

Bridget Jones

Have a wonderful evening, Debbie my hero! :)

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.