NetDays with an If
I'm wanting to graph the number of days requests are waiting for customer response per month.
So, if a request was closed in June, I want the number of days it spent waiting for the customer.
Thanks in advance to all those brilliant minds out there!
Answers
-
Hello @JSpears
The brackets you are using { } are to reference ranges and not individual cells or rows.
By adjusting the function you have showing, it should look closer to this.
=IF(MONTH([ACM_CL_ClosedDate]@row=6,NETDAYS([ACM_CL_DateRequestClose]@row,[ACM_CL_ClosedDate]@row))
Although, you wouldn't need the IF statement unless you only want values for that month, which can be a pain when graphing because if you want to change the graph in the future, you have to change the function.
This part alone would be enough as a column function. In a report you can filter by the month and is more manageable than changing the function to view a new set of data.
=NETDAYS([ACM_CL_DateRequestClose]@row,[ACM_CL_ClosedDate]@row)
-
Thank you, I'm actually using the {} because I'm referencing columns in another sheet. I'm thinking that I will actually need to add a NetDays column to the source sheet and then pull that into my metrics sheet....
-
NETDAYS only allows individual values to be used, and not ranges. You can use a lookup function to analyze a range and pull a single value. Index/Match would work, or Index/Collect if you have more than 1 criteria.
=IF(MONTH(INDEX({ACM_CL_ClosedDate},MATCH(Lookup@row,{Lookup Range},0)))=6, NETDAYS(INDEX({ACM_CL_DateRequestClose},MATCH(Lookup@row,{Lookup Range},0)),INDEX({ACM_CL_ClosedDate},MATCH(Lookup@row,{Lookup Range},0))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!