automation to update a date field to new value

Steven De la Marche

Hi,

I am trying to set up an automation where it detects when a date found in column DATE is passed

and then would automatically update it based on the value found in the column frequency

for example :

if the DATE = 01/07/2021

and the frequency = Weekly

then when we pass the date 01/07/2021 it should automatically change the DATE field to 08/07/2021

I am able to calculate the new date value using a formula

But for some reason I can not figure out how to automate the DATE field being updated to this new value.

Can some of you advise on how this can be done ?

Bassam Khalil

Hi @Steven De la Marche 

Hope you are fine, you can do that as following

1- create a helper column "Check Box" to calculate when you pass the date use call it "Date Pass" the following formula

Date Pass =IF(DATE@row - TODAY() = 0, 1, "")

2- Create a column called "New Date" Date type to calculate the new date and use the following formula

New Date =IF([Remaining To Pass]@row = 1, DATE@row + 7, DATE@row)

and you can use the new date column to show up-to-date Dates as per your request.

the following screenshot shows the result:

Steven De la Marche

Hi @Bassam Khalil ,

Thank you very much for your attempt at answering my question .

Sadly I fear i have not been clear about the actual problem.

1) I succeeded in calculating the next date ==> OK

2) I managed to make an automation that detects that the DATE has passed. ==> OK

3) but when I when I want this automation to replace the DATE with the newly calculated date

I do not know how. ==> PROBLEM

The reason I want this original date to be replace is that it is used for the calendar view.

So for example when i have a recurring action in my project, I want the date to be moved every time so this action appears on the new date in the calendar view each time.

Example :

we have this repeated task that will happen on the date mentioned in DATE column

So when this date is passed the FUTURE date should be copied over the DATE FIELD and a new FUTURE date can be calculated

I tried to do this via automation but here is where i get stuck

Garrett Henke

Steven,

This is a possible work around i can think of but I'm not 100% sure that it will work as it is basically a continuous loop.

Set up a checkbox column to the right of the Extra Days column.

• Formula for the checkbox column =IF([Extra Days]@row=0,1,0)
  • (This will check the box when extra days is equal to zero)
• Next set up a Record a Date workflow automation
  • When checkbox column is checked Record a Date in Date column
  • Set this to reoccur every day at 7pm (or any time of your choosing)

Give this a shot because I have a feeling that it will work for what you are trying to do. Best of Luck!