Days Difference between Previous Entry Date

Topic Labels: Formulas
2222 8
Showing results for 
Search instead for 
Did you mean: 
7 - App Architect
7 - App Architect

I’m tracking bicycle tire air pressure fills and my current setup include 3 main columns: Begin Date, End Date, and Duration. Duration is a DATETIME_DIFF function, calculating the days between air refills. The problem is, I have to copy each End Date to the subsequent row’s Begin Date every time. This is quick on the desktop, but on iOS it’s extra steps.

Ideally, I would have just one date column and the duration column would calculate the difference from the previous record. For example, in Google Sheets, the function would be able to easily calculate the difference between cells A1 and A2. I haven’t figured out how to do this in Airtable.

This article mentions how to "find the difference between a record’s date and today’s date.
Airtable Support | Calculate the difference between two dates I’m looking for a replacement for the “NOW()” formula to be the previous record.

Thank you!

8 Replies 8

All record positions in Airtable are arbitrary which is why there is no “A1, A2, A3, etc.” notion in Airtable like there is in Excel or Sheets.

Assuming you’re only logging one bike’s pressure, you could have a simple Automation create a new record with the “Begin date” prefilled with the last log date.

You could use a same-table linked record field to link to the previous record, then pull the date from the previous record through via a rollup field.

If you go this method and need to manually create the link, you should have a filtered view that shows only records with recent dates, then limiting the selection of records to the filtered view. This will make picking linked records easier. You may need to fiddle with how recent the dates need to be.

If you do not want to have to manually create the link, you can also use an automation to automatically create a new linked record whenever the air pressure is entered for a record. If you have multiple bikes, have the automation fill in the newly created record with the same bike.

I see.

I’ve never used an Airtable Automation before. I looked at a Support article but don’t quite understand it yet.

Creating an automation

Would you or someone else mind giving a step by step on how to accomplish this? Thank you!

I apologize but I don’t understand how any of that works. I’ve used Airtable for years but am still a beginner in its functionality. I would greatly appreciate a walkthrough.

Frankly, I don’t understand why this basic functionality is so complicated. I could do this in Google Sheets in a second. As I mentioned in my original post, Airtable offers the option to Calculate the difference between two dates, so I would think the logical next step is for Airtable devs to add a function to calculate according to the previous (above) or following (below) record.

Thanks for lending your ear.

Simple answer: Google Sheets makes spreadsheets, Airtable does not.

There are just some features common to spreadsheets that Airtable won’t ever and shouldn’t ever add, one of which is referencing the “above” record. The reason being is in Airtable, the order records appear is arbitrary; from a data architecture perspective, there is no such thing as a record being “above” or “below” another. You would get a wildly different answer to “what is above this record” depending on what View you’re looking at, which is not at all reliable if want a definitive field value, and would be a nightmare for everyone who uses Airtable data outside of Airtable.

Airtable devs would have to rebuild a lot of Airtable’s core behavior from the ground up to do what you’re asking, natively.

I genuinely appreciate the explanation of Airtable’s architecture. Having said that, in order for Airtable to gain mainstream traction, it should function better than a spreadsheet because it makes data more malleable. If I search for a tutorial on Excel or Google Sheets, I will find thousands of results. Apparently, I’m not alone in desiring a similar above/below relationship function that is simple to use.

I tried creating an Automation but failed. If Airtable’s support pages do not explain how to create this particular function, how are lay people supposed to figure this out? In the past when I’ve asked for help on this discussion forum, contributors would offer step by step instructions, but it seems like times have changed…

People usually do still offer in-depth instructions, nothing has changed. Kuovonne gave a pretty exact description on how to solve the issue manually; I understand you couldn’t get it to work, but you didn’t say where in the process you’re having trouble. There’s only so much we can help with if we don’t know what the issue is.

In my case, there seemed to be a misunderstanding of how Airtable is supposed to work which is why I didn’t explain the steps to the Automation-based solution i suggested until the basics were understood. There are multiple ways you could set up an Automation to accomplish this, the following two will result in creating a new record with the date prefilled:

Method 1
Trigger: When a record in “Pressure Fills” table meets the conditions: “{End Date} is not empty and {Fill Amount} is not empty”

^ the example trigger assumes that you only want to run the automation when the “previous” record has been filled out completely

Action Steps:

  1. Create a record:
    • Table = “Pressure Fills”
    • Field Values =
      • {Begin Date} = Trigger record’s field value for {End date}

^ to insert the value from the trigger record, click the blue plus button to the right

Method 2

This Automation setup will assist in the setup described by Kuovonne where you are linking a log record to the ‘previous’ record with a field assumed to be named {Previous Record}

Trigger: same as Method 1

Action Steps:

  1. Create a record:
    • Table = “Pressure Fills”
    • Field Values =
      • {Previous Record} = Trigger record’s record id

As Kuovonne mentioned, in this scenario the {Begin Date} field would be a Rollup-type field that pulls in the {Previous Record}'s {End Date} field

You didn’t clarify if you’re only logging records for one bike or for many bikes. If you are logging records for multiple bikes the above instructions will vary.

It already often does, depending on the use case (the use case is usually “I need a database, not a spreadsheet”)

Airtable is a database, not a spreadsheet. As such, it works differently from a spreadsheet. Airtable is better than a spreadsheet only when a database is the appropriate structure. Airtable is not better when a spreadsheet is needed. Plus, data in Airtable is actually less malleable than in a spreadsheet. In a spreadsheet, every cell can have a different data type and reference any other cell. In Airtable every cell in a column must have the same data type, and formula fields can only reference other cells in the same row.

There are many reasons why a question may not get a detailed answer. Most of the contributors on this forum freely volunteer their time and knowledge to answer questions, but they are under no obligation to do so.