Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,940 Topics
  • 18,233 Replies
5940 Topics
B
BllfngrNew Participant
posted in Formulas
How to return a formula result into a number field

Hello,I have a Lookup field called "Last Update" linked to a Last Modified Time field. I have created a formula that return minutes of difference between my "Last Update" field and the actual datetime.I would like to return this value into another field that is not a formula (maybe i'll use a number or a single line field).Can someone help me to do this ?Thank you !

83
B
J
jRich_AudioNew Participant
asked in Formulas
IF Field Contains User(s) then Replace User Name with Initials Help

Hi All,I have a Calendar table within a base that we use to assign calendar events to Airtable users. We also share the calendar link with everyone so the calendar will populate on their iPhone calendar and such.  Because of this, we want to show what event is assigned to which user in the calendar event ID (labeled "Event" in the screenshot below).  We currently do this with a concatenate formula but it will show each users full name, which makes the Event ID very long.  Current Event formula is as follows and works fine: CONCATENATE(IF({Tentative?}=1,"(t) ",""),Project, " ",{Service Call}," ", RMA," ",Title, " ", {User (Assigned To)})In short, I can not find a way in which to swap out each users name for their initials in the form of custom text (not just taking the first and last initial from the users name since two users have the same initials "JR").  I am looking for some sort of formula to insert into my existing {User (Assigned To)} spot in the for

273
J
C
CunwalkerNew Participant
asked in Formulas
Compare 4 Cells in a row and Return Column name with the highest score

Can someone help me out.  I'm want Airtable to return the "Column name" after he compares 4 adjacent cells in the same row. I expected to be something like...if(@A>@B AND(@A>@C) AND(@A>@D), "A")if(@B>@A AND(@B>@C) AND(@B>@D), "B")if(@C>@A AND(@C>@B) AND(@C>@D), "C")if(@D>@A AND(@D>@B) AND(@D>@C), "A") Thanks in advance!

50
C
J
Jesper_HolmstroKnown Participant
asked in Formulas
Dynamic date formula

Hey guysI have a brain drain problem with how I should set up a dynamic date formula, I have 2 tables in a base, one for odd weeks and one for even weeks. They both just contain 5 records, one for each workday. I have separate fields where the current date is shown, current weekday, current week number and if the current week is odd or even (it shows 0 for even and 1 for even). Now to the tricky part.I want to show in a field in the even table (week 50), lets say today Wednesday the 14th of dec. I want todays date to be visible in that field, on Tuesday the 13th should be visible and on tomorrow the 15th and so on. And I also want if the dates in the odd table shows next weeks date, and then when it becomes an odd week the even table updates the dates to the week after the odd. Any of you geniuses have a solution in mind?

980
Andrey_Kovalev
C
CreateGoNew Participant
asked in Formulas
Manage Inventory prices Adjusted to Inflation

Hi airtable communicty 🙂Here is a small challenge I face.I created an inventory for an auto shop. How to show the last price added for the product? Example: Product AA - we bought for $10Latter with $30And after a couple of weeks price decreased to $20 Now I can make only average or max. Here is a screenshot how I made MAX value added for this product.I found a formula like LAST_MODIFIED_TIME() But I don't know how to use, to display last price of product addedThank you  

250
C
A
AUTORRARI_MAINInspiring
posted in Formulas
Assign the Previous Answer a Number

Hello Airtable friends,So I have finally sent out my customer surveys and now I find that I forgot to find a way to "score" the responses!?!So, I have a Customer Satisfaction survey that has several "3-option" answers along with several "5-Star" options. No I have collected their responses but don't know how to "score" them!What I'm looking for is "IF('previous questions name'=first answer,1,0)Am I at least headed in the right direction here??As always, THANKS!dg

21
pressGO_design
Ricardo11
Ricardo11Inspiring
posted in Formulas
Help with regular expression for REGEX_EXTRACT

I'm trying to detect overlaps between my records and display data from the respective other records they're overlapping with, but am struggling yet again with creating the right regular expression for the REGEX_EXTRACT. Currently I'm...Creating an identifier field that helps recognize overlap (where identifier is equal = overlap).Creating an data field that includes the identifier and additional data that I want to display.Roll-up the data fields related to the linked record. Look up the roll-up on each record.Apply a REGEX_EXTRACT to the roll-up to extract the data I want to display. IF(List,REGEX_EXTRACT( List&"", "(?:" & SUBSTITUTE(Identifier,".", "\\.") & ")([^,]*)" ) ) Result:When record A & B overlap, it correctly shows the data of record A on record B and vice versa.When there's no overlap, it shows it's own Data and I cannot figure out how to avoid this. I'm trying to exclude itself from the list.To show multiple overlaps, I th

316
Ricardo11
J
Javier_Plaza_GoParticipating Frequently
posted in Formulas
Is it possible to combine a chechkbox on a formula field????

Hi, Im struggling to improve my data base and i ve found something that i don´t even know if its possible to be done. I have a formula field which shows a check , made with IF function, depending on the conditions of other fields. But the issue is that sometimes, i need to show a check also when those conditions are not fulfilled, i mean to edit it manually. The thing is that only happens on some records that fulfill a determined condition, so my idea is to look for that condition on the corresponding field, then allow the manual edition, and then count those records in the summary count.Is that possible?Thank you very much in advance.   

10
J
M
michalus88New Participant
posted in Formulas
filterFormula: how can I refer to relation field ?

My data record structure{    "id": "rec6CtSsmlVJe48Uj",    "createdTime": "2022-12-07T11:55:53.000Z",    "fields": {        "VOTES": 5,        "ID": 2,        "CONTENT": "....",        "AUTHOR": {            "id": "...",            "email": "test@",            "name": "test"        }    }}How can I use AUTHOR.id I wan filter by ID and AUTHOR.id in one queryID=1, ID=2 and AUTHOR.id=5How can i write it?

10
M
Y
YargJayNew Participant
posted in Formulas
Searching Base for any records that clash

Hi there!I'm building a booking system for a Go Kart hire company. I need to be able to detect if a new booking I am making will clash with other bookings.I have a date field "Date" and multi-select "Race Time" field (with races or selections every 10 mins starting at 9:00am up until 9:00pm). It needs to be multi-select to avoid mis-spellings.I would need an automation or formula that lets me know if any bookings are clashing based on both the Date and Race Times. I have also considered Make.com (Integromat) but I can't quite get the Airtable functions right it seems.It would also need to be able to indicate which record the booking is clashing with.Is there any way to do this?

31
kuovonne
P
plyskeInspiring
posted in Formulas
Help improving table

Hello everyone! I've made this Table: The idea with the table is to show different kind of tasks, sorted by Week and Day. Right now when a task is completed, I will click on the 'Næste gang?' field and choose whether the task should be listed in the daily or weekly view. If it's a daily task, I will choose 'Daglig' and for weekly tasks I will choose 'Ugentlig'. However when I do that, this happens: Instead of the task being re-created in the right view, it's being added in an Empty view. I therefore manually have to add it to the right view by clicking in the 'Hyppighed' field and select either Weekly or Daily. What I want here is the task to automatically be added to the right view. Also I really want to just be able to click in a check box when the task has been made and then Airtable knows which view the task belongs to instead of have to choose the right one in 'Næste gang?'Are the 2 above improvements possible to do? 

2713
P
S
Sara11Inspiring
asked in Formulas
How can I do Inner Join?

Hi, I'm trying to do an inner join (find elements in common between 2 columns).How can I do that here?I saw the functions Concatenate and Arrayjoin, but I didn't find anything about Inner join, maybe I'm looking in the wrong place. Is it possible to do it? Thank you😊 

690
S
jasonstewart
jasonstewartNew Participant
asked in Formulas
REGEX_REPLACE using expressions in Replacement

My end goal is to calculate the Flesch–Kincaid readability of a given text field ("Copy (Required)"). In order to do that, I need to determine how many syllables are in that text.Syllabification is somewhat of a subjective thing, but the approach that I've chosen is to use a series of nested REGEX_REPLACE functions inside a formula field based on this approach from Guilherme D. Garcia. The resulting formula looks like this: REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( {Copy (Required)}, "([aeiou])", "\\1-" ), "-$", "" ), "-([bcdfghjklmnpqrstvxz]$)", "\\1" ), "-(n|r|st)(t|n|d|f)", "\\1-\\2" ), "([aeiou])-s([tpnml])", "\\1s-\\2" )  I know that Airtable’s REGEX functions are implemented using the RE2 regular expression library but I can't seem to get the syntax correct for the "replacement" string. For refe

1060
TheTimeSavingCo
M
MarioDParticipating Frequently
asked in Formulas
Calculations on comma separated values

I have a zap that pushes 3 cells with comma-separated values, the first one being the part or service names, for example [trip, labour] the second cell is the quantities [1, 3.5] and the third one the prices [60, 120] to our invoices table.How can I extract that information and create a record in another table (line items) every time  a new record is added to the Invoices table.

120
M
R
randolf-alegreNew Participant
posted in Formulas
Filter record by reference ID in Airtable API

I have this relational feature of 2 tables:Table 1 (material) and Table 2 (material attributes)Now, Material Table has one or many material attributes (Table 2).I've been stuck with this feature where I can filter all the records belongs to Material ID (1234567)I checked the datatype of the reference ID and its an array. Now how can I filter all the material attributes that belongs to the specific Material?I tested all the helper functions of FilterByFormula and the response is not correct. It returns a mixture of IDs that does not belong to the given material ID.Thanks for the help!!

151
TheTimeSavingCo
A
Anthony_RizziNew Participant
posted in Formulas
Today()

when does the date actually change when using today() function? i would think midnight est for my acct but it seems to be around 7pm of the previous day as evidence by the email I received from my automation/workflow...thoughts here?

41
kuovonne
M
Michael_LeverInspiring
posted in Formulas
CONCATENATE - blank space between

Scenario:CONCATENATE(Town, "-", Address, "-", {No / unit})displays for example Town-Address-100Question:: I would like the display to appear as Towm Address 100.  How do I enter a blank space between the field items rather than a -  ?tia  

11
M
A
AaronThomasNew Participant
posted in Formulas
Average Cost Per Lead In Interface?

I run a digital marketing company and I'm trying to have an element in an interface that reports the Average Cost Per Lead. For this I need to divide my total amount spend on paid ads with the amount of leads generated. Is there a way to do this?

551
M
M
mattnorrisNew Participant
posted in Formulas
IF or SWITCH Function for multi-range values

I am building a savings calculator, and I'm using Airtable to store a Rate Table, and also to do the formula based calculations needed for the project. Here's what I want to do: Have a formula that takes value x  and checks if it is in a range of a and b in a specific row. Once it identifies what row, and therefore what range it is in, it needs to show/equal value y. Here is what I have so far: (But it is giving me an error)SWITCH( {Inputed debit swipe}, >= 10000, <= 20000, 2.75/100 >= 20001, <= 30000, 2.75/100 >= 30001, <= 40000, 2.75/100 >= 40001, <= 50000, 2.75/100 >= 50001, <= 60000, 2.75/100 >= 60001, <= 70000, 2.75/100 >= 70001, <= 80000, 2.75/100 >= 80001, <= 90000, 2.75/100 >= 90001, <= 150000, 2.75/100 >= 150001, <= 250000, 2.75/100 >= 250001, <= 500000, 2.75/100 >= 500001, <= 750000, 2.75/100 >= 750001, <= 1000000, 2.75/100

691
M
A
Achim_BaurInspiring
asked in Formulas
Change commas to line breaks in a lookup field

I have a lookup field called “Jobs” that returns fields from a linked record. In my case these are job descriptions. It shows them as a comma separated list, e.g. “CEO, director, manager…”. I would like to convert the commas into line-breaks with a formula. But SUBSTITUTE(Jobs,", “,”\n") returns an #ERROR! What am I doing wrong?

767
I
S
SondraWithAnONew Participant
posted in Formulas
date range forumla

Hi! I'm trying to create a field of active/inactive based on a date range. I already have a field each for start date and end date, but can't figure out how to create the formula for the active field based on those dates. Any ideas?Thanks! Sondra.

31
C
J
JustineCNew Participant
posted in Formulas
Sum up values if value is the same

Hi, I'd like to sum up the values from the "Weitghted results" column, when "Technology"=Social Media, or when "Technology"=BlockchainI have made some researches but can't find to get it correctly. I've used this formula but it doesn't work: INT(IF({Technology (from 🇺🇸 Questions)}="Social Media", SUM({🇺🇸 Questions copy},"")))Would you please be able to help me?Many thanks

532
J
P
plyskeInspiring
posted in Formulas
Keep Record setting when formula runs

Hello everyone! I have this table:The issue right now is that when I in 'Næste gang?' selects an option, fx. 'Daily' or 'Weekly', the Record will update and get a new deadline based on my choise (daily or weekly). However the Record also looses all it's data, fx. Assignee and the 'How often' all the way to the right (let me show you):  How do I change this, so that the Record will appear in the right Group when updated? 

42
P
K
Karim11New Participant
posted in Formulas
Formula Based on Monthly Income

Hi guys, im trying to do an automation that performs the following: “If Salesperson A’s monthly revenue is below the target, then do X”  However, I can’t find a way to automatically reflect the revenue of the month based on the month I’m in. I can do Rollup to generate the total revenue,  but not revenue for that specific month. How can I solve this? 

41
TheTimeSavingCo
R
Ready_RichmanNew Participant
asked in Formulas
VSC Code Formatting for Formulas?

For folks that write their formulas in VSC (or Sublime Text or any other code editor), what syntax auto-coloring do y'all use? There's nothing specific to Airtable's syntax, so I'm wondering what folks have been to be most pleasing and/or function the best? 

202
kuovonne

Badge winners

Show all badges
Terms & Conditions