Trying to record time between changes in specific contact names

I am trying to record the response times of two individuals (A and B) when they receive notice of their name within a contact column (start time) and when they can remove their name from that column (end time).

Note: there are dozens of other names in these sheets, but we are only focused on A and B.

My thought is to trigger a copy automation when the contact cell changes value AND the contact cell HAS person A or B in it. We would want to see the copied lines of when the row adds names A or B, and then the copied line when the names are removed.

Then with some concatenation, we can create a unique identifier within the copy sheet ex: ProjectName_RowID_PersonA, and with that unique identifier, I can do some formulating to average the times (Less concerned with the formulas for the time being)

My issue is I only want to copy rows that have names A or B, or rows that change TO names A or B, and I am uncertain that the automation properties are sophisticated enough to accomplish this. I am hoping someone has an idea or workaround for this.

Find more posts tagged with

Accepted answers

KPH

Part 1 - revising the automation to separate person A and person B.

A picture paints a thousands words... It is probably easier for me to replicate the automation and take screen shots. Here goes

Automation 1 - When contact changes to A, Update cell value with name or some identified for person before copying the row

Automation 2 - Copy the row

Automation 3 - Copy the row when the contact is changed from A and clear the A/B person column

You will need a copy of automation 1 and 3 for person B.

That will create a series of rows in the second sheet with all the details from the original sheer and A or B in the column I am calling A or B Person.

So now when it comes to averaging you can average separately for person A and B as each entry for each of them will have their unique identifier.

OK so far?

All comments

atewari1

@KPH Progress is the motto!

The formula works thank you! I see now that the difference being calculated is always based on the first instance of the unique identifier. So differences in response time are being made from the first instance of a name in any given row ID rather than the immediately previous one

KPH

Hi @atewari1

Did you complete Part 2 of the message on April 4th? The difference formula needs to use the values in the COUNTIF column you added. You will be comparing the current row with the row that has the same details but the COUNTIF is the same minus 1.

atewari1

@KPH Progress is the motto!

With this formula I am getting the correct difference between a pair of IDs. The issue I have now is if there are several entries associated to an ID, the difference will only calculate from the first instance of the ID and not the previous one.

KPH

Can you share the IDs that you now have - each should be unique, based on the row ID.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.