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.
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))
-
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.
-
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.
-
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)?
-
-
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?
-
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. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!