Automate Date Fields

SabrinaH · 07/02/25

I have reoccurring tasks that have start dates and end dates. What I would like to do is after the status changes to completed, then automatically change the date fields to the next occurrence. For example: I have a task that has a start date of 7/1/25 and an end date of 7/10/25, and that task reoccurs quarterly. Is there a way to automate it so that once the status changes, the dates change to 10/1/25 and 10/10/25. Any ideas would be helpful!

Paul Newcome · 07/03/25

What are your different frequencies?

SabrinaH · 07/07/25

Reoccurrence frequencies range from monthly, quarterly, and annually.

Corey W. · 07/08/25

@SabrinaH you could do this using automation. use the status column changing to completed as the trigger. Then have the action set to record a date in a field called status date or something similar. Then use this field to calculate your next start date, =[status date]@row+90(or whatever frequency). Then your end date set to [start date]@row+10(or whatever duration).

SabrinaH · 07/08/25

@Corey W. I do like that idea, and I could see it working in some scenarios. For this specific case I would need the automation to change the Start Date and End Date fields because those two fields are linked to automation/conditional formatting. Following your solution, it would get me the new dates, but the dates would not be connected to the overall date tracking for the project list.

For example let's say we have ten projects being tracked, three of them are reoccurring every quarter every year, when the next quarter starts how would we accurately track the new start and end date without consistently adding new columns.

Corey W. · 07/09/25

@SabrinaH in my scenario the dates would be updated each time the status changed so you wouldn't need other columns. I used it for reoccurring inspections. It is more of a rolling setup since they next date is determined by the previous completion date so it might not work for your use case. I also left out the resetting of the status column that would also need to occur in some automated or manual way.

Another approach would be to calculate the start date using functions based on Today(), but again that would result in a date derived by a function. I don't know of a good way off the top of my head to use automation to directly edit a date (other than marking the current date) but there may be workarounds to accomplish something like this. Good luck!

jmyzk_cloudsmart_jp · 07/10/25

Hi @SabrinaH

Here’s the setup that worked well for me, using 3 linked sheets and automation workflows:

Your Need:

Automatically update the Start and End Dates to the next recurring period once Status changes to "Complete" – without disrupting downstream automation or conditional formatting.

Summary of My Method:

I use a three-sheet structure with automations and formulas to handle recurring tasks automatically:

Sheet 1 (Master Task Tracker) calculates the next Start Date (monthly, quarterly, or annually) based on the completed date.
Sheet 2 (Task Generator) pulls the latest start date by task name, determines if the row should be copied, and flags it using a [Copy] checkbox.
Sheet 3 (Archive) stores older task instances. In Sheet 1, RANKEQ ranks duplicate task names; if a row is ranked "2", it gets moved here.

This approach keeps only the latest instance in your active sheet and lets you reuse the original Start/Finish columns without creating new ones.

My Solution (3-Sheet Setup):

Sheet 1 – Master Task Tracker

https://app.smartsheet.com/b/publish?EQBCT=bcf6b612d7804d218446dfafce5fbb1c

This is your central task sheet.
Contains fields for Start, Finish, Status, Cycle, and Complete Date.
- [Row}=MATCH([Row ID]@row , [Row ID]:[Row ID], 0
- [Next Month Start] = IF(AND(ISDATE([Complete Date]@row ), Cycle@row = "monthly"), DATE(YEAR([Complete Date]@row ) + IF(MONTH([Complete Date]@row ) = 12, 1, 0), MOD(MONTH([Complete Date]@row ) + 1, 12), 1))
- [Next Quarter Start] = IF(AND(ISDATE([Complete Date]@row ), Cycle@row = "quarterly"), DATE(YEAR([Complete Date]@row ) + IF(MONTH([Complete Date]@row ) >= 10, 1, 0), MOD(CEILING(MONTH([Complete Date]@row ), 3) + 1, 12), 1))
- [Next Year Start] = IF(AND(ISDATE([Complete Date]@row ), Cycle@row = "annually"), DATE(YEAR([Complete Date]@row ) + 1, MOD(MONTH([Complete Date]@row ), 12), 1))
- [Rankeq] = RANKEQ([Row ID]@row , COLLECT([Row ID]:[Row ID], Task:Task, Task@row ), 0)
Automation:
- When the Status changes to complete, record the [Completion Date]
- When Rankeq = 2 → Move the row to the Archive sheet (Image 1)

With the new schedule rows copied to, the automation moved old or RANKEQ=2 rows to the 3rd sheet.

Sheet 2 – Task Generator

https://app.smartsheet.com/b/publish?EQBCT=44201e53995143cf988adfee33890f47

[Start] = IFERROR( IF(Cycle@row = "monthly", MAX(COLLECT({Sheet1: Next Month Start}, {Sheet1: Task Name}, [Task Name]@row )), IF(Cycle@row = "quarterly", MAX(COLLECT({Sheet1: Next Quarter Start}, {Sheet1: Task Name}, [Task Name]@row )), IF(Cycle@row = "annually", MAX(COLLECT({Sheet1: Next Year Start}, {Sheet1: Task Name}, [Task Name]@row )), ""))), "")[Finish] = IF(ISDATE(Start@row ), WORKDAY(Start@row , 10))
[Copy] = ISDATE([Start]@row )
⚠ Note: Smartsheet does not trigger Copy Row actions from changes in cross-sheet formulas or cell links.
To work around this, I use a time-based trigger (like daily at 11:00 AM) to process rows that meet the criteria.
Reference: Smartsheet Help Article – Automatically Copy Rows

Formulas

[Task Name] =IFERROR(INDEX(DISTINCT({MQA Task Scheduler : Task Name}), MATCH([#]@row , {MQA Task Scheduler : Row}, 0)), "")
[Assigned To] =IFERROR(INDEX({MQA Task Scheduler : Assigned To}, MATCH([Task Name]@row , {MQA Task Scheduler : Task Name}, 0)), "")
[Start] =IFERROR(IF(Cycle@row = "monthly", MAX(COLLECT({MQA Task Scheduler: Next Month Start}, {MQA Task Scheduler : Task Name}, [Task Name]@row )), IF(Cycle@row = "quarterly", MAX(COLLECT({MQA Task Scheduler: Next Quarter Start}, {MQA Task Scheduler : Task Name}, [Task Name]@row )), IF(Cycle@row = "annually", MAX(COLLECT({MQA Task Scheduler: Next Year Start}, {MQA Task Scheduler : Task Name}, [Task Name]@row )), ""))), "")
[Finish] =IF(ISDATE(Start@row ), WORKDAY(Start@row , 10))
[Cycle] =IFERROR(INDEX({MQA Task Scheduler : Cycle}, MATCH([Task Name]@row , {MQA Task Scheduler : Task Name}, 0)), "")
[Copy] =ISDATE(Start@row )

For testing purposes, I have two automations.

The two automations have worked as expected.

📁 Sheet 3 – Archive

Older task instances are archived when new ones are created.

https://app.smartsheet.com/b/publish?EQBCT=b574c21adc084fc3928d57e4ae091e97

Benefits of This Design

No need to add columns for each recurrence
Works with existing automations/formatting
Tracks history via archive
Fully dynamic and flexible — supports Monthly, Quarterly, Annually

Let me know if you'd like a shareable version of the sheets or a CSV sample.

“Automate the boring. Focus on what matters.”

Paul Newcome · 07/10/25

@SabrinaH Are you wanting it based on when something is actually marked complete, or are you just going by the [End Date] being in the past to reset the dates to the next set frequency?