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.
Best Answer
-
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?
Answers
-
You can add an automation to trigger when a value changes to something but it is a little more tricky to trigger when it changes from something.
How about:
Adding a checkbox column to the sheet as an identifier that a row is an A or B person row. You don't need to put anything in this column.
Then:
Automation 1
- Trigger when row is added or changed
- When contact column changes to
- Tick person A or person B
Copy rows to another sheet as per your original plan. These will be the rows where the row becomes an A or B person row.
THEN add another action to change a cell value. Use this to tick the box in the column we created. This will flag this as a row with an A or B person as the contact. We can then use this as a condition for the next automation.
Automation 2
- Trigger when row is changed
- Where contact column changes to Any Value
Add a condition
- Where field that we checked in automation 1 is checked
Copy rows to another sheet as per your original plan. This will copy the rows that were A or B and have changed.
Untick the box in the column we created so that if these rows change again the rows will not be copied.
Let me know how you get on!
-
@KPH I created a helper checkbox column to only tick when A or B exist in the contact column. The checkbox changing from any value triggers the automation to copy the row!
In my copy sheet I have a concatenated unique identifier with the project name, ID, and personnel name
Now I am trying to figure out how I can average the times taken between A/B name change instances for a given row. I have a created date column that will timestamp when the rows are copied, and want to try and get the average difference of all time stamps with matching ID#s
-
Glad that automation worked for you, @atewari1
The average time between timestamps is the tricky part as there isn't a time format in smartsheet. I was impressed that you said you weren't concerned by that part.
I am pressed for time right now so may I direct you to an answer I posted for a similar question?
-
@KPH My initial lack of concern was just to avoid overwhelming myself haha!
I have taken a look at your previous post and being able to pull date values from the created/modified columns has helped a great deal.
My current problem at hand is being able to calculate these time differences across entries on different rows.
If I were to have 2 or more line items corresponding to ID 99, I would want to find the net average response time between all the entries. The goal here is to see the time taken between person A or B having their name mentioned to when they can remove their name and add someone else.
My first thought is to create a way to compare any given pair of responses and see what the time between them is (Through the use of some sort of indexing/matching via the unique identifier), then separately average all those comparisons.
-
At risk of overwhelming you😬😀.....you have three things going on here (in addition to the automation to copy the rows which was step 1). I'll handle each separately.
2. Compare the given pair
The two rows you want to compare have many details the same, one will be assigned to an A or B person (copied when they are assigned) and the other will not (copied when they are removed) and the ID is the same on both rows of the pair, and does not appear on any other pairs. If that is correct....
You could add a column to the sheet that contains the copied rows and in that column to do the math between the two rows with the same ID.
If we just look at date to start with (see part 4 for time), we could do something like this:
The formula in the difference column is
=NETDAYS(INDEX([Modified Date]:[Modified Date], MATCH([Issue # / ROW ID]@row, [Issue # / ROW ID]:[Issue # / ROW ID], 0)), [Modified Date]@row)-1
This finds the days between the first date in the column that has an ID that matches the ID on the current row, and the date on the current row. We subtract one from the result to exclude the current day.
3. Averages
You should be able to do the average on the difference column. If you use the AVERAGEIF function instead of AVG you can exclude the 0s (which would halve your average).
=AVERAGEIF(Difference:Difference, >0)
I would wrap the formula that you end up with in step 2 in an IFERROR so any partial pairs will return 0 rather than an error message that would prevent the Average from working.
=IFERROR(NETDAYS(INDEX([Modified Date]:[Modified Date], MATCH([Issue # / ROW ID]@row, [Issue # / ROW ID]:[Issue # / ROW ID], 0)), [Modified Date]@row) - 1, 0)
4. Work with time
As well as extracting the date from the Modified Date you are going to need to extract the time, in hours from the start of the day. You need hours (or minutes) to do the math, you can't do it with a timestamp.
Below is the formula I suggested earlier. Can you try this? I can then help with the math we'll need to make a formula like that in part 2 but using the number you will create with this formula.....
Step 2 - Time Stamp as Hours Since Start of Day (Modified)
Formula
=IF(RIGHT(Modifed@row, 1) = "M", (IF(VALUE(LEFT(RIGHT(Modifed@row, LEN(Modifed@row) - 9), FIND(":", RIGHT(Modifed@row, LEN(Modifed@row) - 9)) - 1)) = 12, 0, VALUE(LEFT(RIGHT(Modifed@row, LEN(Modifed@row) - 9), FIND(":", RIGHT(Modifed@row, LEN(Modifed@row) - 9)) - 1))) * 60) + IF(RIGHT(Modifed@row, 2) = "PM", 720, 0), (VALUE(LEFT(RIGHT(Modifed@row, LEN(Modifed@row) - 9), FIND(":", RIGHT(Modifed@row, LEN(Modifed@row) - 9)) - 1)) * 60)) + VALUE(MID(Modifed@row, FIND(":", Modifed@row) + 1, 2))Explanation
The formula starts with an IF function to check whether the Modified column ends with "M". If it does, the function to calculate minutes since midnight for 12-hour clock times is used. If there is no "M", a simpler formula is used for those with a 24-hour clock format.
Note - If there are 12-hour clocks in non-English languages (where AM and PM aren't used) a change will be needed here.
More detail
The formula takes the hour part of the timestamp and uses that to find the minutes since midnight. If this is 24 hour clock that is simply hours times 60. This part:
(VALUE(LEFT(RIGHT(Modified@row, LEN(Modified@row) - 9), FIND(":", RIGHT(Modified@row, LEN(Modified@row) - 9)) - 1)) * 60))
If this is 12 hour clock it converts 12 to 0 (as 12:30 is 0 hours since midnight or noon). It multiples the hours by 60. This part:
(IF(VALUE(LEFT(RIGHT(Modified@row, LEN(Modified@row) - 9), FIND(":", RIGHT(Modified@row, LEN(Modified@row) - 9)) - 1)) = 12, 0, VALUE(LEFT(RIGHT(Modified@row, LEN(Modified@row) - 9), FIND(":", RIGHT(Modified@row, LEN(Modified@row) - 9)) - 1))) * 60)
It also adds an extra 720 minutes if the time is PM. This part:
+ IF(RIGHT(Modified@row, 2) = "PM", 720, 0)
It then adds the minutes part of the timestamp. This part:
+ VALUE(MID(Modified@row, FIND(":", Modified@row) + 1, 2))
I may not be available on Monday and Tuesday next week, but will come back if you need help.
-
@KPH This has been incredibly helpful I cant express how much help this has been. Thank you, seriously.
The only trouble I am having is with the averages. Within the averages, I need to calculate the separate response averages for persons A and B.
Additionally, the way the Difference formula is set up, the index matching compares the first instance of the row ID existing to any given cell, so when the response time between a pair of line items may be 1-2 days, it is comparing to the first instance of an ID in the sheet so at the moment it is saying 6 days for the difference.
Im having trouble figuring out how to break the difference based on pairs of copied rows corresponding to either person A or B and the other name it changes to/from
*There are no instances where person A's name would be replaced with person B or vice versa.
-
Hi @atewari1
No problem. I am glad I am making sense! Nothing you say seems insurmountable.
- The easiest way to keep person A and B separate is probably to duplicate the original automation and change the checkbox that we had for person A or B to a text field and populating this with either A or B.
- It sounds like the ID may not actually be unique to each pair. If so, then we will need a second thing to match on. If there isn't a single thing we can match on multiple things by swapping INDEX MATCH for INDEX COLLECT - in other words, ID and something and something must also match. Is there something(s) in the data that matches the pairs but doesn't match anything else? If not, we can probably do something where we auto number the rows and then match on the row that has the largest number less than the number of the partner.
- The change in step 1 should help with this. You will have a record to show the row was A or was B rather than was A or B.
-
Just to clarify your points
- At the moment the checkbox will tick/untick at the presence of either name A or B. Are you saying to have it be a text column instead that will present names A or B when their name is within the contact column to then average by those names? The automation runs when the box is ticked/unticked so if the checkbox is changed to the names, is the automation to remain the same: copy row when the name is present/copy row when the name leaves the column? I think this would still yield a blank entry every other row
- Since there can be loads of back and forth for any given row you are correct that ID is not unique only to a pair. Adding an auto number to this sheet seems like the way to approach this, but there is already an auto # row being copied as that is the ID column and we cannot have 2 auto number columns on a single sheet.
- To point 1, the "was" would retain a blank for when the row changes to a name that isnt A or B
-
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?
-
Part 3 - Blanks
Hopefully the solution in part 1 solved this.
Part 2 - How to identify pairs
You are 100% correct. If your ID number is an auto number column we cannot add an autonumber column to the sheet the rows are copied into. But that is OK, we've got this, we can make our own!
Try adding a new column and using this formula (drag it down to all rows with data in):
=COUNT([A or B person]$1:[A or B person]@row)
The only issue is that you can't make this a column formula, but so long as it is on the last row before a new row is added, it should be copied down to the new row automatically. You may have issues if anyone clicks in the blank rows at the end of the sheet causing the newly copied rows to appear after a gap.
You could also count the A rows separately from the B rows by using COUNTIF instead of COUNT:
=COUNTIF([A or B person]$1:[A or B person]@row, "A")
Which will simplify the next step.
Let me know how you get on with that. The next step is to change the difference formula to use the values we now have in the COUNTIF.
-
Looks like part 1 is working for you 💃
Let me know if you have a problem with the difference formula.
-
@KPH I just learned so much more about the automation capability we have thank you!
I've gotten the automation and new ID method all setup.
For the formula I am using an Index Collect. Regarding Collect vs Match, the range that we state for collect I would assume to be the same range we are indexing from when it is index/match? I've come up with the following based on the changes:
=IFERROR(NETDAYS(INDEX(COLLECT([Modified Date]:[Modified Date], [Procurement Personell]:[Procurement Personell], [Procurement Personell]@row, [Issue # / ROW ID]:[Issue # / ROW ID], [Issue # / ROW ID]@row)), [Modified Date]@row), 0)
I am getting an INCORRECT ARGUMENT when removing the iferror
-
Hi
Regarding Collect vs Match, the range that we state for collect I would assume to be the same range we are indexing from when it is index/match?
Yes. The COLLECT will provide the range and in effect the row as well as it should only return one row that matches all the criteria.
I am getting an INCORRECT ARGUMENT when removing the iferror
You are missing the row index from the INDEX function (with MATCH you specify the range and then use MATCH to find the row index, with COLLECT the range is the COLLECT and you need to enter the row index). You might have just put it in the wrong place as there is a ,0 in the formula later - that one is the result to return if there is an error. Just add the little fella in bold (and his comma).
=IFERROR(NETDAYS(INDEX(COLLECT([Modified Date]:[Modified Date], [Procurement Personell]:[Procurement Personell], [Procurement Personell]@row, [Issue # / ROW ID]:[Issue # / ROW ID], [Issue # / ROW ID]@row), 0), [Modified Date]@row), 0)
-
@KPH This formula is still yielding an invalid data type when I test without the IFERROR. Im confused as the output should be a number and the column is a text/num column
-
Hi @atewari1
Adding the 0 changed the error from INCORRECT ARGUMENT to INVALID DATA TYPE. That is still progress. 🤗
Not much progress, but still progress.
The issue is the 0 I said to add. I should have said 1. Got myself all confused by what you were doing and thought you meant 0 and had it in the wrong place. But 0 makes no sense, of course you didn't mean that. Row 0 is useless. You want row 1.
Try this
=IFERROR(NETDAYS(INDEX(COLLECT([Modified Date]:[Modified Date], [Procurement Personell]:[Procurement Personell], [Procurement Personell]@row, [Issue # / ROW ID]:[Issue # / ROW ID], [Issue # / ROW ID]@row), 1), [Modified Date]@row), 0)
Sorry!
Help Article Resources
Categories
Check out the Formula Handbook template!
