If and Formula tracking date, progress with ryg status dashboard


To share back with the community here, I'd like to share some key formulas that I've been helped with by the community and extensive testing/fixing.

I have a sheet that tracks:

  • progress for items based on user inputs of %age complete
  • compared to the progress they should have completed so far (%age expected as of the current date compared to the start date/due dates)
  • and gives you a detailed answer for what that progress means (good, delayed, off-track, completed, not started, etc.)
  • and an associated formula that gives you a RYG(and Gray for not started) associated with the prior formula.


Detailed status formula:

=IF(AND([Calendar Due Date]@row < TODAY(), [Actual Progress]@row < 1), "NOT COMPLETE - OVERDUE", IF(AND([Actual Progress]@row = 1, [Date Completed]@row = ""), "ENTER COMPLETION DATE", IF(AND(([Calendar Due Date]@row - TODAY()) >= 0, [Actual Progress]@row <> 1, ([Planned Percent Complete Per Timeline]@row - [Actual Progress]@row) >= 0.25), "In Progress - Off Track", IF([Date Completed]@row - [Calendar Due Date]@row > 0, "Completed - Late", IF(AND([Planned Percent Complete Per Timeline]@row = 1, [Actual Progress]@row = 1), "Completed - On Time", IF(AND([Days Until Start]@row > 0, ([Actual Progress]@row - [Planned Percent Complete Per Timeline]@row) <= 0.1), "Not Started", IF(AND(([Planned Percent Complete Per Timeline]@row - [Actual Progress]@row) > 0.1, ([Planned Percent Complete Per Timeline]@row - [Actual Progress]@row) < 0.25), "In Progress - Delayed", "In Progress - Good")))))))

Dashboard (RYGG) formula:

=IF([Detailed Status]@row = "In Progress - Delayed", "Yellow", IF(OR([Detailed Status]@row = "In Progress - Off Track", [Detailed Status]@row = "NOT COMPLETE - OVERDUE"), "Red", IF(OR([Detailed Status]@row = "Completed - On Time", [Detailed Status]@row = "Completed - Late", [Detailed Status]@row = "In Progress - Good"), "Green", "Gray")))

Hope this helps some of you out with your formulas.

These may not be the most succinct formulas, but they work wink


p.s. I know the "Not Started" only shows up for when it's prior to the start date, and that the other parts of the formula start showing results even if the user-entry %age is still 0% but the start date is already at/past today's date (this is ok for my purposes, since I want to know what the status is compared to my start dates, you may not like this, and if you find a way to do that in addition to this please post it for others use). 




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!