Can you check / help with my IF, AND, NOT ISBLANK formula

Mary_SW

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.

1. Pre-launch (dates before the Launch Date)
2. Nominations (Dates after launch but before nomination deadline)
3. Evaluations (Dates after launch, after nominations deadline, but BEFORE Survey Deadline
4. 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"))

Jeff Reisman

@Mary_SW

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"))))

Mary_SW

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