"Date" cell updates "status" cell

Question

Hello Community! I have been searching thru the posts for a solution without success, time to ask you all wonderful peeps! I have a tracker to manage damaged items, one of the steps is to assign the job to an external designer contractor, the contractor enters an Estimated Design Completion Date and once the design package is completed, an Actual Design Completion Date. Currently, every time the designer enters the Estimated Design Completion Date they have to manually change the status to Request in Progress, and similarly when entering the Actual Design Completion Date status is manually changed to Request Completed. I am looking to automate this and every time a date is entered in the Estimated date the status changes to In Progress, when Actual date is entered status is Completed. Design Status column is a Dropdown (single select), and its being used in Conditional Formatting (in case this is useful info).

This is the formulas l have tried to not avail, only the Request in Progress comes up:

=IF([Estimated Design Completion Date - Helper column]@row = 1, "Request in Progress", IF([Actual Design Completion Date - Helper Column]@row = 1, "Request Completed"))

Thanks!

Jeff Reisman · Accepted Answer

@Silvia Rangel

You can use automation to do this. Create a new automation workflow:

Trigger: When rows are changed, when the Estimated date changes to any value

Action: Change cell value (Select the Design Status column, enter the status you want displayed.

Create another automation rule that does the same thing, but gets triggered when the Actual date changes to any value.

Jeff Reisman · Accepted Answer

@Silvia Rangel

FYI, the reason your formula wasn't working is because the IF formula works from left to right, and once it encounters a true condition, that's where it stops.

If you wanted this formula to work, you'd have to flip it around and add an AND condition:

=IF(AND([Actual Design Completion Date - Helper Column]@row = 1, [Estimated Design Completion Date - Helper column]@row = 1), "Request Completed", IF([Estimated Design Completion Date - Helper Column]@row = 1, "Request In Progress"))

In English - if both helper columns have a 1 in them, set the status to Request Completed, otherwise, if the Estimated helper column has a one in it, set the status to Request in Progress.

You could also do this without helper columns:

=IF(AND(ISDATE([Estimated Design Completion Date]@row), ISDATE([Actual Design Completion Date]@row)), "Request Completed", IF(ISDATE([Estimated Design Completion Date]@row), "Request In Progress"))