Multiple Time Stamp Measurements

RDill

Is there a way to have 3 timestamps per sheet and then have a formula calculate the difference? For example, think about an interview process. We need to capture File Received, File Touched, and Status Changed. Then we would want to measure how long it takes to go from received to touched, Touched to status change, and then received to status change. Kicker - it needs to be dated and time stamped.

HELP!

CodyRussell

You can do this with automations, but you'll need 3 individual automations.

Create a new automation from template or scratch:

Trigger: "When Rows are added or changed"

When: "File Received" changes

Run workflow: "When triggered"

Action: "Record a date"

Record date in: "1st Contact time (Manual Entry)"

Repeat this process for each column/status you want to capture the date stamp for.

RDill

But it would only be a date stamp, not a time, we need to count hours.

jmyzk_cloudsmart_jp

Hi @RDill

When I track time, I employ a copy rows automation method. For instance, when I record the start time, pause time, re-start time, and finish time for a task, I duplicate the corresponding row onto another sheet, say a record sheet.  This duplication process is triggered by a change in the 'Track' column's status, which includes dropdown options for start, pause, re-start, and finish.

The record sheet also features a 'Created' column, which logs the date and time when the status change occurs on the source sheet. This 'Created' timestamp represents the moment when the status change initiates the row to be copied onto the record sheet, with a slight time lag to account for any processing or automation delays.

To link this 'Created' value with the source sheet, I utilize an auto-number as a key in a formula. Here's an example of how the formula looks:

• INDEX(COLLECT({Created at the record sheet}, {Row ID at the record sheet}, [Row ID at the source sheet]@row, {Track at the record sheet}, [Track at the source sheet]@row),1)

To ensure the time portion displays correctly on the source sheet without errors, it's crucial to format the column as Text/Number and append three double quotes at the end of the above formula. Additionally, I make use of the recently introduced TIME() function to retrieve the time part in a 24-hour format, simplifying subsequent time difference calculations.

I invite you to explore the demo Dashboard linked below for a practical demonstration of this process."😀

https://app.smartsheet.com/b/publish?EQBCT=e83c7808fff24e21b71e13cb9c3242a9

Krystal Garcia

@jmyzk_cloudsmart_jp I think I am following your instructions but how did you get the file received, file touched, and status changed times back to your "Data" sheet?

Krystal Garcia

That formula is on the Record sheet right? If so, why is there an error on file touched?

jmyzk_cloudsmart_jp

Hi @Krystal Garcia

>> how did you get the file received, file touched, and status changed times back to your "Data" sheet?

I will explain the "File Received". The rest is just changing the "File Received" to "File Touched", etc.

1. When the status changes, the workflow automation copies the row to the record sheet.
2. With this formula, the data sheet gets the Created data on the Record sheet.
   • File Received
     • =IFERROR(INDEX(COLLECT({Created}, {Row ID}, [Row ID]@row, {Track}, "File Received"), 1) + "", "")
     • meaning get the first item of the Record sheet's Created value collected with the condition that the Record sheet's Row ID is equal to the Data sheet's [Row ID]@row and the Record sheet's Track value is equal to "File Received."

>>why is there an error on file touched?

When the workflow automation copied the row, I did not have IFERROR( ,"") for "File Touched" column.

• Before
  • =INDEX(COLLECT({Created}, {Row ID}, [Row ID]@row, {Track}, "File Touched"), 1) + ""
• After
  • =IFERROR(INDEX(COLLECT({Created}, {Row ID}, [Row ID]@row, {Track}, "File Touched"), 1) + "", "")

The copy row automation is not instantaneous. It takes some time, so when the INDEX function tried to get the first item of the COLLECT function's resulting range, as there was no match, the #INVALID VALUE was returned. The automation copied the row with the error.

The published dashboard's Datasheet is editable, so you can test by adding a new traveler and changing the Track value.

Krystal Garcia

Thanks @jmyzk_cloudsmart_jp for your help. Another question, when I set up my automation to copy row to my record sheet, it just copies the created date that the row populated on my original sheet. It doesn't give me the created date of when it hits the record sheet. That is what I need. So how did you get the true created date of when it got your record sheet?

jmyzk_cloudsmart_jp

Hi @Krystal Garcia

You must add a "Created" system column to the record sheet. (Note. You can't have a Created system column in the datasheet, as you want to use the time when the automation copied the row to the record sheet as a time stamp.)

Krystal Garcia

Yup that did the trick, thanks for your help @jmyzk_cloudsmart_jp : )

jmyzk_cloudsmart_jp

Happy to help!😁

Krystal Garcia

@jmyzk_cloudsmart_jp Ok, so I thought I had it figured out but I don't. The formula on my original sheet works for the "Not Started" status column, not not on any of the others. The In Review Time, Pending Parts Time, Pending Tech Response Time, and Completed Time are blank. I can't figure out why

Here is the formula used on the Not Started Time column that works:

Here is the formula for In Review Time that comes up with blank results:

and here is the cross reference for the {TA In Review} portion, it should be pulling from this:

And the other cross reference {Time Analysis Request #} to pull if the request # matches:

Krystal Garcia

@Paul Newcome I see you everywhere helping so thought i'd tag you for assist. See above most recent comment. I have my index/match formula working on one column, but it doesn't on another. I updated the Index cross reference but can't figure it out.

jmyzk_cloudsmart_jp

Hi @Krystal Garcia

The range you are referencing in your INDEX formula is not the Created range of your Tyme Analysis sheet.

My formula uses the COLLECT function, but the range to reference is the Created range. The Created range at the copied sheet represents when the original sheet's status changes to Not Started, In Review, etc. (In my demo case, File received, File Touched, etc.)

Paul Newcome

@Krystal Garcia Are you able to provide a screenshot of both sheets that shows an instance where it should be pulling but is not?

Have you tried removing the IFERROR function to see if there is an error message being suppressed?

Krystal Garcia

@jmyzk_cloudsmart_jp @Paul Newcome I updated the formula to:

=INDEX(COLLECT({TA Created Date/Time}, {TA Request #}, [Request #]@row, {TA Request Status}, "In Review"), 1)

But getting '#incorrect argument' error still

Here is a screenshot of the cross references being used on the helper sheet that rows are copied to: