I know I’m going to kick myself when I get an answer, but I haven’t used Coda in a while so I’m a bit rusty at best.

I’m simply trying to make a formula that calculates days between a date in a cell in the row above, verses the cell in the current row (they are in different columns).

To help explain, see my screenshot below. I want ‘Time between visits’ to show number of days between the last rows end date and the current rows start date.

A word of caution for the solution - that solution works only if rows were created in order.

If you have rows created out of order - you might want to actually pick the record with highest end date that is earlier than current records’ start date.

here’s a document that shows results with both approaches and you can see when rows are created out of order- you might get odd results.

This method is a pretty big ask for everyday Excel users coming over who just want to run calcs based on preceding values, which is a mundane task in Excel.

$.02

Also, while playing with this I arrived at another question.

The Filter() formula documentation contains this:

If list is a table, use “thisRow” to reference the current row being evaluated. Otherwise, use “currentValue” to reference the current item being evaluated.

When list is a table and thisRow is used to reference the current row being evaluated, it makes it difficult to Filter() using a logic statement that compares the thisRow that’s being evaluated by Filter() and the thisRow that holds the formula in which Filter() is being run (unless the two instances of thisRow are unique under the hood?). Whereas, if the former were CurrentValue (as with other lists) then thisRow would still be free to use in the latter, and the two could be compared…??

What’s the correct way to think about this? (Because I’m confused !!)

This works, even when dragging rows out of the order in which they were created. thisTable.Find(thisRow) appears to be a stable way to reference relative row position.

It seems too simple, like I must be missing something.

NOTE: Rows “week 4” and “week 5” were dragged out of position and then their dates were populated.

Poorly communicated on my part. I should have clarified that I was only solving for basing a calculation off of a preceding row value, without using RowId().

It was not intended to also solve for the case when start/end dates are changed in such a way that they are no longer in sequence, which your formula does.