# Using IF formula to drive status

Options
edited 12/09/19

Mine is a little different than what I've found here.  I have 4 columns:  Closed Date and Completion Date, Status column that has 4 choices:  open, closed, complete, overdue and a Due date column.

If closed and completion are blank, the status should be open

If Due date has passed, status should be overdue.

If completion is populated, status should be complete

If Closed is populated, status should be complete

I have a form attached to this, so when someone completes the form, the sheet populates with that information.  When I add these IF formulas, will they be applied to these new rows that are automatically added with the form?

Thank you!

Denise

Tags:

• ✭✭✭✭✭✭
Options

Give this a whirl...

=IF(OR(ISDATE([Closed Date]@row), ISDATE([Completion Date]@row)), "complete", IF([Due Date]@row < TODAY(), "overdue", "open"))

• Options

it worked, however, it marked it complete when it was closed instead of complete. Also, how do I get this formula to apply to rows that are added automatically through a form?

Thank you so much!!!!

• ✭✭✭✭✭✭
Options

Sorry about that. I was going by the list in the original post. Try this one instead...

=IF(ISDATE([Closed Date]@row), "closed", IF(ISDATE([Completion Date]@row), "complete", IF([Due Date]@row < TODAY(), "overdue", "open")))

.

As long as the formula is in two consecutive rows, it will auto-populate as new rows are added.

• Options

This worked perfectly!  THANK YOU.  I guess this is where algebra would have paid off?  I have one more I can't figure out.

=IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", IF([Due Date]1 > TODAY(), "Green")))

This formula dictates the RGYB and works.  I'd like the ball to turn blue if the [closed date] or the [completion date] cell is filled.  I'm not doing something right!

Can you help?  Thank you!

• Options

If a row is added automatically through a form, what would the formula be for a dropdown cell [category] to fill in "form". I want to indicate the source of the incoming question as we use an email alias as well.  Thanks!  again!

• ✭✭✭✭✭✭
Options

Hi Denise,

If I understand your question correctly, I'd recommend adding the drop-down value "Form" as a default value in the form and hide it.

Would that work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

For this, you would just add the IF statement to the BEGINNING of the nested formula.

=IF(OR(ISDATE([closed date]@row), ISDATE([completion date]@row)), "Blue", IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", IF([Due Date]1 > TODAY(), "Green"))))

• Options

Thank you.  This worked beautifully.  I have one more!  Sorry, I think I used that "one more" last time, however, in the above thread, I was given a formula to manipulate the open, closed, completed, etc.  the formula works great.  I would like to have an override in there.  So that IF the [completion date] cell has a value, it will override the status and make it "complete" vs "closed."  Closed is a bit of a first step.  it means the answer has been provided to our submitter.  However, IF a question is added to our document, that would then make it "complete."  Here is the formula I have now - not sure what/where to add!  =IF(ISDATE([Closed Date]@row), "closed", IF(ISDATE([Completion Date]@row), "complete", IF([Due Date]@row < TODAY(), "overdue", "open")))

• ✭✭✭✭✭✭
Options

It is simply a matter of rearranging the order of the IF statements...

Current:

=IF(ISDATE([Closed Date]@row), "closed", IF(ISDATE([Completion Date]@row), "complete", IF([Due Date]@row < TODAY(), "overdue", "open")))

.

=IF(ISDATE([Completion Date]@row), "complete", IF(ISDATE([Closed Date]@row), "closed", IF([Due Date]@row < TODAY(), "overdue", "open")))

• Options

Thank you again.  Have to say this is not one of, but THE best support community I've ever engaged with. Thank you for the swift, accurate and patient answers from someone that is a "letters" person vs. a "numbers" person.  These formulas make me crazy.  Thank you for making it easy.

Denise

• ✭✭✭✭✭✭
Options

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!