Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

    ✭✭
    in Formulas and Functions

    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!

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • Community Champion
      Answer ✓

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

      image.png

      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.

      Huge List of Unicode Character Symbols

      https://www.vertex42.com/ExcelTips/unicode-symbols.html
      This page provides a set of tables for Unicode character symbols, including emojis, emoticons, arrows, music, sports, etc.


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

      image.png

      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:

      image.png

      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! 😀

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion

      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.

      · Share on FacebookShare on Twitter
    • ✭✭

      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.

      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓

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

      image.png

      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.

      Huge List of Unicode Character Symbols

      https://www.vertex42.com/ExcelTips/unicode-symbols.html
      This page provides a set of tables for Unicode character symbols, including emojis, emoticons, arrows, music, sports, etc.


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

      image.png

      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:

      image.png

      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! 😀

      · Share on FacebookShare on Twitter
    • ✭✭

      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!

      · Share on FacebookShare on Twitter
    • Community Champion

      No problem, happy to have helped! ☺️

      · Share on FacebookShare on Twitter
    • ✭✭

      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!

      · Share on FacebookShare on Twitter
    • Community Champion

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

      CERTIFIED SMARTSHEET PLATINUM PARTNER

      10xViz

      Would you benefit from getting my help in person?

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions

    • Formula Help

      I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
      User: "Allan Z"
      Answered ✓
      Formulas and Functions
      8
      2

    • Unparseable Error

      Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
      User: "stratman"
      Answered ✓
      Formulas and Functions
      15
      2

    • Formula to return the non-blank cell

      I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …
      User: "m_anderson"
      Answered ✓
      Formulas and Functions
      13
      2