Mark Complete If column value is 100%

Michael Burstein

Hi Everyone,

 

I'm trying to write a formula to mark a status column one of the following based on a % complete column:

Complete If 100%
Not Started If 0%
In Progress If 0<>100%
Empty if blank

 

I'm using:

=IF([% Complete]1 = "100", "Complete", IF([% Complete]1 = "0", "Not Started", IF([% Complete]1 = "", "", "In Progress")))

 

When the % column is 0%, I get "Not Started" without issue.  However, when I mark 100% it always gives "In Progress".  I've tried changing the formula to use 100, 100%, "100%" but none of those work.

 

Anyone know how to get an IF function to do something based off a 100% value?

Brett Evans

Your 100% might = 1.00 not 100 depending on how your columns are set up.  Try changing the formula to

 

=IF([% Complete]1 = "1", "Complete", ...

Michael Burstein

Brilliant!  That fixed it.  Thanks for the help.

Bill Repucci

Old thread but this might help:

=IF(NOT(ISBLANK([Task Name]1)), IF([% Complete]1 = 1, "Completed", IF(Finish1 < TODAY(), "*** LATE ***", IF(AND([% Complete]1 > 0, [% Complete]1 < 1), "In Process", IF([% Complete]1 = 0, "Not Started")))), "")

First it looks to make sure there is a task name (I like to insert blank lines in my plans and this allows me to copy the formula all the way down to the bottom, without skipping blank lines.)

Next, it marks it complete, if it sees 100% (1).

After that, it looks to see if the end date has passed and marks it "*** LATE ***", if it has.

Next is to flag it in process or not started.  I guess you could add other things such as late starts, starting next week, due next week, etc.