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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!