Reference two columns to populate a third

Stephanie Stewart

Hi,

I'm fairly new to Smartsheet and am stuck trying to figure out a formula. I have three columns; 'Due Date', 'Complete' and 'Status'. Due is a XX/XX/XX format, Complete is a drop down with 'Yes or 'No' and Status is blank. Here's what I need:

If 'Due Date' is past the current date (today) and Complete = No, then Status needs to populate with 'PAST DUE'

If 'Due Date' equals tomorrow and Complete = No, then Status needs to populate with 'Due Tomorrow'

If 'Due Date' equals 2 or more days from today and Complete = No, then Status needs to populate with 'In Progress'

If 'Due Date' equals any date and Complete = Yes, the Status needs to populate with 'COMPLETED'

Any help is greatly appreciated! I've been struggling with this formula for a couple of days now. Maybe I need more than one formula? I'm not sure.

Thanks!

Paul Newcome

Just a missing comma immediately before "Due Tomorrow".

I also suggest using "@row" instead of row numbers if you are referencing cells on the same row as the formula.

Paul Newcome

Try something like this...

=IF(Complete@row = "Yes", "COMPLETED", IF(TODAY() >= [Due Date]@row, "PAST DUE", IF(TODAY(1) = [Due Date]@row, "Due Tomorrow", "In Progress")))

Stephanie Stewart

Hi Paul,

Thanks for posting an option. I was way off, I thought I was need and 'IF(And' formula. I'm still getting #UNPARSEABLE, I did:

=IF(Complete1="Yes", "COMPLETED", IF(TODAY()>=[Due Date]1,"PAST DUE", IF(TODAY(1)=[Due Date]1"Due Tomorrow","In Progress")))

Any thoughts on what is causing the error?

Paul Newcome

Just a missing comma immediately before "Due Tomorrow".

I also suggest using "@row" instead of row numbers if you are referencing cells on the same row as the formula.

Stephanie Stewart

Hi Paul,

I can't believe I missed that comma. Thanks so much! I used your other suggestions as well.

Thanks again!

Paul Newcome

No worries. Happy to help! 👍️

Please don't forget to mark an "Accepted Answer" so that others know a solution has been found.

Stephanie Stewart

Hi again!

I just found that in addition to the options above that you helped me with, Complete may also have 'Cancelled'. If I want Status to show 'CANCELLED' for those cells, can I just add =IF(Complete@row = "Cancelled", "CANCELED" to my formula?

Thank you!

Paul Newcome

Yes. It would look something like this:

=IF(Complete@row = "Yes", "COMPLETED", IF(Complete@row = "Cancelled", "CANCELLED", IF(TODAY() >= [Due Date]@row, "PAST DUE", IF(TODAY(1) = [Due Date]@row, "Due Tomorrow", "In Progress"))))

Notes:

I placed it near the beginning of the formula so that it would be run immediately after the formula finds that it is not "Yes". This means we still do not have to specify that Complete@row = "No" for the rest of them. If you have three option in that column and the first two have already been checked as false, then by default it MUST be the third option which means we can save the hassle of having to specify it.

There is also one more closing parenthesis at the end of the formula since we have added one more IF statement.

Stephanie Stewart

Perfect! Thank you!

Paul Newcome

Happy to help! 👍️