Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,228 Replies
5939 Topics
V
v1_oh_n0Participating Frequently
posted in Formulas
Combining Two Short Phrase Cells

Hello! I have a form configured for my clients to fill out that asks in two cells for first and last names. In the table view is it possible either through a function or formula to have a cell that combines the names together to easily sort and identify my client list? Thank you.

52
V
R
RashaKnown Participant
posted in Formulas
Rollup aggregation formula to grab most recent checkbox status

In an Interactions table, I’m using a checkbox field to mark specific interactions 7-day follow-up. In a different table, I’d like to see if the most recent interaction for a contact has 7-day follow-up checked. MAX(values) works for dates but returns zero here. Thanks for the help!

42
R
Scott_Gardner1
Scott_Gardner1Known Participant
posted in Formulas
Formula question - if statement and concatenate

Question: I need the ability to have an open text field show up called Text in my field IF the Item Description is Blank. So it would look like, CORPORATE REQUEST: ‘TEXT’. IF( {ITEM DESCRIPTION}=BLANK(),‘CORPORATE REQUEST’, CONCATENATE( {ITEM DESCRIPTION}, IF( {ASSET TYPE}, " - " & {ASSET TYPE}), IF( {COPY TYPE}, ": " & {COPY TYPE}), IF( {MEDIA TYPE}, ": " & {MEDIA TYPE}) ) ) Thanks!

21
Ben_Young1
D
Dana_SelzerInspiring
posted in Formulas
If/then formula that uses the value from a single cell

I need to write a formula that uses a specific cell of data as a multiplier: IF({BoM Category}=“3.1-Infusion Ingredients”,({Formula %}*SPECIFIC CELL OF DATA),IF({BoM Category}=“4-Product Ingredients”,{Formula %},0)) Do I need to create an automation to do this? If so, what are the steps?

82
D
B
bederNew Participant
posted in Formulas
If Statement Formulas

