Formula skipping rows

Hi all,

I'm trying to use the below function to automatically change the color of Harvey balls to red, yellow, or green based on the length of time till a task's due date.

=IF([Due Date]@row >= TODAY(+61), "Green", IF([Due Date]@row >= TODAY(=31), "Yellow", "Red"))

However, for some reason some rows show "#invalid operation" rather than a colored ball.

I've reached out to support but haven't received any direction on how to adjust my formula. So any help or direction would be greatly appreciated.

Thank you!

Find more posts tagged with

Formulas

functionality

Accepted answers

Paul Newcome

Try this...

=IF([Due Date]@row <> "", IF([Due Date]@row >= TODAY(61), "Green", IF([Due Date]@row >= TODAY(31), "Yellow", "Red")))

Paul Newcome

You would use something like this...

=IF(Standing@row = "Complete", "Blue", IF([Due Date]@row <> "", IF([Due Date]@row >= TODAY(61), "Green", IF([Due Date]@row >= TODAY(31), "Yellow", "Red"))))

All comments

Paul Newcome

Try this...

=IF([Due Date]@row <> "", IF([Due Date]@row >= TODAY(61), "Green", IF([Due Date]@row >= TODAY(31), "Yellow", "Red")))

Tina Gigioli

Thank you, Paul! You're a rock-star. :)

Tina Gigioli

@Paul Newcome , what would I add if I wanted anything marked as "complete" in a column labeled "standing" to show as blue?

I've tried playing around with it myself, but no luck.

Paul Newcome

You would use something like this...

=IF(Standing@row = "Complete", "Blue", IF([Due Date]@row <> "", IF([Due Date]@row >= TODAY(61), "Green", IF([Due Date]@row >= TODAY(31), "Yellow", "Red"))))

Tina Gigioli

Thank you so much, Paul! Greatly appreciate your help. :)

Paul Newcome

Happy to help. 👍️

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.