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

Options

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

Tags:

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

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

    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!

  • Mary_SW
    Mary_SW ✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!