ISBLANK combined with AND statements
Hi, I'm having trouble nesting AND & OR statements with the ISBLANK condition in my IF statement.
I have two columns: [Agreed End Date] and [Actual End Date]
=IF(AND(NOT(ISBLANK([Actual End Date])), (OR(TODAY() - [Agreed End Date]@row < 1, "On Track", IF(TODAY() - [Agreed End Date]@row < 7, "Delay", IF(TODAY() - [Agreed End Date]@row > 7, "Overdue", "Error")))
I tried to split it into multiple IF statements rather than having the OR in place but it did not work neither. For example:
=IF(AND(TODAY() - [Agreed End Date]@row > 7, ISBLANK([Actual End Date]@row), "overdue", ""))
The above returns an "invalid argument set"
Answers
-
Hi @Blue Pencil
When nesting IF statements, I find it a little easier to look for Blanks or Not Blank by using = "" for equals blank, or <> "" for not blank. This eliminates the need for parentheses which can sometimes cause an error if they're in the wrong place.
For example:
=IF(OR(TODAY() - [Agreed End Date]@row > 7, [Actual End Date]@row = ""), "Overdue", "")
You'll notice I closed off the OR statement before telling it what to do:
=IF(OR(statement ), "Overdue", "")
Then for your other formula, I'm not quite sure what you want it to do. Could you write out what you want it to do using words?
Let me know if this is close:
=IF(OR(TODAY() - [Agreed End Date]@row > 7, [Actual End Date]@row = ""), "Overdue", IF(TODAY() - [Agreed End Date]@row < 7, "Delay", IF(TODAY() - [Agreed End Date]@row < 1, "On Track", "Error")))
Cheers,
Genevieve
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!