Calculating Average Days between 2 columns

Lisa Riley

Hi All!

I am making a report for a Risk Submission Sheet and I have two columns within my sheet: "Date Submitted" and "Date Closed."

I want to create a widget for a report that calculates the average days it takes to close a risk from the "Date Submitted" to the "Date Closed" for ALL submissions.

Knowing that 1) risks added are ongoing and 2) some "date closed" fields will be empty until closed, how would I write this formula?

My head is hurting from trying to figure this out, so any help is super appreciated.

Thanks!

Lisa

Heather Duff

Hi @Lisa Riley ,

Try something like this:

=IF(ISDATE([Date Closed]@row), [Date Closed]@row - [Date submitted]@row, "")

This translates to: If there is a date in the Date Closed column of this row, calculate the number of days that passed from the submission date to the closed date; otherwise, remain blank.

Let me know if it works!

Best,

Heather

Lisa Riley

Thank you! What would the @row refer to in this case?

Heather Duff

Hi @Lisa Riley ,

The @row means "in this row". So, if you're putting the formula in row 3, it would reference [Date Closed]3 and [Date submitted]3. @row is a great way to be able to reuse a formula (or use it as a column formula) without accidentally referencing the wrong row.

Lisa Riley

Thank you!