I would like to ask for help with updating status my formula:

Jean_Marc_Kruger1992

I use the below formula to return a status (Upcoming, In progress, Complete, Behind) based on % complete and due date:

=IF([% Complete]@row = 0, IF([Start Date]@row < TODAY(), "Behind", "Upcoming"), IF([% Complete]@row = 1, "Complete", IF([End Date]@row < TODAY(), "Behind", "In Progress")))

I would like add another condition (together with the above conditions): return "Requires review" when % complete is 70% and due date is not passed.

Thanks!

Nick Korna

The slight issue here is adding the 5th value as the symbols column doesn't support having 5 coloured circles unfortunately. You can get round this by adding either a symbol/emoji directly or using the UNICHAR function.

The other issue is that there aren't exact matches of the dots used in Smartsheet, so you will have to decide what works for you. For example, using a black dot instead of grey:

=IF(Status@row = "Upcoming", "Blue", (IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Requires Review", "⚫", (IF(Status@row = "Behind", "Red", "Green"))))))

Or with UNICODE:

=IF(Status@row = "Upcoming", "Blue", (IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Requires Review", UNICHAR(9899), (IF(Status@row = "Behind", "Red", "Green"))))))

However the extra one doesn't quite fit in with the others (slightly different size/position in the cell), but if needs be you can make them match more stylistically by using other symbols from the UNICHAR set.

https://www.vertex42.com/ExcelTips/unicode-symbols.html

For example:

=IF(Status@row = "Upcoming", UNICHAR(128309), (IF(Status@row = "In Progress", UNICHAR(128993), IF(Status@row = "Requires Review", UNICHAR(9899), (IF(Status@row = "Behind", UNICHAR(128308), UNICHAR(128994)))))))

Another alternative is to use the black dot (code 11044) and alter the font colour & size to get it 'close enough', but this also has the side effect of making your rows slightly larger (due to the increased font size of the cell). For example:

Is using the formula:

=IF(Status@row = "Upcoming", "Blue", (IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Requires Review", UNICHAR(11044), (IF(Status@row = "Behind", "Red", "Green"))))))

With the font colour set to the darkest grey in the column and font size 12.

Pick whichever option works for you! 😀

Nick Korna

Hi @Jean_Marc_Kruger1992,

This should do what you're after (assuming you're happy with what you currently have already):

=IF([% complete]@row = 0, IF([Start Date]@row < TODAY(), "Behind", "Upcoming"), IF([% complete]@row = 1, "Complete", IF(AND([% complete]@row = 0.7, [Start Date]@row > TODAY()), "Requires Review", IF([End Date]@row < TODAY(), "Behind", "In Progress"))))

Note that this only works for exactly 70% completion, but you can easily alter it to equal to & greater than by adding a single symbol:

=IF([% complete]@row = 0, IF([Start Date]@row < TODAY(), "Behind", "Upcoming"), IF([% complete]@row = 1, "Complete", IF(AND([% complete]@row >= 0.7, [Start Date]@row > TODAY()), "Requires Review", IF([End Date]@row < TODAY(), "Behind", "In Progress"))))

Hope this helps, post if you've any problems/queries on it.

Jean_Marc_Kruger1992

Hi Nick, it works perfectly, thank you so much for the assistance!

As a follow up question, could you please assist with the formula for the health column. I currently use the following formula to return a colour code based on the status column:

=IF(Status@row = "Upcoming", "Blue", (IF(Status@row = "In Progress", "Yellow", (IF(Status@row = "Behind", "Red", "Green")))))

I would therefore like to include an additional colour for "Requires review", e.g. gray

Thanks.

Nick Korna

The slight issue here is adding the 5th value as the symbols column doesn't support having 5 coloured circles unfortunately. You can get round this by adding either a symbol/emoji directly or using the UNICHAR function.

The other issue is that there aren't exact matches of the dots used in Smartsheet, so you will have to decide what works for you. For example, using a black dot instead of grey:

=IF(Status@row = "Upcoming", "Blue", (IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Requires Review", "⚫", (IF(Status@row = "Behind", "Red", "Green"))))))

Or with UNICODE:

=IF(Status@row = "Upcoming", "Blue", (IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Requires Review", UNICHAR(9899), (IF(Status@row = "Behind", "Red", "Green"))))))

However the extra one doesn't quite fit in with the others (slightly different size/position in the cell), but if needs be you can make them match more stylistically by using other symbols from the UNICHAR set.

https://www.vertex42.com/ExcelTips/unicode-symbols.html

For example:

=IF(Status@row = "Upcoming", UNICHAR(128309), (IF(Status@row = "In Progress", UNICHAR(128993), IF(Status@row = "Requires Review", UNICHAR(9899), (IF(Status@row = "Behind", UNICHAR(128308), UNICHAR(128994)))))))

Another alternative is to use the black dot (code 11044) and alter the font colour & size to get it 'close enough', but this also has the side effect of making your rows slightly larger (due to the increased font size of the cell). For example:

Is using the formula:

=IF(Status@row = "Upcoming", "Blue", (IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Requires Review", UNICHAR(11044), (IF(Status@row = "Behind", "Red", "Green"))))))

With the font colour set to the darkest grey in the column and font size 12.

Pick whichever option works for you! 😀

Jean_Marc_Kruger1992

Hi Nick, it works perfectly using:

=IF(Status@row = "Upcoming", "Blue", (IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Requires Review", UNICHAR(11044), (IF(Status@row = "Behind", "Red", "Green"))))))

With the font colour set to the darkest grey in the column and font size 12.

Thanks again for all the help, I really appreciate it!

Nick Korna

No problem, happy to have helped! ☺️

Jean_Marc_Kruger1992

Hi Nick, I hope you are doing well. I would like to ask a follow-up question and if you can possibly assist.

I have an update request for the same sheet as discussed above.

It still has the same elements for Health (Grey = Upcoming; Blue = Requires review; Yellow = In progress; Green = Complete) based on the output from Status column. But it now includes a Column with a checkbox named "Reviewed".

Is there a way to set up the sheet so that it includes the following conditions:

1. Reviewed checkbox column is unchecked and % Complete column is 100% : Status column returns Requires review and Health column returns the color Blue.
2. Reviewed checkbox column is checked and % Complete column is 100% : Status column returns Complete and Health column returns the color Green.

The status column therefore still has the same initial conditions based on the start/end date and % complete, but now ads the extra complexity to look at the checkbox column before it says Complete (when the checkbox is checked and at 100%) or Requires review (when checkbox is unchecked and at 100%). The Health column therefore follows and updates based on what is returned in the Status column.

Thanks for the help!

Paul Newcome

This should do the trick:

=IF([% complete]@row = 0, IF([Start Date]@row < TODAY(), "Behind", "Upcoming"), IF([% complete]@row = 1, IF(Reviewed@row = 1, "Complete", "Requires Review"), IF(AND([% complete]@row >= 0.7, [Start Date]@row > TODAY()), "Requires Review", IF([End Date]@row < TODAY(), "Behind", "In Progress"))))

Note on colored indicators: I use the UNICHAR(11044) and then include conditional formatting to automatically change the color, size, etc..