RGYB Ball Formula

Hi there, Trying to create a formula where it uses date for red, yellow, green and then Complete checkbox for blue. Below is where I'm at, but it's not functioning. Any advice?

=IF(Finish@row - 3 <TODAY(), "Red", IF(Finish@row - 10< TODAY(), "Yellow", IF(Finish@row - 15< TODAY(), "Green", IFAND(Complete@row = 1,"Blue"))))]

Find more posts tagged with

Accepted answers

Sean Morgan

Hello @Maura White ,

Based on what you've provided, it looks like you want the formula to say; If the Finish date is less than 3 days from Today, put red, If the Finish date is less than 10 days from Today, put Yellow etc.

Please see the example I was able to create that should help achieve this:

=IF(Complete@row = 1, "Blue", IF(Finish@row = TODAY(-15), "Green", IF(Finish@row = TODAY(-10), "Yellow", IF(Finish@row = TODAY(-3), "Red"))))

As you can see, if the Finish date is X amount of days before Today, it will input the corresponding color. If the Complete checkbox is checked, it places the Blue RGYB ball. Lastly, if the Finish date is X amount of days before, but the Complete checkbox is checked, it will still place the Blue RGYB ball.

If you'd like it to be days greater than today, then you can remove the "-" from within the TODAY brackets. E.G TODAY(15)

Kindest Regards

Sean

All comments

Sean Morgan

Hello @Maura White ,

Please see the example I was able to create that should help achieve this:

=IF(Complete@row = 1, "Blue", IF(Finish@row = TODAY(-15), "Green", IF(Finish@row = TODAY(-10), "Yellow", IF(Finish@row = TODAY(-3), "Red"))))

If you'd like it to be days greater than today, then you can remove the "-" from within the TODAY brackets. E.G TODAY(15)

Kindest Regards

Sean

Toby W

@Sean Morgan when i attempt this formula i get an error. #UNPARSEABLE. I dont need the "completed" checkbox. What i was looking for was a formula that would essentially say "If request date is less than 15 days, green, if less than 30 days, yellow, if less than 60, red, all others are blue"

Genevieve P.

Hi @Toby W

What is the formula you're currently working with?

You could try something like this, depending on your end goal:

=IF([Request Date]@row <= TODAY(-60), "Red", IF([Request Date]@row <= TODAY(-30), "Yellow", IF([Request Date]@row >= TODAY(-15), "Green")))

Break out each statement to see what it says:

=IF([Request Date]@row <= TODAY(-60), "Red",

If the Date in the Request Date column is more than 60 days in the past, turn Red

IF([Request Date]@row <= TODAY(-30), "Yellow",

If the Date in the Request Date column is more than 30 days in the past, but less than 60 days in the past, turn Yellow

IF([Request Date]@row >= TODAY(-15), "Green", "Blue")))

If the Date in the Request Date column is between 15 days in the past, today, or in the future, return Green.

Although there's a blue statement, we won't get to that statement as all dates are covered. If this isn't what you want to do, please write out exactly what you want for each status, with a screen capture example if possible.

Cheers,

Genevieve

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.