# 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

• ✭✭✭✭✭✭
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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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:

• ✭✭✭✭✭✭
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

• ✭✭✭✭
Options

Excellent! That worked Perfectly. Thanks

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!