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

# Using AND and ISBLANK in nested IF statement

Options
edited 12/09/19
I am responsible for tracking my companies incoming inquires and quotes. Did we get back to everyone, quote them, did they accept or decline the quote?, etc.

As part of that I sweated out the following formula,

=IF(ISBLANK([Follow Up Date]98), IF((TODAY() - Created98) - 2 > 0, IF(OR([Quote Accepted]98, [Quote Rejected]98, [Investigated, Not pursued]98) = 0, 1, 0), 0), IF((TODAY() - [Follow Up Date]98) - 2 > 0, IF(OR([Quote Accepted]98, [Quote Rejected]98, [Investigated, Not pursued]98) = 0, 1, 0), 0))

This checks the Action Overdue Checkbox col under the following circumstances. There is nothing in the Follow Up date COl, or if there is, if we are now 2 days past that date and the fields Quote Accepted, Quote Rejected, or Investigated, Not Pursued are not checked.

This formula works just fine and dandy. However, for the life of me I cannot figure out how to change the fields Quote Accepted, Quote Rejected, or Investigated, Not Pursued from checkboxes to Date cols and just have the formula check the Overdue box if all 3 are empty.

Can anyone help me with this? My attempts at using ISBLANK as part of the OR operator in the nested if just get me an incorrect data error when i convert the Checkbox cols to Date cols.

• ✭✭✭✭✭✭
edited 09/26/16
Options

Matthew,

ISDATE() formula works in the case of Date type columns.

The following formula gives you 1 if any of the three cells contain a date:

=IF(OR(ISDATE([Date1]2), ISDATE([Date2]2), ISDATE([Date3]2)), 1, 0)       [for row#2]

I hope you can go on with this.

Atus

• Employee
Options

Hello Matthew,

Alternatively, if you're wanting to return a result (text string or value of 1 to check a box) you can use a formula like the following:

=IF(AND(ISBLANK(Date1), ISBLANK(Date2), ISBLANK(Date3)), 1)

If the above formula is placed in a checkbox column, the box will be checked ONLY if Cells 1, 2, and 3 of a column named "Date" are all blank. The AND function indicates that all conditions must be met in the IF statement, whereas the OR function indicates that at least one condition must be met.

Hope this helps!

• Options

Thankyou Atus, ISDATE should have jumped out at me from the IF modifiers documentation page, but I guess I blanked it out.

Thanks for giving me the correct modifier syntax for AND and OR in IF statements guys.

Hopefully I can rejig properly now.

This discussion has been closed.