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")))


Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓


    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"))))



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!