MIN DATE with Conditions
Hello. I'd like to return the minimum (and maximum) date from another sheet with multiple conditions.
Detailed Sheet:
Rollup Sheet
I would like to populate the earliest Start and latest End Date in the Rollup sheet from the detailed sheet where the Request Owner is equal to request owner in rollup sheet and the status is not Complete. My formula does not work. I haven't included the Status in the formula but that would be the second condition I want to meet.
Thanks in advance.
Answers
-
Hi @Karen Hansard,
The formula you would want would be something like this:
=MIN(COLLECT({Start Date}, {Request Owner}, [Request Owner]@row, {Status}, <>"Complete"))
Dataset (sorry about the Euro date format!):
The MAX would use much the same formula, possibly only changing the column references if you wanted the End Date instead of the Start Date.
Hope this helps, but if I've missed something or you've any questions then just post! 😊
-
Hi Nick,
Thanks for the quick response. It works when I only have 1 condition, but I'm having an issue when I include a 2nd condition. I get an #INCORRECT ARGUMENT error.
=MAX(COLLECT({End Date}, {Lead}, [Lead Name]@row, {Status}, <>"Complete"))
Works fine with the one condition below
=MAX(COLLECT({End Date}, {Lead}, [Lead Name]@row))
Any ideas?
K.
-
Your formula structure looks correct - is it possible that your {Status} column has an error in any of its cells? If so, this will bubble up into any other formula referencing it.
You could also try adding an @cell before the <> "Complete" :
=MAX(COLLECT({End Date}, {Lead}, [Lead Name]@row, {Status}, @cell <>"Complete"))
However it should work without that.
Let us know if the {status} column could be the culprit!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi Genevieve. That worked. Thanks so much.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!