IF(AND(ISBLANK Formula Help
I am utilizing the following formula to return a value based on the values in my columns:
=IF(AND([Work Needed]@row = false, [Scheduled Inspection Date]@row > TODAY()), "Scheduled", IF(AND([Work Needed]@row = true, [Scheduled Inspection Date]@row < TODAY()), "In Progress", IF(AND([Work Needed]@row = false, [Scheduled Inspection Date]@row < TODAY(7)), "Completed", IF(AND([Work Needed]@row = false, [Scheduled Inspection Date]@row < TODAY(7)), "Completed"))))
I would like to add "Not Scheduled" if the Work Needed Column is false and the Scheduled Inspection Date column is blank. I have been at it for hours with no solution. Any help is greatly appreciated in advance!
Answers
-
So first... it looks like the last two IF statements in your formula are identical.... is that an error or are you missing one set of conditions for 'Completed'? Also "= true" is not the right syntax for smartsheet, you would need to use = 1. And it looks like you would never end up with a line getting flagged as 'Completed'...
Also, I typically work from Completed backwards to not started to make sure things land in the spot on the process. But it looks like the only one that has Work Neded checked is for 'In Progress' so you could pull this one to the front and simply your formula a bit
What about the following....
= IF([Work Needed]@row = 1, "In Progress", IF(ISBLANK([Scheduled Inspection Date]@row), "Not Scheduled", IF([Scheduled Inspection Date]@row < TODAY(7), "Completed", IF([Scheduled Inspection Date]@row > TODAY(), "Scheduled"))))
Kelly Drake (she/her/hers)
STARBUCKS COFFEE COMPANY| business optimization product manager
-
@Kelly Drake Worked PERFECTLY! Thank you so very much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!