Calculate days between first and last instance

Dave Schierman

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.

Paul Newcome

Make sure the column you are putting the formula in is a text/number column.

Paul Newcome

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))

Dave Schierman

Thanks for breaking it down into steps for better understanding however I am getting an #INVALID OPERATION

Paul Newcome

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.

Dave Schierman

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?

Paul Newcome

Make sure the column you are putting the formula in is a text/number column.

Dave Schierman

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.

Paul Newcome

Are all of your item numbers the same length (which is accomplished using leading zeros)?

Dave Schierman

Yes

Paul Newcome

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?

Dave Schierman

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.

Paul Newcome

Happy to help. 👍️