Start time & end time for event

Welcome to the community @Diletta_D_Arrigo!

You should be able to use the following formula to do exactly what you’re looking for:

DATEADD({Start Formula}, Duration, 'seconds')

This will add the duration to the start formula to calculate the end time. Do keep an eye out that the formatting between the different columns is the same. You may get wonky-looking results if one is on 12 vs. 24-hour time or GMT vs. your local time zone.

If this answers your question, please consider marking it as “solution”. If not, I’m happy to work with you further. Thanks!

Thanks for this response. Would there then be a way to make the next event (record) start at the time the last event ended? I’d like to enter a start time for the first event, and have all subsequent events’ start times determined by the duration of the events prior to them. Thanks!

Welcome to the community, @Viveca_Gardiner! :grinning_face_with_big_eyes: I helped another user with a very similar use case recently. While this isn’t possible using formulas, it can be done using a script. Here’s what I came up with…

Thank you so much, but I’m afraid I can’t follow this at all. I am new to Airtable and don’t know anything about coding. So if the answer is that I can’t do it, I can’t do it. Which is horribly less convenient than a spreadsheet for this key need as I am trying to plan a day’s itinerary for an online event in which each event immediately follows the one before.

But this seems particularly frustrating since the example includes a column called “Previous End,” which implies that it is populating with the previous event’s end time. If I could do that, my problems would be solved with just the formula you provided for start time:

