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

kss5229
kss5229 ✭✭
edited 05/08/24 in Formulas and Functions

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.

Tags:

Best Answers

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!