IF FORMULA TO PUT AUTOMATIC STATUS

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.

Find more posts tagged with

Formulas

Real Estate

IT & Operations

Project Management

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?