Largest Gap Between Dates?
Hi . I have a column which contain dates. Is there a formula that would:
- Return the largest gap between the dates in the column?
- Return the dates?
My column contains dates of call logs. I want to see the largest gap that occurred (for example, we didn't call anyone for 7 days). I also want to see the two dates used to calculate the largest gap.
So for the above, the largest gap between dates would be between 01/05/24 and 01/30/24 (25 days).
Answers
-
You would need a few helper columns. One to record how many days until the next call and one to record how many days since the previous.
Next call:
=MIN(COLLECT(Date:Date, Date:Date, AND(@cell <> "", @cell> Date@row))) - Date@row
Previous call:
=Date@row - MAX(COLLECT(Date:Date, Date:Date, @cell< Date@row))
Once we can ensure these are working, we can move on to the next step of flagging start and end dates for the gaps.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
My column is not a fixed length, dates are being added constantly. Is my only option to mofify the formula each time a new date is added?
-
@Paul Newcome thanks for the reply. My column is not a fixed length, dates are being added constantly. Is my only option to modify the formula each time a new date is added?
-
Also, the dates may not always be in sequence.
-
You would not need to modify any formulas as new rows are added. Both of the above formulas are written so that they can be applied as column formulas and should output a number on every row.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 303 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!