Calculating days between dates?

[Jono_Bacon]

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!

[Christiaan_Huizer]

hi @Jono_Bacon

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

[Pch]

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