Hi guys,
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.
Help much appreciated!
Hello.
I believe End-Start should work?
Yeah, but it needs to be End from the previous row minus the Start from the current row if that makes sense, that’s the bit I’m stuck with
Fantastic, thank you! That was more involved than I thought it would be!
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.
Thanks @Krunal,
I was unclear on this RowId() issue.
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.
EXAMPLE STATEMENTS
If(thisRow = thisTable.First(), start - start, start - thisTable.Filter(thisRow.period = thisRow.period - 1).end.First())
If(thisRow = thisTable.First(), start - start, start - thisTable.Filter(CurrentValue.period = thisRow.period - 1).end.First())
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 
!!)
I just tried something that I learned from Krunal’s post here: Visual Position of the row in table
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.
how does week4 start before week1 in that sample data? if you change the data of start, end it would be not be correct.
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.
