Difference between the end date in a row and the end date in the previous row

Hello,

I am trying to create a formula in a row to calculate the difference in days between the end date of that row and the end date of the row above it. The end date of the row where the formula is is easy to reference with @row, but how do I reference the end date of the top row?

And how do I avoid that in such a formula I don't get an error in the first row (since it doesn't have a top row)?

Thanks,

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Manuel Crespo

    Depending on how rows in your sheet are added and if the rows are moved around once added, you may need 1-2 helper columns, if they are not already in your sheet.

    If your rows are added to the bottom of your sheet, and you don't slide them around once they are on the sheet, you will need either the system generated Created column, or system Generated autonumber column, [Row ID]. These will allow us to create a reference to the max that is less than the current row, which will correspond to current row - 1. If you do slide rows around, we will need an additional column. Let me know if that is the case.

    I will use the [Row ID] column in my example.

    =IF([Row ID]@row = 1, 0, [End Date]@row- INDEX(COLLECT([End Date]:[End Date], [Row ID]:[Row ID], @cell = [Row ID]@row - 1), 1))

    If the preceding End Date isn't always an earlier date than current row and you want the difference to be absolute, we can add that. If the End Dates aren't chronological then without the absolute you will end up with negative values.

    Will this work for you?

    Kelly

  • Thank you very much, Kelly.

    Although it's not going to be frequent, sometimes I will slide rows around.

  • Hi again.

    Thanks to your indications I have managed to make it work with this solution.

    As you indicated, I created two columns. One called "row identifier" which is automatically numbered.

    And another column called "Row ID" with this formula:


    =IF([Row ID]@row = 1; 1; MATCH([Row ID]@row; [Row ID]:[Row ID]; 0) - 1)


    Thanks, Kelly.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!