NETDAYS using MAX and Criteria

Justin Mauzy

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.

ericncarr

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!

ericncarr

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

This works perfectly. Thank you.