Harvey Ball status based on due date

Greetings

Hope everyone is doing well. I have a need for some guidance. I've used a similar formula in the past but I am having issues with this one. What am I trying to do: I have a status column and RTDD date column and Symbol column for the Harvey Balls (red, yellow, green, and gray)

I'm looking to have the balls color adjust based on the following criteria
If status is closed = Gray

If status is anything but closed and the dates are

within 7 days from due date = Red

within 14 days from due date = Yellow

All else is green

Formula I've crafted is below but I am getting the infamous Unparseable error

=IF(Order Status@row = "Closed", Gray, If(AND([RTDD Date]@row < TODAY(+7), Order Status@row <> "Closed"), "Red", IF(AND([RTDD Date]@row <= TODAY(+14), [TDD Status]@rowOrder status ]@row <> "Closed"), "Yellow", "Green")))

Any help is appreciated

Find more posts tagged with

Accepted answers

Akblevins

In theory you don't need to have the ANDs saying the Order status is not closed because you caught all of the closed ones at the beginning.

The next thing I noticed is in your pasted formula you included a TDD Status - is that needed?

Here's what I think you were trying to go for and I'm not sure if it was syntax or maybe the "+" in the TODAY function:

=IF([Order Status]@row = "Closed", "Gray", IF([RTDD Date]@row < TODAY(7), "Red", IF([RTDD Date]@row <= TODAY(14), "Yellow", "Green")))

All comments

DKazatsky2

Hi @AJ Cruz,

At first glance, I can see that Order Status@row should be [Order Status]@row.

AJ Cruz

Hello @DKazatsky2
Tried it and still get unparseable error

By the way, the statuses are Release, Managed, Acceptance, and Closed

Akblevins

In theory you don't need to have the ANDs saying the Order status is not closed because you caught all of the closed ones at the beginning.

The next thing I noticed is in your pasted formula you included a TDD Status - is that needed?

Here's what I think you were trying to go for and I'm not sure if it was syntax or maybe the "+" in the TODAY function:

=IF([Order Status]@row = "Closed", "Gray", IF([RTDD Date]@row < TODAY(7), "Red", IF([RTDD Date]@row <= TODAY(14), "Yellow", "Green")))

Paul Newcome

There were a couple of issues.

Square brackets needed around the first [Order Status]@row.

Quotes around "Gray".

Square Brackets around the second [Order Status]@row.

Pick one column from [TDD Status]@rowOrder status ]@row (part of the second AND function).

However… Nested IF formulas work from left to right and stop on the first "true" statement. This means that once we are evaluating the second IF, we can assume the first IF is false. Once we get to the third IF, we can assume the first and second IFs are both false.

This means you do not have to explicitly state that the first is false when you are writing the second. In this case, if [Order Status]@row = "Closed", we output "Gray". If it moves past that, all other IFs automatically assume that [Order Status]@row <> "Closed", so you do not have to state that in the IFs that follow.

Keeping that logic in mind, the nested IF provided by @Akblevins is the way I would go as well.

@Akblevins The + inside of the TODAY function is ok. It accepts both + and -, but if it is left out, it is assumed to be a positive number.

Akblevins

Ahh I was trying to make the edits in the formula provided above and it kept giving me problems so I did what I enjoy and started from scratch and at the beginning. I didn't realize the quotes - which is probably what was catching me in the first place with the OG formula.

I break it into chunks and add little by little, ensuring my formula hasn't failed until I get to my destination ☺️

And good to know about having the plus sign or not! I learn something new every day

Paul Newcome

@Akblevins

I break more complex formulas out too.

I will put each piece into a different cell so I can keep an eye on every part while changing the data feeding the formula to make sure everything is operating as expected.

Then I will write out a compiled formula using cell references to the individual formulas.

Finally, once all of that is working, I will copy/paste the individual formulas into the compiled formula where I have each cell reference.

Doing it this way helps with those pesky parenthesis and commas.

Here is a really basic example of how I break things out:

I have learned the hard way that I like to have the [Cell References] formula in place that does not get edited when copy/pasting. That way if something does go weird or I accidentally highlight the wrong thing, I have the original syntax of the [Cell References] formula that I can restart from.

AJ Cruz

Thank you @Akblevins
It worked as intended and yes, pasting the formula this time around in the post was a chore so I manually spaced and deleted

@Paul Newcome

Thank you for the clarification on the logic. Makes sense and missed it as I rarely use nested. Good suggestion on breaking out the more long and complex formulas

Great day gents

Paul Newcome

@AJ Cruz No worries at all. Keeping that logic in mind can really help simplify IF statements by being careful about which order you put them in.

And breaking out formulas is the only way I keep my sanity some days when I am trying to tackle something super complex. Depending on how complex, I don't always combine all of the parts. Sometimes I will leave it spread out over a couple of columns to help with future troubleshooting / maintenance if it is ever needed.

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.