Find next / subsequent date in list

ateleoh
ateleoh ✭✭
edited 12/09/19 in Formulas and Functions

Hi, I am stumped.  I am trying to figure out how to figure out the second (and third) date for each unique entry in the Provider/Resource column.  I am using this formula to find the first date: 

=IF(Date@row = MIN(COLLECT(Date:Date, [Provider/Resource]:[Provider/Resource], =[Provider/Resource]@row)), Date@row, 0)

smartsheet.PNG

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 10/11/18

    Swap your MIN out for a SMALL.

     

    =SMALL(range, n)

     

    where n represents the nth lowest number within the range. 

     

    So

     

    =SMALL(range, 2)

     

    would provide you with the second smallest number, or in this case, the second date.

     

    (LARGE works the same way for MAX)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!