Date & Timestamp a specific cell change

MBMaddox

Hello,

I am very new to Smartsheet and I am working on a project tracking sheet. We have the following statuses in a cell: Unassigned; In Progress; Completed; Cancelled; On Hold

When a request is submitted this automatically triggers a 'Created Date' column that logs the date/time of creation. I would like this to be duplicated to two other columns I have 'Modified Date' and 'Completed Date'.

When the status in a cell changes to In Progress or On Hold, I would like the 'Modified Date' cell on that row to log the date and time.

When the status in a cell changes to Completed or Cancelled, I would like the 'Completed Date' cell on that row to log the date and time.

Could you offer step by step instruction or point me to a video that shows how to accomplish this, if possible.

Thank you so much for your help and sorry if I've explained poorly.

TVang

@MBMaddox

Hi, here is another approach.

Let's say that your sheet is called My Sheet. Ensure that this sheet has the system column "Modified Date". In this sheet:

1) Create a Text/Number column named "Timestamp - Status In-Progress or On-Hold" (or something more preferrable).

2) Create a Text/Number column named "Completed Date".

3) Create a Text/Number column named "Last Status" (helper column)*.

4) Create a Text/Number column named "OK to Route?" (helper column) and place the column formula =IF([Last Status]@row = Status@row, 0, 1)

5) Create a new sheet that has only the "Primary Column". Let's name this sheet My Sheet - Record Dates (or something more preferrable).

6) Copy one row from "My Sheet" to "My Sheet - Record Dates". (Do this by left-clicking on the row number and select "Copy to Another Sheet..." from the options.)

7) Create a workflow automation configured as follows:

Trigger: When rows are changed...When Status changes to Any Value

Condition: When OK to Route? is equal to 1.

Action: Move rows...Move to "My Sheet - Record Date"

Open the sheet My Sheet - Record Dates.

8) In the column "Timestamp - Status Changed to In-Progress or On-Hold", create the column formula = IF( OR(Status@row="In Progress", Status@row="On HOld"), [Modified Date]@row+"", "")

9) In the column "Completed Date" create the column formula = IF( OR(Status@row="Completed", Status@row="Canceled"), [Modified Date]@row+"", "")

10) In the column "Last Status" create the column formula =Status@row

11) Create a workflow automation configured as follows:

Trigger: When rows are added or changed...When Primary Column changes to to Any Value

Conditions: Where "Timestamp - Status In-Progress or On-Hold" is not blank OR where "Completed Date" is not blank.

Action: Move rows...Move to "My Sheet"

Done!

Here's how this works.

At the time the row is created in My Sheet, Last Status is blank and OK to Route is 1.

When Status is changed and saved, Modified Date is recorded and automation moves the row into My Sheet - Record Date.

Depending on what Status is equal to, the Modified Date will be recorded in Timestamp - ... or Completed Date. The formula in Last Status sets the value equal to whatever was recorded in Status. Because the trigger was set to "When rows are added or changed" automation moves the recently added row back to My Sheet.

Now back in My Sheet, Status and Last Status are the same so OK to Route? is 0 (or False). This fails the condition for automation to move the row until someone changes the value in Status.

_______________

*The helper columns prevent an infinite loop from occurring when automation moves a row back from "My Sheet - Record Date".

Hollie Green

Have you tried a sheet automation for record a date there is a template in the automation.

Andrée Starå

Hi @MBMaddox

I hope you're well and safe!

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

Would that work/help?

I hope that helps!

Have a fantastic weekend & Happy New Year!

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!

MBMaddox

@Andrée Starå thank you very much for your post. I apologize, I'm still quite new to this, so I'm not familiar with "copy-row automation and a VLOOKUP or combination of INDEX/MATCH". Do you happen to know of a help video or step by step guide that I could reference? Thank you so much!

TVang

@MBMaddox

Hi, here is another approach.

Let's say that your sheet is called My Sheet. Ensure that this sheet has the system column "Modified Date". In this sheet:

1) Create a Text/Number column named "Timestamp - Status In-Progress or On-Hold" (or something more preferrable).

2) Create a Text/Number column named "Completed Date".

3) Create a Text/Number column named "Last Status" (helper column)*.

4) Create a Text/Number column named "OK to Route?" (helper column) and place the column formula =IF([Last Status]@row = Status@row, 0, 1)

5) Create a new sheet that has only the "Primary Column". Let's name this sheet My Sheet - Record Dates (or something more preferrable).

