If and Formula tracking date, progress with ryg status dashboard

Digital_Kru

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 

Enjoy!

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

 

Chris McKay

Well done! I've been planning to post similar topics myself. Thanks for sharing.