Collect and display nearest dates

I have a job tracker designed to track the art components of our jobs. We would like to have a column display the next nearest due date for that row. My trouble is we may have 1-10 due dates on a given row and there are non-date columns between each of the due date columns. I'm fairly sure I need to use a MIN(COLLECT( type formula, but not sure how to arrange it. Screen shot below w/ pertinent columns circled.

NextDateExample.JPG

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try something like this...

     

    =MIN(COLLECT([First Date Column]@row:[Last Date Column]@row, [First Date Column]@row:[Last Date Column]@row, AND(ISDATE(@cell), @cell > TODAY())))

    thinkspi.com

  • So I tried that and got and "Invalid Column Value". I may have typed in something incorrectly. Formula below.

    =MIN(COLLECT([Assets Due]@row:[Component10 Due to Deliver]@row, [Assets Due]@row:[Component10 Due to Deliver]@row, AND(ISDATE(@cell), @cell > TODAY())))

    Example2.JPG

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Is your [Next Due Date] column set as a date type?

    thinkspi.com

  • MakeItHappen
    MakeItHappen ✭✭✭✭✭✭

    Exactly what I wanted. It worked for me.