IF FORMULA TO PUT AUTOMATIC STATUS

Mariniel Bautista

I would like to know what can I do to put automatic status for my monitoring sheet?  There are 3 status:  In Transit, Cancelled and Delivered.  I want to know if its possible that when the requestor input the delivery date (refer to the screenshot), the status will change to "Delivered".  For the Cancelled and Transit though, is it possible that even if the delivered status is formulated, putting In Transit and Cancelled is manual dropdown?

Hope someone can help me.  Thanks.

Andrée Starå

Hi Mariniel,

Yes, it is, but when you change it manually to Cancelled or Delivered, it will remove delete the formula, but it seems like that shouldn't be a problem because it's after "Date of Delivery."

Try something like this.

Add the formula below to the Status column.

=IF(ISDATE([Date of Delivery]@row); "In Transit")

The same version but with the below changes for your and others convenience.

=IF(ISDATE([Date of Delivery]@row), "In Transit")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Mike Wilday

I don't know if I agree with Andree. Your status column as a dropdown has in transit and Delivered. If delivered is the LAST leg of the journey, as it appears it is. Any manual input of the dropdown will override your formula. I would recommend putting in checkbox columns for in transit and canceled, and then lock the dropdown column and use this formula: 

=IF(ISDATE([Date of Delivery]@row), "Delivered", IF(Cancelled@row = 1, "Cancelled", IF([In Transit]@row = 1, "In Transit")))

This will make your status column completely a formula, but also allow for each checking off of items in transit and canceled. 

Mariniel Bautista

Hi Mike.  I will try your suggestion and check if it will work.  Thank you so much.  I will give update once I am done with the checking.

Mariniel Bautista

Hi Mike.  I tested it and it worked.  Thank you so much for your help!

Mike Wilday

Awesome! So glad I could help you out. Have a great day! 

Andrée Starå

I clearly misunderstood! Sorry about that!