NETDAYS using MAX and Criteria
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
-
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
-
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!
-
This works perfectly. Thank you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!