Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,228 Replies
5939 Topics
Scott_Gardner1
Scott_Gardner1Known Participant
posted in Formulas
Formula for rich text

Hello! I need help trying to make these comma separated fields into a bullet rich text format and the extra comma’s to be removed at the end if they are present, help please! 55 Cards, 1 Rulebook with 5 Mini-games, 1 Tin, , , , , , , , , , , , Looking for: 55 Cards 1 Rulebook with 5 Mini-games 1 Tin Thank you as always!

17210
Scott_Gardner1
A
Anais_KaliNew Participant
posted in Formulas
Manage booking event and waiting list

Hello, I search on the forum but I don’t find responses. Here is my request : I organise an event with many courses. To do this, I created a form in airtable to allow people to book a place to the course they choose. But I want to limit the number of person for a class and create a waiting list. For exemple : In my form the participant can chose between class A, class B or class C. There is only 6 places for each class. So when six persons are registered for a class, I want to note the 7th person and more as “waiting list”. In my table “Book a course”, I have a field with the quantity of places for a course (“6”), another field with the “autonumber field” to rank person when booked, another field with a formula : when the autonumber of a person is taller than quantity of places for a course, put the label “waiting list” and when this number is smaller or equal, put hte label “booked”. This way, with “autonumber field” it’s OK if I have only one class on my table but I don’t find a way

202
M
D
Diane_MartinezParticipating Frequently
asked in Formulas
Difference formula

