How to use Formulas to change the value of Symbols and Check Boxes?

smuhammad41001
edited 12/09/19 in Formulas and Functions

I'm kinda new to this. I'm a little familiar with how formulas worked in Microsoft Excel, but not a pro in the slightest. I only know basic stuff.

How do you use a formula to change the value of symbols and check boxes and drop down menus? Also, I've been having trouble pulling the data from dates. I always get the #UNPARSEABLE error.

Here is my formula: = IF(MONTH([Column2]1) = 3, "X',"XX") What's wrong with my syntax?

Tags:

Comments

  • JamesR
    JamesR ✭✭✭✭✭✭

    Checks a box it finds the word color in the Task_Name cell    

    =IF(FIND("color", Task_Name1) > 0, 1, 0)

    Formula in date columns expect  the returned value to be a date, so it it returns a number it must be converted to text to sow or it will try and convert it to a date.

    The formulae you show appears ok except if Column2 is not a date column. If that is the case then it is treating the cell as text.

  • smuhammad41001
    edited 03/12/18

    Is that formula for all of the mentioned types? If not, which parts of the formula should I change so it works for other data types?

    Also I checked to see if the data type for Column2 is date, and it is, but the formula is in a different column.

    (Edit: Apparently there was a single quote in the formula, and that was the only issue.)

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Is Column 2, the actual name of your column? You want to use the column name in there. 

  • JamesR
    JamesR ✭✭✭✭✭✭

    smuhammed

    There are many combinations /uses of the iIF Function, too many to outline here.  The help is  here:

    https://help.smartsheet.com/function/if?frame=0&nav=1

    Other useful link.

    https://help.smartsheet.com/function/if?frame=0&nav=1

     

  • Hi,

    First, you have a typing mistake in the formula.

    after X you have ' instead of ".

     = IF(MONTH([Column2]1) = 3, "X',"XX") 

    As far as i saw for symbol columns, you need the IF results to be the exact phrase of the symbol.

    For example:  if you are using the Red/Yellow/Green circles set, you need the ,formula to be something like:

    = IF(MONTH([Column2]1) = 3, "Green","Red") 

    I am not sure what are the values for all symbols, but for the 3 colors it works.

    It is usually written in the tooltip that appears when you hove on the symbols in the column properties.

    Regarding checkbox, you need to use 1 for "true" (checked) and 0 for "false"

    = IF(MONTH([Column2]1) = 3, 1,0) 

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!