MIN DATE with Conditions

Options

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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    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! 😊

  • Karen Hansard
    Karen Hansard ✭✭✭✭
    Options

    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.

  • Genevieve P.
    Options

    Hi @Karen Hansard

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Karen Hansard
    Karen Hansard ✭✭✭✭
    Options

    Hi Genevieve. That worked. Thanks so much.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!