Hi there! I am a little stumped on creating these 2 formulas with a few different layers. All of my Commuity requests are about Iff statements- they hurt my brain, but I love them! If an If statement is not the best approach, please let me know :slightly_smiling_face: Formula 1 Display the word “Block” if a status is blocked, and if it does not say “Block” it shows the date/time/type of the webinar. What am I doing wrong here? If({Status})='Block','Block', DATETIME_FORMAT(SET_TIMEZONE({Start Date/Time}, 'America/Los_Angeles'),'MM/DD/YY h:mm A PT')&"-"&{Type}) Second Formula The second one works as is below but I want to add a second webinar type that would return the “Sponsor” name field in the results. If the webinar type is Sponsor OR Sponsored Survey, display date-sponsor-topic, ELSE display “date-webinar type-topic” IF({Webinar Type}='Sponsor',DATETIME_FORMAT(Date, "MM-DD-YYYY")&" - "&Sponsor&" - "&Topic, DATETIME_FORMAT(Date, "MM-DD-YYYY")&" - "&amp

82
B
C
Chris_WrayNew Participant
posted in Formulas
Count the instances of text in a comma separated entry

I’m trying to count the number of female attendees in a training session - I’ve managed to get them into a text string (eg: ,Female,Female, ) but can’t count the number of times the word ‘Female’ appears. I just can’t work out the formula to give me a number - help !

223
T
C
Carrie_MoyParticipating Frequently
posted in Formulas
Tracking Work with Lookup Records

I’m not quite sure how to do this but feel like there is a way in AirTable so I wanted to ask… I have a Base with three different tables filled with people. In Table C, I have a list of teachers that I have linked to a school leader, Table B, and in Table A, I have a list of District People that are linked to Teachers in Table C. Records are created in each table based on specific conditions. How can I help my team track their work? For example, if they go to Table A and lookup all the teachers associated with a district record on Monday and then a teacher that should be linked with the district record gets added on Tuesday, how can my team when they check on Wednesday distinguish between the teachers they saw on Monday verse the Teacher needing to be added on Tuesday? Hope this makes sense.

00
C
J
jcas_sparkNew Participant
posted in Formulas
Making due dates work days using DATEADD?

Hi! I want to have due dates as work days only, but the formula below gives 5 days before the start date, NOT 5 working days. IF({Start Date},DATEADD({Start Date},-5,‘days’)) Can someone please help me with the formula to make the due date of something 5 WORKING days before the start date? Thanks in advance.

692
Zack_S
D
Dreambound_ServNew Participant
asked in Formulas
Help on creating formula field that will add character to a word

Hi, I want to create a formula field that will add a “$” to those number that doesn’t have that. I tried using different codes but I still haven’t achieved it, can you help me? Thank you!

85
D
V
Valerian_LEBERTNew Participant
asked in Formulas
Emojis counting double in string formulas counts?

Hi, I amm having a strange issue with MID(X,1,5) returning more than 5 characters and LEFT(XX,10) returning more than 10 :slightly_smiling_face: Is this a known issue ? Any clue to make it work ? See attached screens Valérian LEBERT digi-studio digi-studio Digitalisez votre entreprise avec digi-studio

92
V
D
David_Brunt1New Participant
asked in Formulas
Calculating a date between 2 dates

I have 2 date fields and would like to calculate the midpoint between the two (as a date). I have tried a few the NGS bit not had any success. Any ideas?

922
D
A
Alexis_PirainaNew Participant
posted in Formulas
Automatically replace specific words with a number in a column

Hey, I have a column containing words from a enumeration: ONE TWO THREE FOUR FIVE Does any one know of a formula to automatically replace those words by their corresponding number? 1 2 3 4 5 Any help appreciated :slightly_smiling_face:

31
Alexey_Gusev
S
Shannon_WhiteNew Participant
posted in Formulas
Airtable forms and formuals

Hi! I have an Airtable form that generates a Chargify signup link. The sales team fills in the form fields and then it spits out a signup link for the customer showing the products, quantities, pricing, and then allows them to fill out their billing information. For some reason all of a sudden the link seems to be fractured. If anything additional is added beyond the base subscription, when the link is clicked it does not show those add ons. However, when you copy and paste the link into a new browser tab it does. Here is the formula being used: IF({Watermarking quota},IF({AUD?}=“AU/NZ”,{Signup Link with Coupon and Seats and Tracks and DS}&“&components[”&{Watermarking Component ID AU}&“][allocated_quantity]=”&{Watermarking quota},{Signup Link with Coupon and Seats and Tracks and DS}&“&components[”&{Watermarking Component ID US}&“][allocated_quantity]=”&{Watermarking quota}),{Signup Link with Coupon and Seats and Tracks and DS}) Here is an example

155
S
S
Still-LearningInspiring
posted in Formulas
How to use API quety for look into multiple colum values

Friends how cam get the row values which are matches with colA values using api query Eg, i wamt the values of field11,field51,field61 For single i use this formula filterByFormula={colA}=“field11”

02
S
L
Lorenzo_RAMNew Participant
posted in Formulas
Formula field that also takes input

I need two dynamic fields, one for EUR currency and one for USD currency. What I want to do is when I input on one of them it automatically convert the value and set on the other. How can I do this with or without formula ?

73
ScottWorld
Jess_Helmstette
Jess_HelmstetteKnown Participant
asked in Formulas
SUBSTITUTE error when value doesn't exist

Hello! I have an issue with a SUBSTITUTE formula I am using. I started with the following formula: SUBSTITUTE( SUBSTITUTE( {Email Body} & "", "{Appraisal Contingency Date}",{Appraisal Contingency Date} & ""), "{Conditional Approval Due Date}",{Conditional Approval Due Date} & "") It returns the following: Appraisal Contingency Expires: 2022-09-20T00:00:00.000Z Conditional Loan Approval Due: 2022-09-02T00:00:00.000Z Since the dates were not formatted properly, I updated the formula to the following: SUBSTITUTE( SUBSTITUTE( {Email Body} & "", "{Appraisal Contingency Date}",DATETIME_FORMAT({Appraisal Contingency Date},"ddd, MM/DD/YY") & ""), "{Conditional Approval Due Date}",DATETIME_FORMAT({Conditional Approval Due Date},"ddd, MM/DD/YY") & "") It returns the following: Appraisal Contingency Expires: Tue, 09/20/22 Conditional Loan Approval Due: Fri, 09/02/22 All seemed well, BUT, when there is a record that does

206
Jess_Helmstette
D
Dennis_PetrouKnown Participant
asked in Formulas
Help with linked records / formula

Hello. This seems like it should be an easy solve but I cannot for the life of me figure it out. I have a table in a base that automatically receives responses from a Typeform survey. It puts the email address (manually entered by person taking the survey) as the main record, and the responses follow in different columns. There is another table in this base where the main record is an employee name, and then we have Department, and 3 columns of 3 different email addresses (there are potentially 3 different ones for each employee, or 2 or 3 can be the same). I have linked the email base to the response base, and as the responses come in, I use a lookup to select the employee and pull in their department. I am trying to get this part to happen automatically. So, when an entry comes in, if that email address matches any of the three emails in the other table, it will bring that employee name in. Hopefully this makes sense and someone has a solution… Thanks!

222
D
R
Rishi_GoutamNew Participant
posted in Formulas
DATEADD but exclude Sundays

Hi, I would like to add days to a start date but exclude Sundays. So if I have a Start Date, I want to estimate the End Date by adding some number of days to the Start Date while excluding days that fall on Sunday. So if I have a Start Date of September 1 and add 7 days, I want to end up with September 9 rather than September 8 as I want to skip over the Sunday in the 7 days. My formula is below: Estimated End Date = IF({Start Date}, DATEADD( {Start Date}, ROUNDUP({Scope}/1000,0), ‘days’), BLANK())

191
M
V
Vito_KlootsNew Participant
asked in Formulas
Create date with timezone from week or date

Hello there! I have made an text field where you can enter a date with a “/” or can input only a week. Only problem is now that because the year is in a seperate column it is hard to get a full date when the record doesn’t have a / and get a full date when the record only has an week. Also the timezone in airtable is american by default but i need european.

61
TheTimeSavingCo
J
Jose_CKnown Participant
asked in Formulas
Formula to store progress

I have a formula to store progress on a time period. CONCATENATE(ROUND({closed tasks}/{Total tasks}*100,0),“%”) There is another field called “deadline” What i am trying to solve is to avoid an update of the formula after deadline date, in order to “snapshot” how was the progresssion until deadline date. I tried with an IF NOW() > deadline but then the field is blank and nothing is saved. Some idea? Many thanks in advance

92
J
Scott_Gardner1
Scott_Gardner1Known Participant
posted in Formulas
If formula question

Question: I have this formula to pull the latest revision in the Submittal revisions for art work. IF({SUBMITTAL: REVISIONS},SUBSTITUTE( RIGHT(‘’&{SUBMITTAL: REVISIONS},1+ LEN(‘’&{SUBMITTAL: REVISIONS})-FIND(‘https:’,‘’&{SUBMITTAL: REVISIONS},LEN(‘’&{SUBMITTAL: REVISIONS})-133) ),‘)’,‘’ )) It’s an attachment field called Submittal Revisions. I have another attachment field called Previous Stage with an approved attachment file - always filled in with a file. That said, sometimes the SUBMITTAL: REVISIONS attachment filed is empty and doesn’t have any data to pull from. Can we add in that if the SUBMITTAL: REVISIONS is empty, it pulls from the PREVIOUS STAGE file? Thank you!

