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

Manuel Crespo

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,

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

Manuel Crespo

Thank you very much, Kelly.

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

Manuel Crespo

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.