Using AND and ISBLANK in nested IF statement

Matthew Anthon

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.

Atus Bartal

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

 

Shaine Greenwood

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!

Matthew Anthon

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.