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

hbeckner

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).

James Keuning

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))