IF({Item#} = 1, DATETIME_PARSE("START_TIME", "START_TIME_FORMAT"), {Previous End})

If I used this, can you please tell me what would replace

DATETIME_PARSE(“START_TIME”, “START_TIME_FORMAT”)
if I wanted the first time to show as just

12:30 pm

From your links, I thought it would be 

DATETIME_PARSE("???", “LT”)

But I couldn’t figure out what to put for the question marks.

Thank you!

Viveca

IF({Item#} = 1, DATETIME_PARSE("START_TIME", "START_TIME_FORMAT"), {Previous End})

From your links, I thought it would be 

You don’t need to. If you use my final script from that thread, the only thing you would need to modify in the script are the field names. However, I can tweak the script so that you choose your own fields from the scripting app. Let me fiddle with that for a few minutes and I’ll get back with the update.

With the new script that I’ll provide, this formula won’t even come into play.

Be back in a few…

@Viveca_Gardiner Below is a version of the script that gives you a simple interface where you can choose the table and view where these event records are set, as well as the three fields needed for the script to do its job (start time, duration, and end time). As long as the first record in that view has a defined start time, the rest will be calculated by the script.

I’ll probably publish this to the Airtable marketplace, but that’ll take some time. In the meantime, here’s a how to add this to your own base:

• Install the Scripting app
• Close the popup dialog once the app opens and delete the short sample script in the app window
• Copy the full script below (scroll to see the whole thing) and paste it into the Scripting app, then click “Finish editing”.
• You should see a UI and some brief instructions. Use the UI to select your event table, a view in that table where you’ve set the event order, and the three fields mentioned above.
• Once these are chosen, the Run button will be enabled, and you can run the script.

And here’s the script:

// Allow user to select the table and view to use, and which fields will be affected
const config = input.config({
    title: "Set Sequential Event Times",
    description: `Update a collection of events that occur in sequence so that each new event starts when the previous event ends.

The first record in the selected view **must** have a set start time to begin the calculations.
Any other record with a set start time will use that as the start time for that record.
All other records will have the start time calculated based on the end time of the previous record.`,
    items: [
        input.config.table("eventsTable", {
            label: "Events table",
            description: "The table containing the event records"
        }),
        input.config.view("eventsOrderedView", {
            label: "Event order view",
            description: "The view where you have set the order for the event records",
            parentTable: "eventsTable"
        }),
        input.config.field("startTimeField", {
            label: "Start time field",
            description: "The field to contain the event start time",
            parentTable: "eventsTable"
        }),
        input.config.field("durationField", {
            label: "Duration field",
            description: "The field containing the event duration",
            parentTable: "eventsTable"
        }),
        input.config.field("endTimeField", {
            label: "End time field",
            description: "The field to contain the event end time",
            parentTable: "eventsTable"
        })
    ]
});

async function main (records) {
    // Begin processing
    let updates = []
    let endTime = null

    // Cycle through the records
    for (let record of records) {
        
        let update = {
            id: record.id,
            fields: {}
        }
        let startTime = record.getCellValue(config.startTimeField)
        let start = null
        
        // If this record has a fixed time, use it
        if (startTime !== null) {
            start = new Date(startTime)
        // Otherwise use the previous end time
        } else {
            start = endTime
            update.fields[config.startTimeField.name] = endTime        
        }
        // Calculate the end time
        let duration = record.getCellValue(config.durationField)
        endTime = new Date(start.getTime() + (duration * 1000))
        update.fields[config.endTimeField.name] = endTime
        updates.push(update)
    }

    // Update all records
    while (updates.length > 0) {
        await config.eventsTable.updateRecordsAsync(updates.slice(0, 50))
        updates = updates.slice(50)
    }

}

// If no time is set for the first record, alert the user
let query = await config.eventsOrderedView.selectRecordsAsync();
if (query.records[0].getCellValue(config.startTimeField) !== null)
    // Kick off the process
    await main(query.records);
else {
    output.clear();
    output.text(`❌ ERROR: The first record has no start time. Please set a specific start time for the first event and re-run the script.`)
}

// Allow user to select the table and view to use, and which fields will be affected
const config = input.config({
    title: "Set Sequential Event Times",
    description: `Update a collection of events that occur in sequence so that each new event starts when the previous event ends.

The first record in the selected view **must** have a set start time to begin the calculations.
Any other record with a set start time will use that as the start time for that record.
All other records will have the start time calculated based on the end time of the previous record.`,
    items: [
        input.config.table("eventsTable", {
            label: "Events table",
            description: "The table containing the event records"
        }),
        input.config.view("eventsOrderedView", {
            label: "Event order view",
            description: "The view where you have set the order for the event records",
            parentTable: "eventsTable"
        }),
        input.config.field("startTimeField", {
            label: "Start time field",
            description: "The field to contain the event start time",
            parentTable: "eventsTable"
        }),
        input.config.field("durationField", {
            label: "Duration field",
            description: "The field containing the event duration",
            parentTable: "eventsTable"
        }),
        input.config.field("endTimeField", {
            label: "End time field",
            description: "The field to contain the event end time",
            parentTable: "eventsTable"
        })
    ]
});

async function main (records) {
    // Begin processing
    let updates = []
    let endTime = null

    // Cycle through the records
    for (let record of records) {
        
        let update = {
            id: record.id,
            fields: {}
        }
        let startTime = record.getCellValue(config.startTimeField)
        let start = null
        
        // If this record has a fixed time, use it
        if (startTime !== null) {
            start = new Date(startTime)
        // Otherwise use the previous end time
        } else {
            start = endTime
            update.fields[config.startTimeField.name] = endTime        
        }
        // Calculate the end time
        let duration = record.getCellValue(config.durationField)
        endTime = new Date(start.getTime() + (duration * 1000))
        update.fields[config.endTimeField.name] = endTime
        updates.push(update)
    }

    // Update all records
    while (updates.length > 0) {
        await config.eventsTable.updateRecordsAsync(updates.slice(0, 50))
        updates = updates.slice(50)
    }

}

// If no time is set for the first record, alert the user
let query = await config.eventsOrderedView.selectRecordsAsync();
if (query.records[0].getCellValue(config.startTimeField) !== null)
    // Kick off the process
    await main(query.records);
else {
    output.clear();
    output.text(`❌ ERROR: The first record has no start time. Please set a specific start time for the first event and re-run the script.`)
}

WOW! I did even know there was a scripting app much less how to use it, and this worked great! I imagine a lot of people would find it useful.

It says the script is free through this month. What would I need to do to keep using it on this or other bases? Would I need to purchase it with my Airtable credit, or upgrade my account, or …?

Thank you so much for going above and beyond. I really wasn’t expecting any answer beyond “you can’t do that,” and now I can!

To clarify, the script itself will always be free, but the Scripting app is only free to use in bases that are in Free- and Plus-plan workspaces through the end of March. To continue using that app after that cutoff, you’ll need to upgrade your workspace to at least the Pro plan. Once that’s done, all bases in that upgraded workspace can use the Scripting app.

Hi all! I was also trying to do this type of time duration to create a Run of Show for a live event. I’m trying to figure this out and work with the script above but I’m not getting it :frowning:
I have the columns: Start Time (Date/time field), Duration (duration field), End Time (Date/time field)… but the end time doesn’t go down to the next row to use that as the start time.
Any help on this? Thanks! :slightly_smiling_face:

This is pretty much the same script as in the other thread that I just replied to, but this one has a more friendly UI so that you don’t need to manually change the code to enter your field names.

Please share a screenshot showing your table as it’s set up before running the script. Something tells me this will provide some useful clues to why it’s not working for you.

I know… was trying hard to figure out using the other script you had… it seemed to work. But only thing it doesn’t do is then put the end time from the top row to the bottom and cascade it down.
Here is the screenshot

Thank you Justin for helping on this!
Pam

In my original instructions, I mentioned that a fixed date/time in the “Start Time” field will always be accepted as-is, and not overridden by the previous record’s end time. What you need to do is erase the start times for everything except the first record. Re-run the script, and it will fill those other start times based on the duration calculations.

On a related note, I submitted my script above to Airtable for inclusion in the scripts marketplace back at the start of April, and I haven’t seen it appear there yet, so I’m not sure if it’s going to ever show up. At any rate, part of the submission was to include a video showing how it worked. Maybe this will make its intended use more clear:

I’ll try to keep this video alive as long as I can, though I’ll probably end up recording a new demo to put on my YouTube channel eventually.

oh wow - I’m a total idiot. Yes it totally worked once I deleted the old info from the other records below the first start time! Awesome!! THank you so much Justin!

In case anyone else finds this thread and not the one where the script originated, there’s now a new thread where I’m planning to post updates and address support questions going forward. Within a day or two, I hope to update that thread with a version that will work in an automation.