Question

I have an automation that looks for today's date under [Next Run Date]. If its today, the automation does its thing. Once the Run is complete, I now need the Last Run Date to be today's date so it pushes the [Next Run Date] into a future date.

The [Next Run Date] is determined by adding the date under [Last Run Date] and [Interval Days]. So it looks like this

See image attached

*[Next Run Date] is set to WORKDAYS. that's why the dates are not exactly 7 or 14 days apart.

How can I get the Last Run Days to update to the date that appears under [Next Run Date]

Does anyone have any creative solutions?

Spoiler alert Chatgpt just gave up trying. LOL!

thanks!!!

Michelle Choate 2 · Accepted Answer

Can you use "Record a Date" to do this? So when the automation you currently have set runs, it checks a box or whatever, and then whenever that box is checked then it records a date in the "Last Run Date" column. Then, 24 hours after last run date, the checkbox gets cleared through a "Clear a Cell" automation or similar?

Michael Drayton · Accepted Answer

Winna, Winna, Chicken Dinna!  That worked. ….So I'll spell it out so maybe someone else can gain.

Start with building two helper columns "Run Completed" which is a Checkbox field and "24 Hours Have Past". In the 24hrs have past column populate with =IF(TODAY() > [Next Run Date]@row, "Yes", "No").

In the Trigger Date column, populate with =IF([Next Run Date]@row = TODAY(), "Run", "")
Trigger: When rows are added or changed ⇒ When Tigger Date changes to Run
Condition: When Trigger Date is equal to Run 
Action: Copy rows
2nd Action: Change cell value in: Run Completed to Checked

Setup this new automation: "Record a Date" 
Trigger: When the "Run Completed" column is equal to checked.

* Action: Use the "Record a Date" Record a date in [Last Run Date].

(this does the actual update in the Record a Date column)

Last Automation:

* Trigger: When rows are added or changed. When  "24 Hours Have Past changes to "Yes".
* Action: Change cell value in Run Completed to Unchecked.

Thanks Michelle (Again)  :-)