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

Answers

  • Paul Newcome
    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))

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

  • Paul Newcome
    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.

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    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?

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!