How to Calculate Age from a Specific Date & Average Results
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 ) )
Answers
-
What do you currently have in your Age column? Are you able to provide some screenshots for context?
-
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.
-
I understand it is on a separate sheet and does a calculation, but what is the formula you currently have in the Age column?
-
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())), "")
-
Unfortunately you are going to need to keep the helper column on the source sheet in this instance.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!