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