Using IF formula to drive status

Denise Dahart

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

Paul Newcome

Give this a whirl...

 

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

Denise Dahart

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!!!!

 

Paul Newcome

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.

Denise Dahart

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!

Denise Dahart

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!

 

Andrée Starå

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

Paul Newcome

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

Denise Dahart

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

 

Paul Newcome

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

.

Adjusted:

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

Denise Dahart

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

Paul Newcome

Happy to help!