Capture Date and Time when column is changed

Mashael

Hello everyone!

I want to know if there is anyway to capture the date and time using if function, when a column value is changed to "Approved"

Let's say I have two columns as follows:

Status Time

Approved if ( [Status]@row = "Approved", Today() AND Time(), 0)

is it possible to achieve this in smartsheet?

Thanks !!

Paul Newcome

It is not possible that way.

You can use this method I developed to capture the date/timestamp and then use an INDEX/MATCH to bring it back to your working sheet.

=IFERROR(INDEX({Copy Sheet Modified Column}, MATCH(ID@row, {Copy Sheet ID Column}, 0)) + "", "")

Date/Time Stamp Option

Paul Newcome

Nov 21, 2019

While trying to figure something else out, I stumbled across a new option for having specific actions date/time stamped using the stem generated Modified (date) type column and the new Copy Row automation.

.

DISCLAIMER:

This does not scale very well as is. If you have a lot of rows you are wanting to track a lot of changes on, it can get very cumbersome very quickly.

My personal use for this solution is tracking a one-time change on each row (think "Completed" box gets checked).

.

Here's how it works...

I have the system generated Modified (date) column. I have another set of columns that separates the date from the time for further calculations, but you could even use a single extra column that simply duplicates the Modified column.

Set up the automation to trigger according to what you want to capture the date/time stamp for.

When a row is copied, only the data is copied. Not the formula. So whatever data is in the extra column that is formula driven to duplicate the Modified column will be pulled, but the formula to duplicate won't. It will also pull attachments and comments as well.

This effectively captures the date/time stamp for a specific action.

.

Yes. The date/time stamp is on a different sheet, but that just means that you can restrict access to the historical data.

Yes. It doesn't scale very well, but at least it is a step in the right direction and gives us an option to date/time stamp natively within SS.

---

View Post

Andrée Starå

Hi @Mashael

I hope you're well and safe!

Unfortunately, it's not possible now, but it's an excellent idea!

Please submit this as a Product Feedback or Idea (If it hasn't been added already) when you have a moment.

Here's a possible workaround or workarounds

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

More info: 

Lock or Store Date/Value Solution without using Zapier

Andrée Starå

May 24, 2020

Hi all,

I hope you're well and safe!

I've developed a solution that lets you store the date or value.

You'd use the copy-row automation and a VLOOKUP or combination of INDEX/MATCH to make it work.

We'd trigger the copy-row to another sheet and get the created date/other value and then use the VLOOKUP/INDEX/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id/value that we can use, it will work.

Also, this opens up more options with auto-numbering and similar.

Depending on other workflows, you could reuse one sheet for multiple workflows.

I hope that helps!

Let me know if you have any questions!

Stay safe and have a fantastic day!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as helpful. It will make it easier for others to find a solution.

---

View Post

Would that work/help?

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!