Copy Row automation brings original sheet 'Created date' instead of showing date it was moved

Krystal Garcia

Please help, I have a helper sheet that I am using to capture time stamps of when a row goes to different statuses (ex: capture time stamp when request status changes from: In Review, Pending Response, and Completed).

To do that I set up a copy row automation that triggers when those statuses change. Then it copies the row to my new helper sheet.

But when it copies over to the helper sheet, it is just bringing the Created Date from that original sheet. It doesn't actually show the created date on the helper sheet. And the modified date column just changes every single row when something new populates on that sheet. How do I get the time stamp at each status change?

Screenshot of my copy row automation:

Screenshot of the columns on my helper sheet that the rows are being copied to:

SteyJ

https://community.smartsheet.com/discussion/114131/copy-row-automation-brings-original-sheet-created-date-instead-of-showing-date-it-was-moved

In this scenario, what you can do is create a new column in the original sheet and have it record a date via automation when it is created. Alternatively, you can simply use the formula =DATE(Created@row)

This column can be used as the actual created date, as it won't change when copied over.

Krystal Garcia

@SteyJ But I need the time stamp capture and not just the date. That is why I have rows being copied to this helper sheet, so I can capture a time stamp of when requests go through the different statuses.

SteyJ

https://community.smartsheet.com/discussion/comment/408784#Comment_408784

If you need the timestamp, you can use something like the TIME function to do so in another helper column.

For standard time:

=TIME(MID(Created@row, 9, 9), 0)

For Military Time

=SUBSTITUTE(TIME(MID(Created@row, 9, 9), 1), ":", "")

Once you have them both in a helper column, you can create another column to combine both.

=Date@row + " " + Time@row

Hope this helps.

Krystal Garcia

Thanks @SteyJ but the Created Date / Time Stamp isn't accurate. We have requests coming in to my main intake sheet from people filling out a form. Each request has an automated/unique request # and they auto populate to a status called "In Review". Once the requests come in from the form submissions, the sheet does already capture that initial Created Date / Time Stamp. But when my team goes to review the request, the status changes to "Pending Response" and that is the time stamp I need to capture. Then it changes status again to "Complete" when resolved and I need to capture that time stamp also.

So when those statuses change, I have automation set up to copy the row to my helper record sheet. But when the row copies to that helper sheet, it brings over that initial Created Date / Time Stamp. Instead, I want my helper sheet to show the Created Date / Time of when that row populated on that helper sheet (not the created date/time from the intake sheet). Do you know how I can do that on my helper record sheet?

SteyJ

https://community.smartsheet.com/discussion/comment/408786#Comment_408786

@Krystal Garcia This process is a little bit more detailed, as you will need to use the modified timestamp column for what I proposed above.

You will then need to create a modified saver sheet, that saves the modified time columns based on the current status.

Finally, you will need to create a new column on the intake sheet for both "Pending Response Timestamp", and "Complete Timestamp". You will then use INDEX/MATCH to pull the row based on the unique request # from the modified saver sheet.

Krystal Garcia

@SteyJ I have my index/match columns on the intake sheet already. but the modified date doesn't work either. Because anytime a new row is copied to the helper record sheet, it changes the modified date on every row. see below:

Andrée Starå

Hi @Krystal Garcia

I hope you're well and safe!

To add to Stey's excellent /advice/answer. (and maybe he answered the same)

This might help!

Please have a look at my post below with a method I developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Krystal Garcia

@Andrée Starå that post hasn’t helped. Sorry. It doesn’t say how to capture date time stamp once I copy a row.

SteyJ

https://community.smartsheet.com/discussion/comment/408788#Comment_408788

@Krystal Garcia Please see this thread where @Paul Newcome answered a similar question I had.

Modified Date Column Issues — Smartsheet Community

Krystal Garcia

Sorry I didn't understand the solution in that post at all. But someone else responded and helped. Thanks everyone

Ric T

Hi @Krystal Garcia,

I'm faced with a similar challenge you shared in your original post, could you kindly share if you found a solution?

Cheers,

Ric

Krystal Garcia

@Ric T The work around is kind of ridiculous but here is what I did.

Essentially, I have a helper sheet for each status that I am capturing a time stamp for.

Then on my main data sheet, every time the status changes, an automation is set up to copy that row to the helper sheet designated for that status.

Helper sheets:

Example of one of my helper sheets and the formula used to capture the created date stamp is below (Yes you have to capture it because of the column type of the 'created date'):

=IFERROR(INDEX(COLLECT([Created Date]@row, [Request Status]@row, "In Review"), 1) + "", "")

Then I use this index match formula with cross references to pull those time stamps back in to my main sheet, so that I can calculate the hours:minutes between each status and pull some metrics on where we can improve.

Example of one of the formulas, but each column has updated cross references:

=IFERROR(INDEX({Tech Support Time Analysis by Status Range 3}, MATCH([Request #]@row, {TA Request #}, 0)) + "", "")

Ric T

Wow, that's a brilliant workaround!! Thank you very much, @Krystal Garcia, for taking the time to share this. Would a limitation be that this was only applicable to new tech support requests? I imagine it would be impossible to back track and retrospectively pull metrics up from entries captured prior to you implementing this solution, but still very awesome to see it's not impossible to create multiple "Creation dates" column function.

Krystal Garcia

Correct @Ric T I have yet to find a solution to back track : ( If you figure one out, let me know because that would be helpful!