RAG Automation: Difference Between 2 Dates

Good Day Smartsheet Community:

I am Trying to Modify the Formula Below to Add Yellow if The Difference Between Plan Vs Actual is 4 Days Behind Plan, otherwise greater than 4 Days Behind Plan is Red

=IF([Milestone Date (Plan)]@row = [Milestone Date (Actual)]@row, "Green", IF([Milestone Date (Plan)]@row > [Milestone Date (Actual)]@row, "Green", IF([Milestone Date (Plan)]@row < [Milestone Date (Actual)]@row, "Red")))

Thanks

Find more posts tagged with

Accepted answers

Kelly Moore

Hey

Sorry, I misunderstood.

Try this

=IF([Milestone Date (Plan)]@row = [Milestone Date (Actual)]@row, "Green", IF([Milestone Date (Plan)]@row > [Milestone Date (Actual)]@row, "Green", IF(AND([Milestone Date (Actual)]@row > [Milestone Date (Plan)]@row, [Milestone Date (Actual)]@row <= [Milestone Date (Plan)]@row + 4), "Yellow", IF([Milestone Date (Actual)]@row > [Milestone Date (Plan)]@row + 4, "Red"))))

Kelly

All comments

Kelly Moore

Hey @Kaleb

Try this

=IF([Milestone Date (Plan)]@row = [Milestone Date (Actual)]@row, "Green", IF([Milestone Date (Plan)]@row > [Milestone Date (Actual)]@row, "Green", IF([Milestone Date (Actual)]@row = [Milestone Date (Plan)]@row + 4, "Yellow", IF([Milestone Date (Actual)]@row > [Milestone Date (Plan)]@row + 4, "Red"))))

Does that work for you?

Kelly

Kaleb

https://community.smartsheet.com/discussion/comment/353965#Comment_353965

Almost, but the Tolerance is Up to 4 Day Behind Plan is Yellow, and anything Greater than 4 Days behind Plan should turn Red. Plan = Actual is Green.

The See Screen Shot Below is 2 Days Behind Plan but The Milestone Health does not Turn Yellow:

Kelly Moore

Hey

Sorry, I misunderstood.

Try this

Kelly

Kaleb

https://community.smartsheet.com/discussion/comment/354021#Comment_354021

Excellent! That worked Perfectly. Thanks

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.