Net Days Between Due Dates, Referencing Current Row and Row Above

I am hoping to identify a duration between dates on my sheet but would like to reference the current row and row above in the formula. I created two helped columns to identify Row # - auto number column "Auto" and a "Row #" column with the following formula =MATCH(Auto@row, Auto:Auto, 0).

Tags:

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    edited 03/12/22

    Will your sheet always be sorted the exact same? If not, you will want to do something that finds the MAX date of the dates that are less than the date in the current record, which you can do pretty easily with MAX and COLLECT.

    Something like this:

    =[Event DAte]@row - MAX(COLLECT([Event DAte]:[Event DAte], [Event DAte]:[Event DAte], <[Event DAte]@row))

    If you want to peg this to a ROW ID autonumber, you can try this:

    =[Event DAte]@row - MAX(COLLECT([Event DAte]:[Event DAte], [Row ID]:[Row ID], <[Row ID]@row))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!