IF FORMULA TO PUT AUTOMATIC STATUS

Mariniel Bautista · 07/31/19

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å · 07/31/19

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 · 07/31/19

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 · 07/31/19

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 · 08/01/19

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

Mike Wilday · 08/01/19

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

Andrée Starå · 08/01/19

I clearly misunderstood! Sorry about that!

IF FORMULA TO PUT AUTOMATIC STATUS

Comments

Help Article Resources

Categories