Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

IF THIS AND THIS then TRUE CHECKBOX

James Maxwell
edited 12/09/19 in Archived 2016 Posts

Since Smartsheets currently doesn't have timestamping system I am using manual dates to do so however I need a checkbox to be ticked to alert me when two conditions have been met. These conditions are dates and are blank before data goes into them. 

 

What I thought would work is =IF(AND([Date1]1 > 0, [Date2]1 > 0), TRUE )

 

However it does not seem to like me. I'm sure I'm simply missing something but if someone could help that'd be great. 

Tags:

Comments

  • Greg Gates
    Greg Gates ✭✭✭✭✭
    edited 09/19/16

    I think you can actually use smartsheet's NOT and ISBLANK functions to get your desired effect here. Your formula might look something like this:

    =IF(AND(NOT(ISBLANK([Date 1]1)), NOT(ISBLANK([Date 2]1))), "True", "False")

     

    I hope that helps solve your problem! Let me know if you have any other questions or if my solution didn't work.

  • Hi Everyone—Greg's formula is the best way to accomplish this, with one slight modification since you're wanting a checkbox to be checked:

     

    =IF(AND(NOT(ISBLANK([Date 1]1)), NOT(ISBLANK([Date 2]1))), 1)

     

    Checkboxes and other booleans in Smartsheet are associated with numeric values of 1 for checked (or "true") and 0 for unchecked (or "false"). 

     

    You don't technically need to add the else (or "false") clause at the end of the formula, as it will return nothing and leave the box unchecked until your conditions are met.

  • Thanks Greg, as Shaine noted it wasn't true false but 1,0 that go the job done. Otherwise your formula was perfect .

     

    Thank you very much!

  • John Sauber
    John Sauber ✭✭✭✭✭✭

    You can even go one step further.

     

    =AND(NOT(ISBLANK([Date 1]1)), NOT(ISBLANK([Date 2]1)))

     

    is functionally equivalent, but is not wrapped in an IF statement. This typically won't matter, but if your sheet has many complicated formulas, any savings in complexity can help your sheet operate and calculate quicker. This works for any Boolean field.

  • Shaine Greenwood
    Shaine Greenwood Employee
    edited 09/20/16

    Hi John,

     

    You make an important note on reducing the complexity of a formula when possible to aid the time it takes for Smartsheet to calculate results.

     

    In this case, due to James's need, I'm not seeing a way to reduce the weight of this formula any further. Also, because of the way functions work in Smartsheet, I believe your formula would return an error. 

     

    =AND(NOT(ISBLANK([Date 1]1)), NOT(ISBLANK([Date 2]1)))

     

    AND(), IF(), and ISBLANK() functions can only be used within other functions, such as IF() statements. There also isn't a "then" clause to perform an action when the statement evaluates to be true.

  • John Sauber
    John Sauber ✭✭✭✭✭✭

    You can slam any boolean result into a SS boolean field (like a star, checkbox or flag) directly. If the result is a 0 or 1 (like the AND() and OR() functions return), then those can directly drive the boolean field without wrapping them in an IF() statement. See the [SAT/SUN] column for the OR example and the [Check] column for the AND() example.

     

    Also, as I understand it, there is actually further translation that needs to be done in the formula engine to convert a 1 to true and a 0 to false, so this eliminates that translation as well as the IF() logic.

     

    Enjoy!

     

    https://app.smartsheet.com/b/publish?EQBCT=1262fc277a7d4d86bdc51ab1ac21dc3e

This discussion has been closed.