RAG Automation: Difference Between 2 Dates

Options

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

Best Answer

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

    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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!