Dynamic Status

Ben Donahue

A more refined and slightly more universal solution to the first posting is as follows:

=IF([% Complete]18 = 0, "NOT STARTED", IF(AND([% Complete]18 > 0, [% Complete]18 < 1), " IN PROGRESS", IF([% Complete]18 = 1, "COMPLETE", IF([% Complete]18 > 1, "INVALID ENTRY", IF([% Complete]18 < 0, "INVALID ENTRY")))))

This assumes that "% Complete" is calculated from something like "=[Task Hours Completed]18/[Task Total Hours]18"

If you are entering the "% Complete" column manually, be sure you enter your value as a decimal value between 0 and 1 and make your column a % column by selecting the column and clicking "%" in the tool bar. The above formula will work, but the following formula is all you will need:

=IF([% Complete]18 = 0, "NOT STARTED", IF(AND([% Complete]18 > 0, [% Complete]18 < 1), " IN PROGRESS", IF([% Complete]18 = 1, "COMPLETE")))

This formula will enter:

"NOT STARTED" if % Complete = 0,

"IN PROGRESS" if 0 < % Complete < 1,

"COMPLETE" if % Complete = 1 and

"INVALID ENTRY" if % Complete < 0 or if % Complete > 1.

Note on conditional formatting:

If you adjust the % values such that you have at least one of the 4 different results showing in your column somewhere ("COMPLETE", "IN PROGRESS", "NOT STARTED", "INVALID ENTRY"), then you can add rules to conditionally format the "Status" column such that, if Status is "Complete" then make the background green and the font black. I added a screenshot of my rules.

I hope this helps someone, somewhere. 

I am curious if anyone will use this. If you do use it, let me know by commenting, or by clicking the heart. This is purely for my own edification.

 

Andrée Starå

Hi Ben,

Nicely done!

Thanks for sharing!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Ben Donahue

A more refined solution is as follows:

=IF([% Complete]18 = 0, "NOT STARTED", IF(AND([% Complete]18 > 0, [% Complete]18 < 1), " IN PROGRESS", IF([% Complete]18 = 1, "COMPLETE", IF([% Complete]18 > 1, "INVALID ENTRY", IF([% Complete]18 < 0, "INVALID ENTRY")))))

This assumes that "% Complete" is calculated from something like "=[Task Hours Completed]18/[Task Total Hours]18"

The conditional formatting is still the same as I described above.

I'm curious if anyone will use this. If you do use it, let me know by responding here, or clicking the heart. Thanks.

Andrée Starå

To refine it even further you could use the @row function so you wouldn't need to think about row numbers.

=IF([% Complete]@row = 0, "NOT STARTED", IF(AND([% Complete]@row > 0, [% Complete]@row < 1), " IN PROGRESS", IF([% Complete]@row = 1, "COMPLETE", IF([% Complete]@row > 1, "INVALID ENTRY", IF([% Complete]@row < 0, "INVALID ENTRY")))))

Hope that helps!

Best,

Andrée