Calculate days between first and last instance
Need help calculating the number of days between an items first entry on a list to it's latest entry. List is populated via Forms with newest entries at top of list. For example below (in yellow) would return 84 days. If possible, would like to determine the second to the last entry (in green). Appreciate the help.
Best Answer
-
Make sure the column you are putting the formula in is a text/number column.
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!
Answers
-
Lets start by grabbing the first instance...
=MIN(COLLECT(Date:Date, Item:Item, Item@row))
Now we need to grab the most recent and the second most recent.
Most Recent:
=MAX(COLLECT(Date:Date, Item:Item, Item@row))
2nd Most Recent:
=LARGE(COLLECT(Date:Date, Item:Item, Item@row), 2)
We will take the MIN date and subtract it from the MAX or LARGE date by saying if the date on the current row is either the MAX or the LARGE, use it.
=IF(OR(Date@row = MAX, Date@row = LARGE), Date@row) - MIN
=IF(OR(Date@row = MAX(COLLECT(Date:Date, Item:Item, Item@row)), Date@row = LARGE(COLLECT(Date:Date, Item:Item, Item@row), 2)), Date@row) - MIN(COLLECT(Date:Date, Item:Item, Item@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!
-
Thanks for breaking it down into steps for better understanding however I am getting an #INVALID OPERATION
-
Make sure the Date column is set as a date type column. If it is and you are still getting the error, we need to make sure your dates are actual date values instead of text strings that just look like dates.
To do a quick check, insert a temporary text/number column and use this column formula:
=IF(ISDATE(Date@row), "Date", "Text")
This will output "Date" for any rows where the date column has an actual date value, and "Text" for any row where it is not an actual date value.
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!
-
The date row is giving a "date". The "Item" list is populated with item numbers that are preceded with an apostrophe to preserve any leading zeros, could that be a problem?. Also, when I remove the apostrophe on one of the entries to test, the error message is change from #INVALID OPERATION to #DATE EXPECTED but only on entries for that item on the same date. Could it be expecting to see a date in the Item field since the Date field is confirmed as a date?
-
Make sure the column you are putting the formula in is a text/number column.
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!
-
OK, that was part of the problem. The other part is it looks like the leading apostrophe is causing the issue. So 12345 works but '12345 does not. Thanks for your assistance and especially for breaking down the steps so it makes sense.
-
Are all of your item numbers the same length (which is accomplished using leading zeros)?
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!
-
-
Ah. Ok. Then we can get rid of the apostrophes and use a formula in a helper column to add in those leading zeros. How many digits is your item number?
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!
-
Actually, I'm early enough into the creation of the project that I'm just going to lead the item numbers with a letter rather than the apostrophe. Those apostrophes have been causing me problems on multiple fronts : ) Thx much for your help.
-
Happy to help. 👍️
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
- 380 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
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!