# Reference two columns to populate a third

Options

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!

• ✭✭✭✭✭✭
Options

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

• Options

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?

• Options

Hi Paul,

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

Thanks again!

• ✭✭✭✭✭✭
Options

No worries. Happy to help! 👍️

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

• Options

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!

• ✭✭✭✭✭✭
Options

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.

• Options

Perfect! Thank you!

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!