Can you check / help with my IF, AND, NOT ISBLANK formula
Hi all,
This is my first post :-)
Hoping for some help with a long IF formula.
I have sheet that has columns with dates in them
Launch Date, then Nomination Deadline, then Survey Deadline - in that timeline, so there won't be a survey report deadline after a launch deadline. Sometimes these dates are not filled in.
I am trying to have a column which returns a project status based on these dates.
I have 4x statuses.
- Pre-launch (dates before the Launch Date)
- Nominations (Dates after launch but before nomination deadline)
- Evaluations (Dates after launch, after nominations deadline, but BEFORE Survey Deadline
- Report & Debrief (Dates after the survey deadline)
I am working off the understand that IF statements apply to the first, and then the next in the string, rather than all in the string at once, and so have worked backwards.
But It's not working and my brain is fried! Can someone help.
To complicate matters, there are some fields that are blank, so I am having to use "NOT ISBLANK" and I think this is used incorrectly
IF(AND([Survey Deadline]@row > Today@row, (NOT(ISBLANK[Survey Deadline]@row]), “Debrief & Report”, IF(AND([Nomination Deadline]@row > Today@row, [Survey Deadline]@row < Today@row), "evaluations"), IF(AND([Launch Date]@row < Today@row, [Nomination Deadline]@row > Today@row), "nominations"), IF(AND([Launch Date]@row > Today@row, (NOT(ISBLANK[Launch Date]@row), "Pre-Launch"))
Answers
-
Since these are Date columns, instead of NOT(ISBLANK(DateField@row)) you can use ISDATE(DateField@row) to check if there is a date value in the field.
Try this:
=IF(AND(ISDATE([Launch Date]@row), TODAY() < [Launch Date]@row), "Pre-Launch", IF(AND(ISDATE([Nomination Deadline]@row), ISDATE([Launch Date]@row), TODAY() >= [Launch Date]@row, TODAY() <= [Nomination Deadline]@row), "Nominations", IF(AND(ISDATE([Nomination Deadline]@row), ISDATE([Launch Date]@row), ISDATE([Survey Date]@row), TODAY() > [Nomination Deadline]@row, TODAY() <= [Survey Deadline]@row), "Evaluations", IF(AND(ISDATE([Nomination Deadline]@row), ISDATE([Launch Date]@row), ISDATE([Survey Date]@row), TODAY() > [Survey Deadline]@row), "Debrief & Report"))))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks Jeff, sadly it is still giving the error that the syntax isn't quite right. I'm gonna try and spot the error, but it's not giving me many more clues than just the error message
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!