Time stamp challenge

Moriz Heyss

I use "the copy row" function to a new sheet workaround for a time stamp if a checkbox is checked.

In my original file I use the VLOOKUP funktion to get date and time. So far that workaround is working. My problem is, that in my file where I collect all this "checked" rows, the modified date always changes (to actual date when I refresh the file and an automation was triggered. How can I have a fixed date when a new row was added? Maybe it is a problem that I already have a "created date" imported from my start file? I cant use the modified date because it is always changing.

I cannot lock the row in my start file because it is an working file.

I hope it is clear what I want? Maybe someone can help me...

And also an additional question: is it possible to calculate the difference between two date/time entries?

Paul Newcome

Are you able to provide screenshots for context?

Thread with numerous time based solutions:

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Andrée Starå

Hi @Moriz Heyss

I hope you're well and safe!

You need to use the Created Date system column for it to work.

Did that work?

I hope that helps!

Be safe, and have a fantastic week!

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!

Moriz Heyss

yes, I have the created date system column in my (help)sheet, but the data from other sheet overwrites the data in my "help sheet"

Andrée Starå

@Moriz Heyss

I agree with Paul.

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

Moriz Heyss

In Detail - sorry Screenshot are a mix of german and english:

I have a working file where tasks are defined. Sometime they get the Status "DM".

If this happened, an automation starts => row will be copied to another file to get date and time stamp.

Problem is, that in the "help-file" for the time stamp I get no creation date/time as when the automation was triggered. It overwrites the creation date with the creation date of the working file. And also the modified date will be updated - so I can not use this instead.

Paul Newcome

I would suggest using a text/number helper column with

=Modified@row + ""

as a column formula in the original sheet. This will convert the date/time stamp in the modified column into a text string which is then captured as static when the copy/row automation happens. Using the INDEX/MATCH to pull over the text string should give you the date/time the row was copied over.

Andrée Starå

@Moriz Heyss

Happy to help!

I saw that Paul had answered already!

Let me know if I can help with anything else!

Best,

Andrée

✅Remember! 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!

Moriz Heyss

@Paul Newcome sorry this wont work for me. The modified field is always updated - and then also the new column (with =Modified@row + "") will be updated.

Maybe I have to skip the time stamp. So I have to stay with the date only. I hope sometimes this will be fixed or updated by smartsheet.

Paul Newcome

Right. That's the whole point. But when the Copy Row automation runs (triggered when the box is checked), it will copy that row's data as STATIC data in the other sheet. This means that whatever is in that helper column when the automation runs is now captured as static in the other sheet which you can then use an INDEX/MATCH to pull back into the original sheet.

Andrée Starå

@Moriz Heyss

If you haven't already, 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'd be happy to share an example. 

Please email me at andree@workbold.com, and I'll share it with you.

Paul Newcome

@Moriz Heyss Here's a post with the basic idea outlined. All you would need to do from there is set up the INDEX/MATCH to pull it back over.

https://community.smartsheet.com/discussion/63561/date-time-stamp-option

Moriz Heyss

@Paul Newcome thank you! I will try it - and I will give feedback. But next week I am on holiday. :-)