IF/AND Formula help please

I am trying to create a formula that says if all 4 cells are blank it returns the text "NOT YET ALLOCATED". If the allocated start date has been completed, but the other 3 cells are blank, the text returns "MISSED", otherwise the cell should remain blank.

I have entered the following formula, but am getting the error #UNPARSEABLE.

=IF(AND(ISBLANK([Allocated Start Date]@row), ISBLANK([Completed By]@row), ISBLANK(Status@row), ISBLANK([Completed Date]@row), ISBLANK([Allocated Start Date]@row) "NOT YET ALLOCATED"), IF(AND(ISBLANK([Completed By]@row), ISBLANK(Status@row), ISBLANK([Completed Date]@row), TODAY() > [Allocated start Date plus 3 days]@row, "MISSED")), " ")

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭

    @Nikki_85

    You are close. You need to close your AND Statement, to get to your TRUE argument. Try the edit below...

    =IF(AND(ISBLANK([Allocated Start Date]@row), ISBLANK([Completed By]@row), ISBLANK(Status@row), ISBLANK([Completed Date]@row), ISBLANK([Allocated Start Date]@row)),"NOT YET ALLOCATED", IF(AND(ISBLANK([Completed By]@row), ISBLANK(Status@row), ISBLANK([Completed Date]@row), TODAY() > [Allocated start Date plus 3 days]@row), "MISSED", " "))

  • Amazing!! thank you so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!