62
Scott_Gardner1
Kirsten_Kern
Kirsten_KernInspiring
asked in Formulas
Formula for removing a comma from an email that has been extracted from a rollup

How do I remove a comma from in front of an email that has been extracted from a rollup. I am very new to this formula writing. I have a rollup field that has two emails in it. I have extracted the first email by itself. However, the second email alway has a comma in front of it. I need to remove that comma. How do I do that? Can I do it by altering this formula in any way? REGEX_REPLACE({Email Roll Up from Educator}, “([^,])(.)”, “$2”) Again, thank you for any help on this.

252
Kirsten_Kern
S
shelby_jacksonNew Participant
posted in Formulas
How to concatenate formula with date without messy time string

Hey AT community, I can’t figure out why this formula isn’t working – AT is telling me it’s an invalid formula: CONCATENATE({Full Name copy}, IF(NOT({Full Name copy}), Org), " - $", {Received Amount}, " - ",DATETIME_FORMAT({Date Received}, “D/M/YYYY”)) I want the formula to be [name or org] - {$received amount} - date received Ex. John Smith - $5,000 - 09/02/2022 Thank you!

391
Kamille_Parks11
M
Marianna_Kerpp1New Participant
asked in Formulas
How to calculate a "winning streak"?

Hello! I would like to calculate a ‘winning streak’. Each row has the result of a game with a column for wins/losses, as well as a column for the dates of the games. I would like to calculate a winning streak. In Excel, I would do this with a self-referencing formula: IF({Win/Loss}=“Win”,{Win/Loss}+1,0), How might I do this in Airtable? Thanks! Here’s a sample table: https://airtable.com/invite/l?inviteId=invEhMSt3pM1PhMUr&inviteToken=022c846bffe765f7904cc3124920d800b61f879f25b7d41b5030bfabcc64b7bb

133
N
O
Osnat_MelnikovNew Participant
posted in Formulas
DATEADD returning the added field date instead of the result

Hi, Struggling with DATEADD formula, trying to get an hours bank result from: Date of purchase and days left to use. for some reason, I keep getting the first date I entered- The date of purchase. Appreciate the advice.

93
kuovonne

Badge winners

Show all badges
Terms & Conditions