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
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!