NETDAYS using MAX and Criteria

Options

Right now, I have a formula that looks at the newest date in an external sheet and return the days since. I am using the - 1 because I don't want to count the current day. This formula works fine.

The external sheet name is Incoming Issues.

Here is the formula:

=NETDAYS(MAX({Incoming Issues Range 1}), TODAY()) - 1

Range 1 is the Date Reported column. Range 2 is the Division column:

What I am trying to do is add a criteria to only look at the rows that the Division column has "Case".

Here is the code I am trying to make work but I keep getting an error.

=IF({Incoming Issues Range 2} = "Case", NETDAYS(MAX({Incoming Issues Range 1}), TODAY()) - 1, "")

Thanks in advance.

Best Answer

  • ericncarr
    ericncarr ✭✭✭✭✭
    Answer ✓
    Options

    Hi @Justin Mauzy,

    I think you'll want to do the MAX of a Collect like this:

    =NETDAYS(MAX(COLLECT({Incoming Issues Range 1}, {Incoming Issues Range 2}, "Case")), TODAY()) - 1

    This does a Max collect only on dates in the Date Reported column that have a corresponding value of "Case" in their Division column. You apply NETDAYS on this maximum date from the collected values, comparing it to today's date and subtracting 1 as you wanted to do. The IF statement won't work in this case, it has to be a Collect that establishes the criterion, hope that makes sense and helps!

Answers

  • ericncarr
    ericncarr ✭✭✭✭✭
    Answer ✓
    Options

    Hi @Justin Mauzy,

    I think you'll want to do the MAX of a Collect like this:

    =NETDAYS(MAX(COLLECT({Incoming Issues Range 1}, {Incoming Issues Range 2}, "Case")), TODAY()) - 1

    This does a Max collect only on dates in the Date Reported column that have a corresponding value of "Case" in their Division column. You apply NETDAYS on this maximum date from the collected values, comparing it to today's date and subtracting 1 as you wanted to do. The IF statement won't work in this case, it has to be a Collect that establishes the criterion, hope that makes sense and helps!

  • Justin Mauzy
    Justin Mauzy ✭✭✭✭✭
    Options

    This works perfectly. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!