NetDays with an If

JSpears

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!

MichaelTCA

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)

JSpears

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....

MichaelTCA

@JSpears

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))))