6) Copy one row from "My Sheet" to "My Sheet - Record Dates". (Do this by left-clicking on the row number and select "Copy to Another Sheet..." from the options.)

7) Create a workflow automation configured as follows:

Trigger: When rows are changed...When Status changes to Any Value

Condition: When OK to Route? is equal to 1.

Action: Move rows...Move to "My Sheet - Record Date"

Open the sheet My Sheet - Record Dates.

8) In the column "Timestamp - Status Changed to In-Progress or On-Hold", create the column formula = IF( OR(Status@row="In Progress", Status@row="On HOld"), [Modified Date]@row+"", "")

9) In the column "Completed Date" create the column formula = IF( OR(Status@row="Completed", Status@row="Canceled"), [Modified Date]@row+"", "")

10) In the column "Last Status" create the column formula =Status@row

11) Create a workflow automation configured as follows:

Trigger: When rows are added or changed...When Primary Column changes to to Any Value

Conditions: Where "Timestamp - Status In-Progress or On-Hold" is not blank OR where "Completed Date" is not blank.

Action: Move rows...Move to "My Sheet"

Done!

Here's how this works.

At the time the row is created in My Sheet, Last Status is blank and OK to Route is 1.

When Status is changed and saved, Modified Date is recorded and automation moves the row into My Sheet - Record Date.

Depending on what Status is equal to, the Modified Date will be recorded in Timestamp - ... or Completed Date. The formula in Last Status sets the value equal to whatever was recorded in Status. Because the trigger was set to "When rows are added or changed" automation moves the recently added row back to My Sheet.

Now back in My Sheet, Status and Last Status are the same so OK to Route? is 0 (or False). This fails the condition for automation to move the row until someone changes the value in Status.

_______________

*The helper columns prevent an infinite loop from occurring when automation moves a row back from "My Sheet - Record Date".

MBMaddox

@Toufong Vang WOW! Thank you so much for your very thorough and helpful answer. You are amazing!

Ali412

@Toufong Vang Thank you for the nice workaround!

I noticed that the returned rows are sent to the bottom of the sheet.

Is it possible to return the rows to the same location they were taken from?

Andrée Starå

Hi @Ali412

I hope you're well and safe!

It's not possible with Toufong's excellent method, but you can do it with mine because it collects the value back to the same row.

More info: 

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

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!

Ali412

@Andrée Starå Thanks Andre!

My issue is that I still do not have a unique ID per each row.

However, I realized that I don't really need accurate timestamp that much, so I might just settle for the workflow template of recording the date.

Andrée Starå

@Ali412

Happy to help!

You can add an Autonumbering column and have the unique ID you need.

Make sense?

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

TVang

Hi, @Ali412, if you don't already have an auto-number column, then:

1) Sort the sheet in the order that you would like.

2) Create an auto-number column.

3) Sort the sheet on the auto-number column.

You can avoid having to perform the sort each time there is a new row if you use @Andrée Starå's "VLOOKUP/INDEX/MATCH" method.

Chamudi Withanawasam

You can create 2 automated workflows:

Completed: Create an automated flow triggered when the cell content of Status changes to Complete or Cancelled and record the date in Complete column

Modified: Flow triggered when the cell content of Status changes to In Progress or On Hold and record the date in Modified column

Nuno Santos

@Toufong Vang

I truly appreciate your detailed step-by-step guidance; it was extremely helpful. If you don’t mind, I have a related issue that I was hoping to get your insight on.

When a row is moved back to "My Sheet" from "My Sheet - Record Dates", the "In Progress" timestamp is being removed, and only the "Completed Date" remains. However, I need to ensure that the "In Progress" date and time remain static while also capturing the "Completed Date" so that I can accurately report the duration from when work started to when it was completed.

Would you happen to have a workaround for this? Your cooperation and support are greatly appreciated.

Thank you in advance for your help!

Best regards,

Humble

@Toufong Vang

Hi! I am also trying to utilize this as a work around for my sheet. I am struggling with the row being returned at the bottom of my sheet rather than it's original location. This throws off a lot of other formulas and make the sheet essentially useless. is there a way to return the cell back to it's original location?

For instance, if this was row 555 out of 895. When I test run this the row is sent to the other sheet and then returns back to my main sheet as row 895 not row 555 where it is needed.

@Nuno Santos

I just made a third sheet. A second Record Dates sheet and changed the formulas to refer to and run the operations for in progress on one sheet and complete on the other.