nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Automating RGY Balls For Date Ranges

h.sidey.sps

Fairly new to this and while I did look apologies if I miss this previously answered. I'm trying to align colors with an upcoming due date (lease expiration to be exact). I'd like to show blue as over two years out, green as 1 to 2 years out, yellow as 6 months to 1 year out and red as less than 6 months out.

I've gotten this formula to work -

=IF([Lease Expires]@row > TODAY(730), "Blue", IF(AND([Lease Expires]@row < TODAY(729), [Lease Expires]@row > TODAY(365), "Green", IF(AND([Lease Expires]@row < TODAY(364), [Lease Expires]@row > TODAY(181)), "Yellow", IF([Lease Expires]@row < TODAY(180), "Red", "Blue")))))

But whenever i try to add an AND for the last IF as below, I get an error. I've tried TODAY and TODAY(1) just in case.

=IF([Lease Expires]@row > TODAY(730), "Blue", IF(AND([Lease Expires]@row < TODAY(729), [Lease Expires]@row > TODAY(365), "Green", IF(AND([Lease Expires]@row < TODAY(364), [Lease Expires]@row > TODAY(181)), "Yellow", IF(AND([Lease Expires]@row < TODAY(180), [Lease Expires]@row > TODAY)), "Red", "Blue")))))

Ideally I'd also like to make past due blank, but getting errors there too, so set as blue for now.

Find more posts tagged with

Accepted answers

h.sidey.sps

It didn't' work at first but I found they why. For one of the And's the ) was at the far end instead of end of that phrase. Below is what worked

=IF([Lease Expires]@row > TODAY(730), "Blue", IF(AND([Lease Expires]@row < TODAY(729), [Lease Expires]@row > TODAY(365)), "Green", IF(AND([Lease Expires]@row < TODAY(364), [Lease Expires]@row > TODAY(181)), "Yellow", IF(AND([Lease Expires]@row < TODAY(180), [Lease Expires]@row > TODAY()), "Red", ""))))

All comments

Megan Ryzenga

Hello @h.sidey.sps !

I think you might be missing a parenthesis in your formula on the last IF(AND()) function:

=IF([Lease Expires]@row > TODAY(730), "Blue", IF(AND([Lease Expires]@row < TODAY(729), [Lease Expires]@row > TODAY(365), "Green", IF(AND([Lease Expires]@row < TODAY(364), [Lease Expires]@row > TODAY(181)), "Yellow", IF(AND([Lease Expires]@row < TODAY(180), [Lease Expires]@row > TODAY()), "Red", "Blue")))))

h.sidey.sps

That only fixed part. I get the blue for some but not all. Other colors are gone.

h.sidey.sps

It didn't' work at first but I found they why. For one of the And's the ) was at the far end instead of end of that phrase. Below is what worked

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.