Multiple Time Stamp Measurements

Paul Newcome

Assuming the {TA Created Date/Time} range is supposed to be referencing the date/time column in the second screenshot of your last post, I believe that range may be set up incorrectly as I do not see those little black arrows in the bottom right corner of each cell that indicates it is being used in a cross sheet reference.

Krystal Garcia

good catch @Paul Newcome I updated and now get error code #Invalid Value

For reference, here are my cross sheet references in yellow being used in this formula

Paul Newcome

@Krystal Garcia So that particular error coming from an INDEX/COLLECT is typically the equivalent of a #NO MATCH coming from an INDEX/MATCH formula. Are you able to provide side by side screenshots of the data that it is supposed to be matching on and pulling from both sheets?

Krystal Garcia

on this main intake sheet I am trying to capture the time of each 'Request Status'. So how long does it take to get from Not Started to In Review, then to Completed for example. The statuses are below

To do that, I have automation set up to copy the rows every time the Request Status changes. The rows copy to the helper sheet I created called 'time analysis by status'. Here are the rows on this time analysis sheet:

From there I am pulling those time stamps back into the mainsheet so on all requests, there are columns that have the Request Status time stamps. So that then I can add columns for formulas to tell me how long (hh:mm) it took between each status.

So the formula used on the main intake sheet is supposed to say pull in date/time stamp using the Request # as the unique identifier, if the status is 'in review' or 'completed', etc.... I didn't know if helper columns were needed on the Time analysis helper sheet but I did add them, if those would be easier in the index formula?

The index/match formula I used on the main intake sheet works for only the first column called 'Not Started' date/time stamp. But i can't figure out why that logic doesn't work for the other statuses?

Krystal Garcia

@Andrée Starå Can you see most recent comment above and tell me what I am doing wrong?