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).
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!