Change a Cell Automation on Dates
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.
Best Answers
-
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.
-
@Lauryn Vogt No problem! You would just create an If statement at the beginning of the formula that states that if that column input is not a date, to leave the cell blank. The formula would thus be changed to this:
=IF(NOT(ISDATE([Due to Copyedit]@row)), "", IF(AND([Due to Copyedit]@row<=TODAY(30), Complete@row<>1), "1. High", IF(AND([Due to Copyedit]@row<=TODAY(60), Complete@row<>1), "2. Medium", "3. Low")))
Alternatively, you can have the formula return a blank (or any other chosen return) when any error occurs by using an iferror statement such as the following:
=IFERROR(IF(NOT(ISDATE([Due to Copyedit]@row)), "", IF(AND([Due to Copyedit]@row<=TODAY(30), Complete@row<>1), "1. High", IF(AND([Due to Copyedit]@row<=TODAY(60), Complete@row<>1), "2. Medium", "3. Low"))), "")
Hope this helps!:)
Answers
-
Here's the formula I would use:
=IF(AND([Due to Copyedit]@row<=TODAY(30), Complete@row<>1), "1. High", IF(AND([Due to Copyedit]@row<=TODAY(60), Complete@row<>1), "2. Medium", "3. Low"))
You can either make this a column formula in your "Priority" column or create a helper column with this column formula. The helper column would allow you to still manually update priorities in your priority column. With the helper column option, you would also want to set up a workflow that triggers when the helper column changes, overwriting the "Priority" column with the helper column value when triggered.
Hope this helps!:)
-
@brianschmidt thank you, the formula makes sense, I don't know that I understand how to set up the helper column. I understand to make a separate column using the formula you gave me. But how do I set up the workflow that triggers when the helper column changes, overwriting the "Priority" column with the helper column value when triggered?
Thanks!
-
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.
-
@brianschmidt thank you so much for taking the time to help me with this. I really appreciate it! This will make project tracking much faster for our team!
Lauryn
-
Awesome! Glad it works for you:)
-
@brianschmidt sorry to bother you again, I am using the formula you gave me and it works great, but how would I get the priority column to remain blank if the Due to Copyedit doesn't have a date? Screenshot included.
-
@Lauryn Vogt No problem! You would just create an If statement at the beginning of the formula that states that if that column input is not a date, to leave the cell blank. The formula would thus be changed to this:
=IF(NOT(ISDATE([Due to Copyedit]@row)), "", IF(AND([Due to Copyedit]@row<=TODAY(30), Complete@row<>1), "1. High", IF(AND([Due to Copyedit]@row<=TODAY(60), Complete@row<>1), "2. Medium", "3. Low")))
Alternatively, you can have the formula return a blank (or any other chosen return) when any error occurs by using an iferror statement such as the following:
=IFERROR(IF(NOT(ISDATE([Due to Copyedit]@row)), "", IF(AND([Due to Copyedit]@row<=TODAY(30), Complete@row<>1), "1. High", IF(AND([Due to Copyedit]@row<=TODAY(60), Complete@row<>1), "2. Medium", "3. Low"))), "")
Hope this helps!:)
-
@brianschmidt once again, thank you so much!! That worked beautifully.
