# 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.