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!


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


    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.


  • 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 ✭✭✭✭✭✭


    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!