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…

Georgie

Hi @kss5229,

Thanks for sharing the screenshots! I see that the Age column in the sheet you’re referencing contains a column formula - what’s the formula you’re using there? Since the Age column is being referenced in your AVG(COLLECT) formula, we need to be sure that the calculation is correct in that column first - could that formula be altered to ensure that the age of events is calculated using the notification date for each row?

Thanks,

Georgie

kss5229

Hi Georgie,

Here if my age formula. It counts to Today if the event is still open and it counts to the latest step closure date if it's closed.

=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())), "")

Thanks,

Kayla

Georgie

Hi @kss5229,

Thanks for providing the formula in your Age column! Since that formula is checking if the status of the task is complete and, if a task is not complete, it’s calculating the age to today, this is the cause of your other formula using the total age of the event when events are still open. 

Would you be able to change the formula in the Age column so that it counts to the latest closure date if the event is not complete and to today otherwise? Eg:

• =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())), "")

Alternatively, you could add another column in the source sheet that outputs the age of events using the formula above, then reference this column in your AVG(COLLECT) formula.

Hope that helps!

Georgie

kss5229

Hi Georgie,

Maybe I'm not understanding your proposal correctly. If the event is complete, I don't want to calculate the age to today's date, I want to calculate it to the day it was completed. If it is not complete, I want to calculate the age to the end of respective month. I like having the age column calculated as it currently is, but am open to creating another column with a modified age formula to reference in this formula.

The age using the formula you suggested above is resulting in a blank for all "open" events. When I delete the "IFERROR" part of the formula, I get "#invalid data type"

Georgie

Hi @kss5229,

I just checked that formula in my test sheet and observed the same, but I was able to determine why - since the formula uses either the date in either the “Step 1 Closure Date” or the “Step 2 Closure Date” columns when events are open, if both of these are blank, then the formula returns the INVALID DATA TYPE error (or blank when the formula is wrapped in IFERROR).

Have you confirmed that the affected rows contain a date in either of those columns?

Thanks,

Georgie

kss5229

No, some of the events are still in the first step, so there is no step 1 (and subsequently no step 2) closure date. But for events in Step 2, there is a Step 1 closure date, so the formula is outputting a value there, but it's calculating the age to the Step 1 closure date, when the event is actually still open, so it shoult be calculating to TODAY or alternatively (for the purposes of my intended metrics) I would want it to calculate to the end of the month for which the metrics are being calculated.

Georgie

Hi @kss5229,

So, if I’m understanding correctly, you’re keeping your Age column with the formula counting to Today if the event is still open and it to the latest step closure date if it's complete, with the formula below: 

• =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())), "")

Then, you want a new calculation which does the following:

• When events are in step 1, there’s no closure date, so it should calculate to today
• When events are in step 2 and not complete, there is a step 1 closure date, but as it’s still open, it should calculate to today
• When events are complete, if the step 2 closure date is blank, it should calculate to the step 1 closure date, but if there’s a step 2 closure date, it should calculate the age to that date

If that’s all correct, you can create a new column in the same sheet and use the formula below:

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

This is the formula I’ve used in my “Calculation Age” column below:

If you then update your existing AVG(COLLECT) formula in the other sheet to reference the column with the new formula, it should then work as desired.

Does that work for you?

Georgie

kss5229

Hi @Georgie,

What you're describing in your proposed calculation sounds like what my age equation is already doing. What I'm trying to do here is separately calculate the age as of a new date.

So for instance, if I had two events initiated 01Nov. The first event was closed 11Nov, and the other was still open. I want the metrics for the month of November to calculate the average age for both events: the first event was closed at 10 days old (this part is calculating as I want it to). The second event is still open, but for the sake of the monthly metrics, I care about the age at the end of November (30 days).

For each month that the event continues to stay open, the metrics for that respective month should count the age of the event as of that month (December metrics would show the age of the 2nd event as of 31Dec, so 61 days old I think).

Apologies if I'm misunderstanding your proposed solution - reading and understanding formulas doens't come quite naturally to me

Genevieve P.

Hi @kss5229

@Georgie's formula is for the overall duration from the Start until Today, which it sounds like you have set up already. However in this second sheet, you're looking to average entirely different numbers than what shows in the calculation column (for example, using "December" as the end of the day-count instead of "Today").

Those individual numbers and calculations per-month would need to be in the source sheet.

The cross-sheet calculation as a whole cannot individually calculate each row's duration based on different criteria, and then average that together. You would need to create the row-by-row calculation of duration for that specific month in the source sheet, then 12 unique cross-sheet formulas can average 12 different helper columns.

The three blue columns above are an example of what you would add to the source sheet. Then you can simply create an AVG of each column in your second sheet (or you could use a Report with Grouping and Summary).

Does that make sense?

Cheers,
Genevieve

kss5229

