Calculating Age Monthly

kss5229

I have a smartsheet with many events that often span several months. I want to calculate the average age of relevant events for each month.

For example, for the month of March, I want it to calculate the age of all events that were closed within the month of March in addition to the average age (as of the end of March) of events that are still open in March.

I have a column that calculates the age of each event. Below is the formula I'm currently using.

=AVG(

        COLLECT(

                {Age}, 

                {Closure Date}, 

                AND(

                      IFERROR(MONTH(@cell ), 0) = MONTH([Start Date]@row), 

                      IFERROR(YEAR(@cell ), 0) = YEAR([Start Date]@row)

                )

         ), 

        COLLECT(

                {Age}, 

                {Status}, 

                @cell <> "Complete", 

                {Date of Notification}, 

                @cell <= [End Date]@row

        )

)

The first half of the formula works correctly. The issue is that the 2nd Collect formula does uses the total age of the event rather than the age at the end of the respective month. i.e. if event notification was 01March and the event is not complete (still open), the formula uses the current age of the event rather than the age as of 31March

Paul Newcome

Are you able to provide some screenshots for context?

kss5229

I did my best to clear out sensitive information, but not sure if this is the level of detail you were looking for…