How to Calculate Age from a Specific Date & Average Results

kss5229

I am trying to average the age of multiple events as of specific dates (end of each month).

Currently I have "Age" calculated from "Today()" in a column in another sheet. I would like to instead calculate the Age from "Date of Notification" to "End Date", and would like to avoid using a helper column to do so. I do not want to change the "Age" column I currently have or create another age calculation helper column.

AVG(
      COLLECT(
              {Age}, 

              {Status}, 
              @cell <> "Complete", 
              {Date of Notification}, 
              @cell <= [End Date]@row
     )
)

Paul Newcome

What do you currently have in your Age column? Are you able to provide some screenshots for context?

kss5229

Currently the Age column is on a separate sheet and calculates the total age/duration of each event (to the day it was closed or to today if still open). Unfortunately, I can't easily provide a screenshot.

Paul Newcome

I understand it is on a separate sheet and does a calculation, but what is the formula you currently have in the Age column?

kss5229

Ah, sorry: Here is the formula:

=IFERROR(NETDAYS([Date of Notification]@row, IF([Status]@row = "Complete", IF([Step 2 Closure Date]@row = "", [Step 1 Closure Date]@row, [Step 2 Closure Date]@row), TODAY())), "")

Paul Newcome

Unfortunately you are going to need to keep the helper column on the source sheet in this instance.

kss5229

When you say keep the helper column, you mean create a new one? If I were to do that, would I need a new helper column for each progressive month? I'm trying to show monthly metrics where it has the average age of incomplete events calculated at the end of the respective month.