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

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    edited 09/11/23

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!