copy rows automation with date formula

Tim Machula

I have two sheets - one with routine tasks, the other with a list of assigned tasks. I have an automation built into the first sheet to copy rows to the second sheet on a regular basis. The first sheet has a date column which uses the TODAY() function.

I'm having a problem with items appearing on the second sheet with the wrong date. I know that the formula is lost in transit, but does Smartsheet evaluate the formula at the time the automation is run? Does the sheet have to be accessed for the cell contents to be updated prior to the automation?

Anyone with similar issues?

Paul Newcome

It may be that the automation is grabbing the row before the automation "activates" the sheet to update the TODAY function.

Do you have access to the Record A Date automation? If so, you can use that to populate a helper column prior to the copy row automation running.

Set the Record A Date automation to run daily at 1:00am and the condition would be when the helper column is in the past. Have the automation populate the helper column.

Then set your daily recurrence of the copy row automation to run at say 2:00am.

The record a date will update the TODAY function prior to the copy row automation running and your dates should be accurate.

Paul Newcome

If you are trying to capture the date the row is copied over, you could use a Created (date) type column in the second sheet.

Tim Machula

I'm actually trying to auto-schedule these routine tasks a few days out, so the actual formula in the first sheet is TODAY() + (whatever number of days in advance). There are other tasks entered into the second sheet through other means, and they all share the same Scheduled Date column. I assumed that an automation set to run daily on the first sheet would transfer the tasks and coerce the TODAY()+ X into whatever Date that happens to be when the automation is triggered.

Is there a better way to achieve this? The big goal here is to reduce the need for manually scheduling tracked tasks that occur every day/week/month.

Paul Newcome

It may be that the automation is grabbing the row before the automation "activates" the sheet to update the TODAY function.

Do you have access to the Record A Date automation? If so, you can use that to populate a helper column prior to the copy row automation running.

Set the Record A Date automation to run daily at 1:00am and the condition would be when the helper column is in the past. Have the automation populate the helper column.

Then set your daily recurrence of the copy row automation to run at say 2:00am.

The record a date will update the TODAY function prior to the copy row automation running and your dates should be accurate.

Tim Machula

What a fantastic idea. I will give it a shot.

Tim Machula

Hey Paul,

Your suggestion worked like a dream. Thank you for this clever solution.

Paul Newcome

Happy to help. 👍️