Change a Cell Automation on Dates

Lauryn Vogt

I am trying to run an automation that will change the priority cell based on the date in a date column.

See attached example.

I want the Priority Column to automatically change to 1. High if the Due to Copyedit Date is in the next 30 days (or past) and the Complete box is unchecked.

I want it to change to 2. Medium if it is in the next 60 days and box is unchecked.

And, I want it to change to 3. Low if it is more than 60 days away.

I haven't been successful with setting up these automations to all work simultaneously. Any ideas are appreciated.

Screenshot 2024-01-30 155556.png

brianschmidt

For sure! Let me see if I can break it down...let me know if anything doesn't make sense.

First of all, this assumes that your Priority column is formatted as a dropdown column with the priority levels set as options.

When you create the additional column ("helper" column), you'll enter the formula we wrote above. Then, you'll want to make sure you right-click the cell you put that formula in and select "Convert to Column Formula".

Then, in the top right hand side of the page (between File and Forms), you'll click "Automation" and then "Create Workflow from Scratch". You'll title the workflow whatever you want. Then, in the trigger section change where it defaults to "When Any field changes" to "When Helper Column changes" (or whatever you've titled your helper column). Select "1. High". Make sure it says "Run workflow: When triggered". Then, in the select an action section, select "Change cell value". Set it to change the value in the Priority column to "1. High".

You will then save the workflow and repeat the process to create workflows that change the values when the helper column changes to 2. Medium and 3. Low, respectively.

Again, this is just if you are wanting to also manually update the Priority column along with using the date formula. Otherwise, you can just make the formula a column formula in your Priority column.