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
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.
Comments
-
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
-
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!
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives