# Calculating days between dates?

Hey, All,

Still pretty new to Coda. I have this table:

I would like to create a formula in the second column that calculates the number of days since the last date. Can anyone advise on how to do this?

Thanks!

I propose we do this in two steps:

• we create a RowIndex
• we use this RowIndex to define positions in the list

how it goes:

and next the usage of the RowIndex to subtract

Enjoy, Christiaan

3 Likes

Hi @Jono_Bacon ,

A little bit different than what @Christiaan_Huizer suggested but following quite the same direction, here’s a quick sample As Christiaan did, I first created a Sort column (after creating some dates) with this formula :

``````[Days Since].Filter(Date <= thisRow.Date).Count()
``````

And what it does is take the whole table (`[Days Since]`) and count the number of dates in the table where the dates are less or equal to `thisRow.Date` (So it creates an `order` from the “smaller” date to the “bigger” one) .

I use this field to actually sort the table (meaning that, when or if you add a row to the table the table will automatically reorder itself, depending on the date you add, following that sorting )

Then, in the 3rd field, to calculate the number of days since the “previous” date, I added this formula :

``````If(
thisRow.Sort = 1,
"",
thisRow.Date - [Days Since].Date.Sort().Nth(thisRow.Sort - 1)
)
``````

And what it does is :

• If : the value in `thisRow.Sort` equal `1`, it’ll leave the value blank (`""`) …
As it is the very 1st row in the table, there’s no previous row, so there’s no way to calculate the number of days since the previous row .

• Else : it does `thisRow.Date` minus `[Days Since].Date.Sort().Nth(thisRow.Sort - 1)`

And `[Days Since].Date.Sort().Nth(thisRow.Sort - 1)` being :

the sorted list of dates in the table (`[Days Since].Date.Sort()`) from which we only need the `Nth()` date …
And as we’re looking for the previous row, `Nth()` here needs to be equal to : `thisRow.Sort - 1` I’ve also added a 4th field called `Days Until Date + 1` (just in case ) which works in a similar way to `Days Since Date - 1` with few exception :

The condition for the `If()` to return `True` is this time :

``````thisRow.Sort = [Days Since].Sort.Max()
``````

to return a blank value (`""`) in the very last row of the table (as there would be no next date).

And `[Days Since].Sort.Max()` is the `Max()` value in the whole list of number from the field `Sort` For the `Else` part of the `If()` :

``````[Days Since].Date.Sort().Nth(thisRow.Sort + 1) - thisRow.Date
``````

This time, you’ll to subtract `thisRow.Date` from the next date (relative to thisRow) which is “bigger” than `thisRow.Date` Note that because to do this kind of math sorting is important (as to get the correct number of days, we need to subtract the value of a date from another date with a relative row position to that date in the table… and vice-versa) if you change it, this could lead to some error(s) but you should be able to correct them by adapting the formulas … So lol, just be careful with the way you sort your table I hope this helps 2 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.