# Calculating Average Days between 2 columns

Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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.

• Options

Thank you!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!