Getting Oldest Date based on another field

I have created a Metrics Sheet to roll up the oldest date based on another field. I am trying to have it return the oldest date in the "Date Sent to CSOS" column if the Date approved by CSOS column is blank. I can also use the if CSOS Review column is Submitted but I can't get either one to work properly.

Does anyone have any ideas on how to get this formula to work?

Kristie Diersen 😀

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @Genevieve P I'm struggling to see how this would work. The MATCH function would pull a number for a single cell, so the MIN function would only be looking at a single cell so there would be nothing for the MIN to compare. Only a singe point of data.

    I tried testing it (ranges on the same sheet), and ended up with an #INVALID VALUE error.

    I was however able to get a MIN/COLLECT to work.

    =MIN(COLLECT([Date Sent to CSOS]:[Date Sent to CSOS], [Date approved by CSOS]:[Date approved by CSOS], @cell = ""))

    =MIN(COLLECT({Date Sent to CSOS in other sheet}, {Date approved by CSOS in other sheet}, @cell = ""))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!