Average days in a status & oldest status date

ChristinaC

Hello -

I have a client data sheet, and one of the columns is New Account Status. I've added a column called Misc. 1 that tracks when the New Account Status is changed to Ready to Open.

I have a separate Metrics sheet to track a variety of metrics on. Making any changes to the main client sheet (changing column names, adding columns, etc is very difficult and needs to be avoided).

I'd like to do a few things:

1. From the Misc. 1 column (which is a date column), find the average number of days. I want to show that rows that have this status don't sit in this status for very long.
2. From the Misc. 1 column what is the oldest date.

I need these two values to be returned to my separate Metrics sheet; I can't have these values on the main client data sheet.

Thanks in advance!

Janae G.

Hello @ChristinaC !

Question #1:

So Misc. 1 contains the date that the status changed to Ready to Open- do you have another date column that contains the date the status changed from Ready to Open to something else?

I'm not really sure how you would find the average days without a adding a helper column to your client data sheet… I'm not aware of a way to create a calculated range within an aggregation formula like AVG. You'd have to do something with @cell, maybe also using a COLLECT?

If you could add a helper column to your client data sheet, it would look like this:

Calculate the number of days between Misc. 1 and the date the status changed again (either another column as described above, ex. "Changed Date", or use today's date).

=NETDAYS([Misc. 1]@row, [Changed Date]@row)
»»or try this»»
=IF(ISDATE([Changed Date]@row), NETDAYS([Misc. 1]@row, [Changed Date]@row), NETDAYS([Misc. 1]@row, TODAY()))

Then find the average of these values in the metric sheet. Create cross sheet references back to your client data sheet.

=AVG({ClientData_Days})

Question #2:

Finding the oldest date is simple- just look for the minimum date in the calculated days field:

=MIN({ClientData_Misc.1})

Hope this helps!