I’m hoping the community can help me out. I think that my formula is a bit off. Here’s the logic I’m trying to do in the Difference field: If Baseline Value is empty, leave Difference empty If Baseline Value is 0, use the value in Target value If Baseline Value is NOT empty, calculate Target value minus Baseline value divided by Baseline value so I can get the percentage difference As you can see, if Baseline Value is 0 or empty, there’s an error My current formula: IF({Baseline} != BLANK(), IF({Baseline value} != BLANK(), (INT({Target value}-{Baseline value})/{Baseline value}), IF({Baseline value} = 0, INT({Target value}), BLANK() ) )

65
Ben_Young1
A
Arnie_SingerNew Participant
posted in Formulas
CREATED_TIME() in Israel not working for me

I’m trying to set up a date/time field. I’m in Israel and I’d like the timezone to be NYC. My first problem is that when I use CREATED_TIME() to get the current time, the time is off by 3 hours (behind). Then the Set_Timezone function is not calculating properly either.

142
A
V
Vito_KlootsNew Participant
asked in Formulas
Making a date from a week and year number

Hello! Does anyone know how you can transform a week number and a year number to a date?

131
M
J
Jason_Friedlan2Participating Frequently
posted in Formulas
Find Duplicate Values with in a Rollup

I need the formula to display the duplicate values found in a rollup. I can’t do ArrayUnique because I actually want to know what the duplicate ones are and there are too many combinations for me to code it using an if statement for each result. Thanks!

1227
kuovonne
Jess_Helmstette
Jess_HelmstetteKnown Participant
asked in Formulas
Replace/Substitute Multi Select Field Values with Values from Another Field

Hello! I am new to Airtable and have been chugging along until now lol. I have done hours of research and received advice from a few people but I am still very much stuck. I have a multi select field and I am trying to write a formula that takes the options in that field and replaces/substitutes them with values from other fields. So let’s say my multi select field contains the following values: Buyer Agent Closing Attorney Closing Attorney Assistant I want a formula that looks at each value and replaces each one with an email address from a lookup field. ‘Buyer’ would be {Buyer Email} ‘Agent’ would be {Agent Email} ‘Closing Attorney’ would be {Closing Attorney Email}’ ‘Closing Attorney Assistant’ would be {Closing Attorney Assistant Email} The email addresses would need to have commas separating them. And if there is no email address in any of the lookup fields it would just ignore it. Please help!

437
Jess_Helmstette
R
RPANew Participant
asked in Formulas
Comparing Select fields in one table

Hi! Is it possible to compare single and multi-select fields across columns in one table? Would a formula be able to do this? I have a single select field listing the launch month for content. Then there are 2 lookup fields with the launch months for connected content (each with multiple months selected). Ideally, I want to compare the single select field to the 2 lookup fields and output anything that is after the single select field month. Would these fields need to be dates rather than single or multi-select fields for this to work? If so, is there a way for multiple dates to exist in one field? Or is there another way to do this? Thank you!

174
R
A
Alba_Machado2Known Participant
asked in Formulas
Eliminating Commas Between Rollup Field Items

Hi All, I’m in the process of creating a catalog of related books that I can hopefully email out to customers, and, so far, that’s involved creating an Entries rollup field in my Catalogs table that pulls from my Books table using the ARRAYUNIQUE(values) aggregation formula. I did as much formatting as possible in the Books table, adding caps with UPPER() and spaces with \n, but I can’t figure out how to eliminate the comma after each rollup field item. I tried this: SUBSTITUTE (ARRAYUNIQUE (values), “,”, " + ") But I kept getting “Sorry, there was a problem saving this field. Please check your formula text.” I’ll add a screenshot here so you can see what I’m talking about and why I’d want to eliminate the comma . . . Thanks in advance for your suggestions!

8316
Ben_Young1
C
Clare_FitzpatriInspiring
asked in Formulas
Change date format in formula field

Hi I would like to change the date format returned in my formula field - I’ve tried using datetime-format but keep getting an error DATEADD({Date Invoice Raised}, 30,‘day,’) DATETIME_FORMAT(‘DD-MMMM-YYYY’) thanks Clare

131
Ben_Young1
C
Clare_FitzpatriInspiring
asked in Formulas
How can I calculate invoice due date based on invoice issued date + 30 days

Hi I would like to add a formula field which calculates when an invoice is due for payment. I have an ‘invoice issued’ date field and would like to use that to calculate +30 days. this is where I have got to but its returning an error DATEADD({Date Invoice Raised} +30,‘day’) many thanks Clare

152
C
J
Jacek_GreberNew Participant
asked in Formulas
Move task on the monday when it's saturday or sunday?

Hey Guys, first time here. Airtable support forwarded me here. I need your help. I have a such formula: IF( {routine type}=‘daily’, DATEADD({task date}, 1, ‘days’), IF( {routine type}=‘weekly’, DATEADD({task date}, 7, ‘days’), IF( {routine type}=‘bi-weekly’, DATEADD({task date}, 14, ‘days’), IF( {routine type}=‘monthly’, DATEADD({task date}, 30, ‘days’) ) ) ) ) and I would like to combine it with something like this: IF( WEEKDAY({next date})=0, DATEADD({next date}, 1, ‘days’), IF( WEEKDAY({next date})=6, DATEADD({next date}, 2, ‘days’), {next date} ) ) Can you help me, please? The first formula creates new task depending on the routine type. Weekly routine, monthly routine, etc. The second formula should move the task to the Monday if the first formula lands task on the Saturday or Sunday. Appreciate your help. Jack

165
J
K
Karola_MartinezKnown Participant
asked in Formulas
Formula IF with emoji result

Hello community I was wondering if you could help me. I want to make a formula that shows me the emoji “ :white_check_mark: ” if the names and emails are in the contact fields, otherwise it shows me “ :x: ” but I don’t know what I’m doing wrong in my formula. Could you help me?

236
K
Scott_Gardner1
Scott_Gardner1Known Participant
posted in Formulas
Deadline date formula

Hello- Any help on not showing negative days here, just show if the number is negative, it says zero? IF({DESIGN DUE DATE}=“”,‘’, CONCATENATE(DATETIME_DIFF({DESIGN DUE DATE}, {TODAY’S DATE},‘days’), ’ Days ')) Thank you!

11
Ben_Young1
R
RyanHNew Participant
posted in Formulas
Complex Formula Question

Hi everyone! This is my first post in the Airtable community, and I have quite the ask. I need to create a formula that is able to look across four different fields in the same table, and check if those fields have one of two particular values (Let’s say the possible values are ‘Complete’ and ‘None’). If all four fields have either ‘Complete’ or ‘None’ at the same time in the same row, I want the formula to print ‘Yes’. If any of the four fields have a value other than ‘Complete’ or ‘None’, then the formula should NOT print ‘Yes’. I have tried using a combination of IF, OR, & AND statements, but this has proven to be quite the formula, and I am not the most experienced coder. My reason for this request is that I want to be able to filter off the value this formula creates, that way I can see all of the tasks in my base that are in progress. I hope this makes sense, any input is appreciated!!!

74
R
K
Keshara_MooreParticipating Frequently
asked in Formulas
Formula that Dynamically Parses a Value

Hello all! I am trying to compile a formula that dynamically spits out a value based on the values in 2 other static data fields, the first being an Additional Authors Count that counts the number of authors that exist on a record and the second field being a deliverable field. The end goal of this formula is to compute a number amount that a freelancer should get paid based on completely specific deliverables and taking into account the number of authors on said project they worked on. For example: If a freelancer works on a project deliverable of content calls and they do so on a project with an author count of 2 additional authors, I want the formula to be able to spit out a number based on those two external values and the static values that live in said formula. I’ve been able to get this far, but now need to somehow add in the deliverable field condition so that if the deliverable is Content Calls and Additional Author is 1 then add 1000 to the existing multi author amount (which

197
K
S
Sal_SuissaInspiring
posted in Formulas
Extract Text From Between Two Set Characters

Hi! This one seems quite easy, but having a tough time to loop LEFT and RIGHT to make it work … I have the following examples 5-DBR_4.JPG 5-GD-WHI_1.jpg In both instances, I want to extract the text between the “_” and the “.JPG”, so “4” and “1” respectively. This is to populate an {Position} field. What is the right formula to use for this?

928
Alexey_Gusev
B
Benson_G_MunyanNew Participant
posted in Formulas
Extracting String Between ""

Hello! I have seen similar posts but cannot seem to get them to work. I have the following string of text: New voicemail in mailbox 1 from “AWESOME DUDE” <555-555-5555> I am trying to extract AWESOME DUDE and 555-555-5555 to seperate cells. I have tried the solution suggested here but cannot seem to get it to function, as it returns a -1. Any assistance would be greatly appreciated.

11
TheTimeSavingCo
M
mcampelloNew Participant
posted in Formulas
Sum values from items on a multiple selector

Hey people, please help me here. I am building a new house and I need to understand pricing for all items we need to buy. To clarify, here is an example: Here I need the fiels "M2 from Walls"to show a sum of the size of all walls on the multiple select column. My objective is for example: When I add a new price for wall painting’s square meter I can multiply this price to all walls on my multiple select field. So, If a square meter of wallpaper is $10 And I have a room with 20 sqm and another with 30 sqm It would give me 50sqm * $10 So the sum of that room’s cost would be $50 Makes sense? Can you guys help me please? Thanks a lot

61
kuovonne
Scott_Gardner1
Scott_Gardner1Known Participant
posted in Formulas
Attachment field with multiples images. Formula?

Hello- Looking for a solution please, I need a formula or a zapier suggestion - for if I have a attachment field with 4 versions of a design, and I want to auto send the last version added say version 4 to a new field that only show the latest revision - possible? Thank you!

43
Alexey_Gusev
S
Shawn_HolmesNew Participant
posted in Formulas
One form prefills, the other doesn't

Hey, everyone! I’m having a really tough time tracking down the issue here. I have two forms, both of which have a field that I’d like prefilled. The facts are: 2 tables: (Companies, Vehicles). Entries in Vehicles are linked to a Company. Each company has a gallery view, filtered to only show vehicles in that company. The primary unique field in Vehicles is the VIN. 2 forms, 1 for each of 2 unique companies, and the field names are the same on each form. The VIN field on the form is linked to the gallery of the respective company, thereby constraining to only those company’s vehicle’s VINs to be selectable. When I fire up form 1 for company 1, no problem! The VIN is pre-selected without issue and works exactly as described in link_records_form_demo - airtable. No issues whatsoever. When I fire up form 2 for company 2, the VIN is never preselected, no matter what I seem to do. I’ve tried the troubleshooting steps listed here: Prefill not working - #2 by ScottWorld Change your primar

71
Zack_S
Cody_Winchester
Cody_WinchesterInspiring
asked in Formulas
DATETIME_PARSE() not respecting timezone

After through the documentation on DATETIME_PARSE() I’m not seeing the timezone respected when used in conjunction with either SET_LOCALE() or SET_TIMEZONE() for conversion of a string field containing date and time information. What am I missing here:

91
Cody_Winchester
W
Whitney_WilsonNew Participant
posted in Formulas
Arrayjoin(ArrayCompact) + IF statement

I am analyzing data from an evaluation form where all field are mandatory. When reporting them using Page Designer I want to omit any entries where the person has typed n/a (with any combination of capital or lowercase letters). My first thought was that there needs to be an IF statement in there some where, but where? Currently I have: ARRAYJOIN(ARRAYCOMPACT(value), “/n/n”)

82
W
A
Ambroise_DhenaiInspiring
asked in Formulas
Formula to convert E164 to US phone number

I have the following phone number +33661291700, do you know a formula that converts it to a US phone number? (e.g: (415) 555-2671) Thank you

406
A
L
Luca_WesselsNew Participant
posted in Formulas
Split text cell into various records for linked lookup formula

Hello All, I am trying to convert a concatenated text cell into various records, split by the semi-column. Ideally, an automation would somehow split the cell contents so I can return the linked lookup value for both Optimisers, and Double Storey and their relevant costs in another lookup column. Is there a way I can split the contents into separate records for the pricing data column?

61
TheTimeSavingCo

Badge winners

Show all badges
Terms & Conditions