How do I return the oldest date from one column based on criteria from another?

I have been trying to use the min(collect) formula to return the oldest date if another column has Yes in it.

=MIN(COLLECT({New Daily Reports to Process Range 1}, {New Daily Reports to Process Range 1}, {New Daily Reports to Process Range 4} = "Yes"), TODAY())

I am referencing below and if there is a "yes" in the column I want it to return the oldest date that is yes. If there isn't a yes then return today. Any suggestion? I thought I had it figured out until value1 had a "yes" and it still turns today.



Answers

  • blmccue
    blmccue ✭✭✭

    I think I figured it out.

    =MIN(COLLECT({New Daily Reports to Process Range 26}, {New Daily Reports to Process Range 2}, CONTAINS("Yes", @cell)), TODAY())


    First range is the Date column, second range is the column that has the yes that I am looking for.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @blmccue,

    Assuming range 1 refers to the date column and range 4 as per your picture:

    =MIN(COLLECT({New Daily Reports to Process Range 1}, {New Daily Reports to Process Range 4}, "Yes"), TODAY())

    Will give you the oldest date where CDL Serious Offenses is "Yes".

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!