Link % completion and Status fields

nozawa51306

We have 2 columns.

1) % completion 

2) Status (not started, in progress, completed, significant delay, etc).

 

We would like that every time we move % Completion from 0 to any number up to 100, Status gets automatically updated to "in progress". And when % completion hits 100%, then Status is "Completed"

 

We will still want to manually override when a task gets signficantly delayed.

Does anyone know how to do this?

Stefan 7k

Hi,

typical usecase for a nested IF statement.

I use this in a similar case.

=IF(AND([% done]1 > 0; [% done]1 < 100); "in progress"; IF([% done]1 = 100; "Done"; ""))

Greetings

Stefan

Paul Newcome

Also keep in mind...

 

If you manually update it, then it will remove the formula, and you will have to re-enter it if you wanted the automation to resume.

 

And the ; should also be changed to a , in the formula above. Another recommendation is changing the row reference from the row number to @row. The first is a MUST. The second is simply a recommendation.

Stefan 7k

Paul,

when your Smartsheet is set to other languages (in my case German), the separator is not , but ; !

Internally this is changed by the parser, but while entering formulas, I have to use ; .

Greetings

Stefan

Paul Newcome

Oh wow! Learn something new everyday! Thanks for the info, and I will certainly keep that in mind.

My apologies for the incorrect assumption.

Katye Reed

Hello, I am struggling to get this to work. I am trying to apply this for 3 statuses (Not started, In Progress and Completed) so I thought a nested IF statement would work. Nothing works. I even tried the formula posted above, nothing.

Any suggestions? You're help is greatly appreciated.

 

Stefan 7k

Hi Katye,

try to type the formula by yourself instead of copying.

A few times I found that copied formulas did not work in a given sheet, even though the syntax and everything was correct.

When I typed the same formula manually it worked. I guess the parser/code interpreter from smartsheet has something to do with that.

Greetings

Stefan

Paul Newcome

I've had the same issue with copy/pasting formulas from an outside source (but not often). For your specific case you will want to use:

=IF(AND([% done]1 > 0; [% done]1 < 100); "In progress"; IF([% done]1 = 100; "Complete"; "Not Started"))

If your column is formatted as an actual percentage (as opposed to text with the % manually added) you will also need to adjust your values. Smartsheet reads percentages as a decimal.

0% = 0

1% = .01

99% = .99

100% = 1

Also keep in mind Stefan's post regarding the separators used.

Katye Reed

Hi Paul,

It worked, once I used the percentage conversions per your suggestion. 

Thanks so much for your help.

Katye

 

 

Katye Reed

Hi Stefan -

 

Thank you so much! You and Paul are extremely helpful.

JasonRiley80

I made mine work with this statement

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