RAG Automation: Difference Between 2 Dates

Kaleb

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

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

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

=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

Kaleb

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

Excellent! That worked Perfectly. Thanks