IF THIS AND THIS then TRUE CHECKBOX

James Maxwell

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. 

Greg Gates

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.

Shaine Greenwood

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.

James Maxwell

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

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

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

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