Today Function used in Automated Workflow not Working

Dr. Cory Burger

Hello,

We have set up an automated workflow using the Today() function as the system date. If the today function is between a certain time period, then we want the Term Column to change. When the term column changes, it will change the Term Start Date Column and Term End Date columns which use an INDEX/MATCH formula.

We have tried the automated workflow with different conditions.

In the first workflow: We are using a trigger of "When a date is reached", the Date Field being the "System Date" (which is the Today() function) and two conditions. The first is when the System Date is between 5/1/22 and 8/31/22 and where the System Date is less than 9/1/22, it will change the cell value in the Term column and change the cell values in three other columns to unchecked.

In the second workflow: We are using a trigger of "When a date is reached", the Date Field being the "System Date" (which is the Today() function) and two conditions. The first is where the System Date is greater than or equal to 5/1/22 and where the System Date is less than 9/1/22 it will change the cell value in the Term column and change the cell values in three other columns to unchecked.

In either condition, the automation does not function as we would like it to. 

The only way we can get it to work is if we change the Trigger to "When rows are added or changed". The problem with this is, whenever a client checked a box, it would uncheck the three columns every time.

Any help would be greatly appreciated!

Cheers,

Cory

Genevieve P.

Hi @Dr. Cory Burger

Can you explain what you mean when you say that the automation does not function as you'd like it to?

If you're using the Today function, this function will only update if certain actions happen on a sheet (like if it's opened and saved, so it can recognize what the date is). This article has a list of those actions.

Based on how your workflows are configured, it sounds like you want this to clear the checkboxes every single day, is that correct? Even though it's set up to "run once" when a date is reached, the Today function means that if the sheet is opened each day it will be triggered once a day.

If you actually only need this to run once a Term, I would build that right into the workflow as a Custom condition based on a Date (not on a Date in your sheet, but just on a Date). Click "Run once" to find "Custom":

You should be able to keep your Condition blocks the same to identify what Term should be entered. See: Create a Time-Based Automated Workflow

Let me know if this makes sense and will work for you!

Cheers,

Genevieve