Thanks @Genevieve P.,

That's a bummer to realize it can't be done without the helper columns. Unfortunately, in this case, I don't think that's a feasible solution for my Smartsheet as the metrics are ongoing, so those 12 helper columns this year would need an additional 12 helper columns for next year, etc.

I'm wondering if as an alternative I could do something along the lines of performing the calculation for the age using the TODAY function and then move that row with the metrics to another sheet where they would be stored as stagnant values? For that, I wouldn't be able to get retroactive metrics for previous months without implementing the solution recommended above, but might be able to get accurate metrics moving forward.

I'm not super familiar with moving/copying rows to another sheet. Is it possible to do so such that formulas get removed and only the values remain? And are there any ways to ensure the calculation using the today function would be refreshed on the last day of the month even if the sheet wasn't opened on that day?

Genevieve P.

Hi @kss5229

Copying rows copies only the values of the formula (not the formula itself) unless you have that formula in the next sheet as well 🙂

Here's more information: https://help.smartsheet.com/articles/770589-copying-a-row-to-another-sheet

Yes, you could copy rows at the end of every month to a separate sheet. You can use a time-based trigger on the workflow, and some conditions so it skips unnecessary rows.

You could use a Record a Date workflow in the second sheet to record that copied-over date (the last of the month) in the Step 2 Closure Date column when rows are added. This replaces the need for "Today", especially as Today will change over time, meaning your calculations will always be adjusting. The Record a Date value will stay static.

Make sure you use conditions so that workflow only updates the Date cell when you want it to, so it doesn't replace other values from the current month.

You'll also likely want to update another column (using a Change Cell workflow) with the month it is you're looking to track… e.g. "November" if the rows were copied over on the last day of November. This will be a criteria for your cross-sheet formulas, so it can skip previously copied rows.

Then you'll need to change the formula in the Calculation Age column to look at that month reference (e.g. the "November" text) to know what "start date" to look for or use.

All of this is fairly complicated, especially if you're looking to track over multiple years. Is there any possibility you could change your intake or tracking sheet instead?

For example, have a Parent Row that has the total span of task dates, but use multiple Child Rows to track the task per-month. That way you can keep your original AVG(COLLECT formula on the Total Column but have it filter out by the related rows.

kss5229

Hi @Genevieve P.,

I'm not sure I'm following completely. My thought was to have 3 sheets:

1. The original data sheet
   1. Contains an age calculated for every event. If the event is open, it calculates to today. If the event is closed, it calculates to the last closure date.
2. Source monthly metrics sheet with calculations. Each month has a start date column and an end date column.
   1. Automation workflow on the last date of every month with a condition where "End Date" is today to copy to Metrics Sheet (#3)
3. Metrics sheet where stagnant values from calculations are copied into at the end of every month.

Since sheet #1 uses the "Today" function (and wouldn't update unless the sheet were viewed), I could create a "Today" helper column to be used in calculations with a workflow to update it daily, so the metrics on sheet #2 would be up to date on the last day of the month prior to being copied over to the metrics sheet.

One weakness of this plan is how to update the metrics appropriately when a closure date for an event gets retroactively added to an event in Sheet #1. For example, if an event was closed on 26Oct, but that closure date wasn't entered into sheet #1 until 03Nov, on the date the data was transferred to the 3rd sheet (31Oct2024), the age for that event would have been calculated to "Today" (31Oct2024), even though the event was closed 4 days prior.

What you're describing isn't fitting into my view of the 2 (or 3) sheets. What am I missing?

I am open to the possibility of changing the intake & tracking sheet, but I'm not able to visualize what you mean about parent & child rows and how it would work for this data.

Genevieve P.

Hi @kss5229

The issue with this set up is sheet 2 listed: "Source monthly metrics sheet with calculations"

The monthly calculations are cross-sheet references. This means that it cannot do individual row-by-row calculations per-month to then Average. The row-by-row count of days will need to be in the original sheet, for your cross-sheet to AVG. This means you would still need to have 12 columns in the original sheet to create the calculations in sheet 2, as in my first screen capture.

What you've described above would copy over the formulas as static numbers, yes! However the numbers you'd be getting from the calculations would be from the Notification Date to either the Closure Date of that task or Today…regardless of month.

We could easily COUNT how many rows have a Notification Date in this month! Or a Closure Date! But we cannot average the total number of days that multiple tasks span in XXX Month without those numbers being present somewhere in the source sheet.

If each row was specific to 1 month, and the Notification Date and Closure Date were in the same month, then this is much easier because your Calculation Age column in these rows would be accurate to just one month… you wouldn't need to subtract earlier or later dates (which is why we need the 12 month columns).

The top parent rows show the overall days for each task, but the rows beneath are broken out by month. Then you can filter and AVG easily… perhaps even using a Report instead of formulas.

Cheers,
Genevieve