How to average the time between various dates if conditions are met

kss5229

I'm building a spreadsheet with monthly metrics and having issues creating a formula.

There will be a separate smartsheet with individual events (1 row per event). Each event has ~3 review cycles. Each review cycle includes the date the draft was provided & the date the draft returned.

Formula I'm trying to build:

If the date of notification for the event was within X month, average duration for all review cycles (each review cycle has a column for [date draft X returned] & [date draft X provided]).

I need the formula to go through all rows to find any rows with a date of notification within that month and if it meets that crtieria, to average the 3 review cycles for that event alongside the average of any other review cycles for events that also occurred within that month.

The equation I've tried to use so far is:

=AVERAGEIF({Date of Notification}, MONTH(@cell) = [Start Date]@row, NETDAYS({date draft 1 provided}, {date draft 1 returned})).

This equation only includes 1 review cycle and so far it doesn't work, let alone to add in the remaining 2 review cycles for each event.

Paul Newcome

In that case you can create a row by row average across the review cycles in a single column and then average this single column.

=AVG([First Cycle End]@row - [First Cycle Start]@row, [Second Cycle End]@row - [Second Cycle Start]@row, ……………)

Paul Newcome

Try this while referencing the entire date of notification column:

=AVERAGEIF({Date of Notification}, IFERROR(MONTH(@cell), 0) = MONTH([Start Date]@row), {Phase 1 Review Turnaround})

Paul Newcome

Are you able to provide some screenshots for context?

Generally speaking though, you would need to output the duration on each row of the source sheet. Then you can average the duration column based on the month of the date column.

kss5229

Here is a screenshot:

For this formula, say I'm building metrics for Feb 2023. So I want the formula to find the rows in red boxes, then to calculate the duration of each review cycle & average the results.

Is there a way to do this without creating helper columns to calculate the duration of each review cycle on the source sheet? For each event, there are ~3 review cycles for each part of the event and events are split into 3 parts. So I already have 18 columns just tracking review cycle dates that drafts are provided & returned. I'd rather not add 6 more helper columns to calculate each duration if it can be avoided.

Paul Newcome

It cannot be done without the helper columns. You can hide helper columns after setting them up though if you are worried about making sure the sheet stays clean.

kss5229

I've added the helper columns, but am still struggling to figure out how to get the formula to work correctly. If the duration helper columns are not adjacent, I'm not sure how to properly select the cells in scope I'm trying to average? or how to get it to average the 3 review cycles for every row with an event in that particular month?

This is what I have so far:

=AVERAGEIF({Date of Notification}, MONTH(@cell) = [Start Date]@row, {Initial Draft Turnaround Duration}, {Rev1 Turnaround Duration}).

Paul Newcome

Are you wanting separate averages between the review cycles or are you wanting just a single overall average?

kss5229

I'm wanting just a single average for all review cycles for all events that had a date of notification within that month

Paul Newcome

In that case you can create a row by row average across the review cycles in a single column and then average this single column.

=AVG([First Cycle End]@row - [First Cycle Start]@row, [Second Cycle End]@row - [Second Cycle Start]@row, ……………)

kss5229

Thanks! That worked for the individual event smartsheet. However, I'm getting an "#INCORRECT ARGUMENT SET" error in my metrics smartsheet when I try to average that column:

=AVERAGEIF({Date of Notification}, MONTH(@cell) = [Start Date]@row, {Phase 1 Review Turnaround})

Where {Date of Notification} is referencing a specific cell from the individual event smartsheet.

When I change the {Date of Notification} to reference the whole Date of Notification column, I get an "#INVALID OPERATION" error.

Paul Newcome

Try this while referencing the entire date of notification column:

=AVERAGEIF({Date of Notification}, IFERROR(MONTH(@cell), 0) = MONTH([Start Date]@row), {Phase 1 Review Turnaround})

kss5229

That worked! I ended up tweaking it a little because I have dates across multiple years, so I needed it to account for both month and year:

=AVERAGEIF({Date of Notification}, AND(IFERROR(MONTH(@cell), 0) = MONTH([Start Date]@row), IFERROR(YEAR(@cell), 0) = YEAR([Start Date]@row)), {Phase 1 Review Turnaround})

Thanks!

Paul Newcome

Happy to help. 👍️