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.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!