Calculate value of cell in one record based on cell in another record?

[Peter_Schindler]

How can I achieve the following from Excel in Airtable? In a time table of “Events”, the StartTime of an Event begins when the EndTime of the previous Event ends:

                   A                    B             C

Record/Row: StartTime Duration EndTime
1 14:00:00 00:30 14:30:00
2 14:30:00 00:45 15:15:00

In Formulas in Excel

1 14:00:00 00:30 =A1+B1
2 C1 00:45 =A2+B2

I would be grateful to anyone who can help me figure out how to do this.

Thank you!
Peter

[Zollie]

I don’t completely understand your current problem, so responding to your title with some concept explanations. Hope these help.

Excel vs Airtable Code

Excel formulas exist within a cell, whereas Airtable formulas apply to an entire column. Both applications also have general purpose languages available for anything formulas struggle to describe (Visual Basic for Excel and JavaScript for Airtable).

Referencing Other Fields

A formula field/column can reference other fields like this:

Sum    Number 1    Number 2
3      1           2
4      2           2

Sum Formula: Sum( {Number 1}, {Number 2})

Referencing Other Records in the Same Base

Formulas

You can use a combination of formulas, linked records, and lookup fields to get the appropriate data from records in the same base.

Scripting Block

For more granular adjustments, there’s the JavaScript based Scripting Block. These are run on command (by clicking a button).

[Peter_Schindler]

Hello Zollie,

many thanks for your reply. After I posted this question, I realised it was too complicated. So, I posted another one with the title " [Calculation to track % Change in a Table]. Here it is again:

I’ve created a Table. I add a record ever day to track the number of occurrences such as: Visitors to an establishment.

Date Day Visits % Change
23.3. Mo 27 NA
25.3. Tu 28 = (28/27)-1 (in formula in Excel (C2/C1) - 1
26.3. We 30 = (30/28)-1 (in formula in Excel (C3/C2) - 1
27.3 Th 35 = (35/30)-1 (in formula in Excel (C4/C3) - 1
etc

Where the % Change Column (or Field in Airtable speak) is, of course, calculated by a Formula.

How can I achieve this with Airtable? With Scripting Block?

I would be grateful for your thoughts on this.

Peter

[Zollie]

Thanks for the explanation - that makes sense now.

Yeah, the Scripting Block would be the only way to accomplish that. The reason being, Airtable formulas aren’t really meant to reference other rows’ data (outside of linked records, but that’s not relevant here).

Checkout the work offered section if you need someone to write some JavaScript for you. But honestly, the Scripting Block seems like overkill to me for such a simple problem. Plus it needs to be run each time you want to log more ‘% Change’ values to the table. Since you already have a working Excel workflow, maybe it’d be easier to just do the calculations in excel then copy them back over to Airtable as necessary.

[Peter_Schindler]

Hello Zollie, thanks for the additional thoughts on this.
Peter