Calculating Average Days between 2 columns
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
Answers
-
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
-
Thank you! What would the @row refer to in this case?
-
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.
-
Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!