Days between two rows of data

[Stasia_Jahadi]

Hi everyone,

Is there a way to calculate the days between records? For example - I’m tracking workouts and want to promote daily activities by tracking streaks. Therefore, I need to calculate the days between each record entry. This was my google sheets formula =IFERROR(DATEDIF(A1,A2,“d”),"") but I’m not seeing any way to compare two dates from two different rows of data.
Thanks for your help, I’m new to Airtable and after some searching I could only find solutions that leveraged data from the same row vs. 2 individual rows.

Anastasia

[Justin_Barrett]

Welcome to the community, @Stasia_Jahadi! :grinning_face_with_big_eyes: Airtable records have no inherent knowledge of each other. It’s one of the key differences between a database and a spreadsheet. There are ways of doing the type of calculation that you want, but they either involve messy multi-table connections (which I don’t recommend) or scripts (which are free if you don’t mind running them manually, or which require your base to be in a Pro-plan workspace or higher if you want something automated). That said, my brain just went off on a tangent, and I can think of one possible method to pull this off via an automation that doesn’t require scripting, but designing the trigger may be tricky.

[Databaser]

Also a possibility: creating a linked field to the same table, linking the previous workout the current one, adding a lookup field to lookup the created date of that previous workout and adding a formula field that calculates the differents in days between the 2. Then you can also use the options at the bottom (range, histogram, min, max, …).

[Justin_Barrett]

Good points. I was somehow picturing a running-total kind of situation, which leads to circular references quickly, or else I would have recommended that. The automation that I’m envisioning could still be used to help build those links between records so that it doesn’t need to be done manually.

[Databaser]

Now you’ve made me curious about that automation Justin :slightly_smiling_face:

[Stasia_Jahadi]

Wow thanks everyone for your suggestions. I’m looking for a very hands-off automation. I was using airtable as a go between from Strava and Notion. I tried google sheets but you can’t import a value created by a formula and since airtable puts the formula in the header - it leaves the cells clean for importing I thought I’d see if there was a way. Basically what I’m hearing is the same problem I have trying to do this in Notion. I can’t compare two individual date records to calculate a value in days. I like where your head was going Justin - I was thinking some kind of rollup view of multiple records count but there could be gaps in dates which makes logic necessary to determine if the days are consecutive. Let me know if this helps spring some inspiration. I totally appreciate all the feedback.

[Justin_Barrett]

I’m tied up for the rest of the day, but I’ll try to flesh out the idea for you tomorrow. In short, though, it would be about 99% hands-off once built. All that you’d need to do is create a new record. The automation would do the rest.

[Justin_Barrett]

@Stasia_Jahadi You already have a {Date} field (guessing that’s the date field name) in your table, so here’s what you need to add:

• A {Previous Record} link field that points to the same table.
• A {Previous Day} lookup field that retrieves the value from the {Date} field of the linked record.
• A {Difference} formula field using this formula:
  IF(AND({Previous Day}, Date), DATETIME_DIFF(Date, {Previous Day}, "days"))
• A checkbox field. I named mine {Current}. Check the box in this field only on your most recent record.

Now you can set up the automation:

• The trigger will be “When a record is created”.
• You’ll then add three actions:

  • "Find records" - This will search the table (I named mine [Progress]) for records with a check in the {Current}. This should only return a single record.
    

  • "Update record" - Set this to update the found record from the previous step to remove the check mark. Just don’t put anything in the setup field and Airtable will clear it.
    

  • "Update record" - Set this to update the triggering record to add a link to the single found record from above into the {Previous Record} link field, and add a check in the {Current} field (enter a 1 in the setup field for this).
    

Hide all of the fields except for {Difference} (unless you want to see them), then turn on the automation. When you create a new record, the link will be made from the newest one to the previous one, and the check mark will move. Once you enter a date into the {Date}, the difference between that date and the previous record’s date will be shown in the formula field.

Here’s how my test looks after adding a few new records (I didn’t add anything to the primary field, which is why the links all say “Unnamed record”):

[kuovonne]

I use a slightly different method.

I have a record that hangs around waiting to be filled with data. When I finish the data entry, an automation runs that creates a new linked record. The automation fills in both the “previous” link in the new record, and the “next” link in the record that I just finished.

This gives me a few benefits:

• I can do calculations in both directions (next/previous)
• there is always a record ready and waiting for data entry (I have a filtered view on my phone that always shows only this one record)
• there are no additional fields