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.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!