Welcome to the community, @Mark_Fredrick!
This is very doable. First off, here’s how I suggest setting up some of the fields you listed.
For the {Start Time}
formula, I recommend something like this:
IF({Item#} = 1, DATETIME_PARSE("START_TIME", "START_TIME_FORMAT"), {Previous End})
For the {Item#}
comparison, change the 1
to whatever number you use for your first item in the show. For START_TIME
and START_TIME_FORMAT
, build a string that represents the show’s start time, and a matching format string using this formatting guide as reference:
Just be aware that unless you also include a timezone indicator in this formula, the time will be calculated relative to GMT, not your local timezone.
There are other ways to specify the start time for the first item, but I’ll stick with this version for this example.
Instead of a button field—which would give you a button in every record—I suggest just clicking the “Run” button in a Scripting app, though a button field could trigger the same script without any issues.
While writing a script to do this, I found that having {End Time}
as a normal date field and letting the script calculate its value worked better. The only way for the script to pull an accurate value from {End Time}
as a formula field is to let that formula refresh after the {Previous End}
is changed, because {Previous End}
drives {Start Time}
, and {Start Time}
drives {End Time}
. Waiting for each record to update before calculating the next record adds a massive delay to the whole process. It’s faster to let the script handle the duration calculation and batch-update the records.
Here’s the test table that I started with:

I put the item numbers out of order in the main view on purpose, to demonstrate how the {Previous Item}
field from your example isn’t necessary. When using Airtable’s sorting in a view, and then collecting records in the script specifically from that view, Airtable keeps the records in view order, so there’s no need to add that extra field.
Here’s my sorted view before running the script:

And after the script runs:

Here’s the script:
const eventTable = base.getTable("Events")
const sortedView = eventTable.getView("Sorted")
let query = await sortedView.selectRecordsAsync()
let updates = []
let endTime = null
// Cycle through the records
for (let record of query.records) {
let update = {
id: record.id,
fields: {}
}
// If this isn't the first record, set the start time from the previous end time
if (record.name != "1") {
update.fields["Previous End"] = endTime
}
// Calculate the end time
let startTime = endTime === null ? new Date(record.getCellValue("Start Time")) : endTime
let duration = record.getCellValue("Duration")
endTime = new Date(startTime.getTime() + (duration * 1000))
update.fields["End Time"] = endTime
updates.push(update)
}
// Update all records
while (updates.length > 0) {
await eventTable.updateRecordsAsync(updates.slice(0, 50))
updates = updates.slice(50)
}
If the field names you listed above (which I used for my test) are accurate to your base, you should only need to change the first two lines to set the variables for your table and view.