How to use IF formula using 2 different columns?

I have a Status column and I want the Status column to be updated based on the updates on 2 different columns.

  1. If Resolution Date column has any value, mark status column as “Complete”
  2. If Due Date column date is today’s date or less and there’s nothing in the Resolution Date column, mark status column as “Delayed”
  3. If Due Date column today’s date or more and there’s nothing in the Resolution Date column, mark status as “On Track”

My formula is not working and coming up #UNPARSABLE

=IF([Resolution Date]@row = “”, "Complete", IF(DueDate@row < TODAY(), "Delayed", IF([DueDate]@row > TODAY(), "On Track", "”)))

Also, I tried using to workflow and it just didn't update properly as I needed it to. It was glitchy.

What am I doing incorrectly?

Tags:

Answers

  • KPH
    KPH ✭✭✭✭✭✭

    Hi @Vivre Belle

    Your formula is almost* great. But you have a mix of normal and "smart" quotes which is causing the formula to be #UNPARSABLE. The first two and the last one are the problem ones. See how they look a little different?

    Smartsheet only recognizes the straight quotes. If you are using a mac you can switch off the smart quotes. If you are copying and pasting from somewhere else, paste into a simple text editor first.

    * The logic on the first IF is doing the inverse of what you want. It is marking as Complete if the Resolution Date IS empty.

    Try this:

    =IF([Resolution Date]@row <> "", "Complete", IF(DueDate@row < TODAY(), "Delayed", IF(DueDate@row > TODAY(), "On Track", "")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!