Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,940 Topics
  • 18,233 Replies
5940 Topics
A
anaclaraNew Participant
asked in Formulas
How can I gather different values in one cell?

I've done a few things in Airtable, but have lots to learn still. I'm creating a database with the contacts across all my organization. We would like to keep track of individuals who work in several organizations, so we have one field for ORG 1 and another for ORG 2. We would like to see all the ORGs a person is involved in at the field ALL ORGS. (See image)How can I create a formula to include ORG 1 and ORG 2 values in ALL ORGs field?Thank you very much!

90
kuovonne
J
JackieyuNew Participant
asked in Formulas
Formula Help

Hi everyone. I’m looking to create a IF statement where if date B exists, then it will calculate the working days between from date A to date B. However if date B does not exists then it will calculate how many days it’s been from date a based on the real time current date. The if statements that I have been trying to create does not work with multiple formulas embedded.

40
kuovonne
A
arrugaParticipating Frequently
posted in Formulas
Case insensitive in linked fields

Hi thereIs it possible to make a linked field case insensitive?If in a source table I have "Blue", and in another table a record is created with "BLUE" in a linked field (response from a user form entry, which I have no control over... this is a simple example, I can't do a select field question in the form, it must be user text), I would like the "BLUE" entry turn into the "Blue" that already exists in the source table, rather than creating another "BLUE" record in the source table. How to best go about it?Many thanks!

332
A
J
jsargentNew Participant
posted in Formulas
Total a full table and percentage complete

Is it possible to total a full table and calculate the % complete?Example:InfoClientsInvite AcceptedTotal CountPercent AcceptedInfoClient AClient BClient CClient DIf I have invited all 4 clients to an event but only 2 have confirmed, I have 50% done. Can Airtable calculate this?I'm looking for it to trigger a completed task in another tab once it hits 100% - is that possible?

41
JonathanBowen
I
Ian_TranNew Participant
asked in Formulas
Lookup multiple value from other table

Hello guys, is there a way to archive this scenario with Airtable. Let’s say I have a field with multiple ids/skus (kind of an array) and a table with ids/sku and name column. I need to somehow lookup the name of multiple ids in a field and display them like the picture above.

262
I
L
Luke_NicholNew Participant
posted in Formulas
Client Fee Formula

Hi, good people of the Airtable community! I am a therapist who sees clients. Each client will be charged a fee in Great British Pounds (£). If attended, a client will be charged £50. If a client does not attend or provides a late cancellation, their fee will be £35. I have a Google Calendar synced table. I have added one tick box column titled ‘Did Not Attend?’. I want a formula that records £50 if attended (default state) but changes the fee to £35 if I tick the box that denotes a did not attend/late cancellation status. A little problem to be solved that I hope someone might enjoy.

32
L
S
Sebastian_BustaNew Participant
posted in Formulas
Is it possible to use a formula to compare columns of a table?

I’m trying to use function IF , but it seems not to work with entire columns, only with specific values or texts. I need to compare like I do in sheets, e.i : IF(A2 = D2, “Valid”, “Not Valid”). I don’t know how to use formulas with cells or if it’s even possible. I have another question regardings lookup, is there some way to use like a vlookup with fields that arent the key ones?

1031
ScottWorld
L
lalosmsNew Participant
asked in Formulas
Removing decimals in column which has both numbers and text

Hi all, New to formulas in Airtable and need a bit of help. My Formula is designed to complete an equation from two different columns, and if the result returns as an error in a cell, then to replace it with a message. The formula can be seen here. IF( ISERROR({TSINGLES} / ({LM}-{TSINGLES})),“No Sales”,{TSINGLES} / ({LM}-{TSINGLES})) Ultimately, as the column now contains numbers and also text, I can’t implement formatting in the dropdown at the top of the column. Could I add something to my formula to ensure that the result from the calculation are returned without any decimals? Here is a screenshot of what we’re working with - not brilliant.

132
L
R
RicardoCoraiNew Participant
posted in Formulas
Use totals in formulas

The status bar at the bottom of each table shows the total of each field. So we have X records and X sum. What I need is to use those values in formulas. How can I call the total records of the table 1? How can I call the sum of a field in the table 2? Etc. Could you help me with that?

61
kuovonne
Thierry_Gattlen
Thierry_GattlenInspiring
posted in Formulas
Arrayjoin separator doesn't apply on linked records

Hello to the AT community, I need to list some related records with arrayjoin(linked field,"\n") in a formula field. Unfortunately, this is not working on linked records as it is on lookup records. (See picture below). I would d like to avoid creating 28+ additional lookup fields in the table (this is a person table linked with 28 project tasks table) to get the proper list with carriage return. I will use this in automatic SendGrid mail blocks. Does anyone knows how to arrajoin linked records instead of lookup records. Thanks a lot. Thierry

322
M
A
asja_fejzicNew Participant
posted in Formulas
ONLY detect duplicates in a column

I tried the already existing topic about duplicates but was unable to find what I am looking for. So basically, I have a list of applicants listed chronologically according to the day they applied, and each of the applicants has their own unique ID number. What I need is to be able to see automatically if someone with the same ID applies…I don’t need to remove duplicates from the list, nor to regroup them or anything. I just need them to be marked somehow in case the same ID occurs, so that I can now immediately if someone is applying again or for the first time. I google sheets I use the =countif(A:A,A1)>1 …is there an equivalent in airtable? Thanks and sorry if this was already asked.

5213
Justin_Barrett
C
Christian_HeinsNew Participant
posted in Formulas
Function TODAY() not always giving the exact output when used with compare symbols "<" or ">" to compare dates

Hi! I had the following situation when using the following formula to compare dates and determine the current row as the current date. the first part state if the count I less or equal to 1, that’s the current row, then if the count is above 1 and now is greater than the start date and now is previous to next date or next date is empty, that’s the current row IF({Count}<=1, “current”, IF(AND({Count}>1, Now() > {Start date}, OR(Now() < {Next date}, {Next date}=“”)),“current”)) This would give the correct output. But when I tried using instead of NOW() > TODAY() IF({Count}<=1, “current”, IF(AND({Count}>1, {TODAY()} > {Start date}, OR(TODAY() < {Next date}, {Next date}=“”)),“current”)) It would throw a completely different result, completely shift on dates. WHY?

43
Andrey_Kovalev
_
_cupofchinoNew Participant
posted in Formulas
Question about nested if(and()) function

Hi, I’m trying to figure out why I’m experiencing issues with my nested IF(AND()) function on Airtable. When I limit the fields in AND() to just two conditions, it works, but anything else and Airtable won’t let me use the formula at all. Please advise! Thank you. What works IF(AND({Person}= “1”, {A}= “Yes”), “Approved”, IF(AND({Person}= “2”, {B}= “Yes”), “Approved”, IF(AND({Person}= “3”, {C}= “Yes”), “Approved”, IF(AND({Person}= “4”, {D}= “Yes”), “Approved”, IF({Quote} = “0.00” , “Temporary approval”, “Unapproved”))))) What doesn’t IF(AND({Person}= “1”, {A}= “Yes”), “Approved”, IF(AND({Person}= “2”, {B}= “Yes”), “Approved”, IF(AND({Person}= “3”, {C}= “Yes”), “Approved”, IF(AND({Person}= “4”, {D}= “Yes”), “Approved”, IF(AND({Person}= “5”, {E}= “Yes”, {F}= “Yes”), “Approved”, IF(AND({Person}= “6”, {E}= “Yes”, {F}= “Yes”, {G}= “Yes”), “Approved”, IF({Quote} = “0.00” , “Temporary approval”, “Unapproved” )))))))

64
_
Scott_Gardner1
Scott_Gardner1Known Participant
posted in Formulas
Reverse Quarter and Date Formula

Hello! Is these a way to reverse this? To show 2023/Q1 instead of Q1/2023. IF({Your Date Field}, “Q” & DATETIME_FORMAT({Your Date Field},‘Q/YYYY’) )

72
Scott_Gardner1
J
Joanna_ParkerInspiring
asked in Formulas
Conditional DATETIMEDIFF

Hi everyone, Hitting a bit of a wall with some formulas and wondering if anyone has a more elegant solution to this… I’m working on a new base that records start and end dates of some work that we do. I then have a datetimediff that calculates the number of days that the record was running (see mocked up example below). We compile reports annually, and I regularly need to show days of work that only fall within the previous calendar year. At the moment I calculate this by pulling off a CSV, adjusting the start and end dates to only cover the range and do a DATEDIF in Excel. Can anyone think of an automated/conditional way of only counting days that fall within a certain calendar year? As you can see above, reporting for 2021 would involve projects 2 and 3, and reporting for 2022 would involve projects 1 and 3. Any thoughts/ideas?

61
J
Soly
SolyBrainy
asked in Formulas
How to adjust Date Formatting?

Hello, I have formatted the date but the hour displayed shows 1 hour less. How can I display the exact time in hours and minutes in the field {date formatted} ? The formula I use is : DATETIME_FORMAT({Time slots},‘DD-MM-YYYY à HH:mm’) How can we solve this ? :thinking:

132
Soly
W
Wendy_YelsikInspiring
asked in Formulas
IF Function Help, Nested

Looking for help creating a formula to output one of these answers based on a person’s meeting start date and the completed date of the program. Meeting Status field (formula): • Done • ToDO • NA • Upcoming My fields are- • Meeting Start Date – • Program Date • Current Date Field If a person has a meeting start date of 11/1/2022 and the program date is 12/1/2022 then the field should output “NA” If a program is scheduled for a future date, then the output should be “upcoming”- If a person hasn’t completed the program, the output should be “ToDo” If a person completed the program, then the output should be “done” I appreciate any help.

287
W
W
Wendy_YelsikInspiring
asked in Formulas
1st of the month date formula

Is there a date formula to calculate the 1st of the month based on a date? For example, I want to calculate 11/1/2022 for 10/15/22, 10/5,22, etc. = Wendy

600
Kamille_Parks11
Scott_Gardner1
Scott_Gardner1Known Participant
posted in Formulas
Add a number to a date formula help?

Hello- Trying add a field that can add days to my formula, any thoughts? IF({DC Arrival Date}=BLANK(), BLANK(), IF(OR({Production Type} = ‘Domestic’,{Production Type} = ‘Puzzles’), DATETIME_FORMAT(DATEADD({DC Arrival Date},-56,‘Days’),‘MM/DD/YYYY’), DATETIME_FORMAT(DATEADD({DC Arrival Date},-98,‘Days’),‘MM/DD/YYYY’)))

122
Scott_Gardner1
M
Mark_HudsonNew Participant
posted in Formulas
Show Top "X" Scores in a grouped view

Apologies, very new to Airtable. I have a view based on a table that has players weekly scores. The view is group by Player Name and Sorted By Score (Desc). In each group I would like to add a rank column so then I add an additional filter to filter by Top x scores per player. I tried autonumber but would need autonumber to reset for each player grouping. Any ideas? Thank you Mark

662
M
H
Hector_ZelayaNew Participant
posted in Formulas
Not able to to edit or compute on formula field

Hello, im trying to introduce a new formula field but for some reason it is just highlighted in gray and won’t let me type anything on the formula field.

31
Karlstens
Sean_Lake1
Sean_Lake1Inspiring
posted in Formulas
@alias.com extraction

Hey All, I’m sure this is somewhere and I’ve searched but I’ve not seen an explanation to certain “things” within the REGEX_EXTRACT field: REGEX_REPLACE(Email, “(\w+)\.(\w+)@.+”, “$1 $2”) for example, I get what it’s doing, but not sure of those internal things, like \w+) etc. What I need is really simple and I was thinking I’d use the regex to extract just the @email.com part. IF({Attendee Email - for Invites}, REGEX_EXTRACT({Attendee Email - for Invites},UNKNOWN PART OF FORMULA),“BLANK FIELD”)

102
B
A
Audrey_DSMDENew Participant
posted in Formulas
Formula not working with data from Make (Integromat)

Hi, I have the folowwing formula: IF({Type Relation} = "Mr","ok","pas ok"), it’s just a test because in my make automation i’ve a problem, and now I understand why, because in my airtable base the formula doesn’t work correctly in a first try. When my automation write “Mr” in my “Type Relation” field, the formula don’t see “Mr” in the first try. If you see the next screen, you can see that when I remove the final “r” of “Mr”, the formula is always to “pas ok”, it make sens and no problem here. When I rewrite the “r” to have “Mr” (What I had in the beginning), the formula is updated correctly to “ok”. Have you ever seen that ?

61
A
A
Ashley_CainNew Participant
posted in Formulas
Help with Concatenate field with a condition

Hi! I need to create a formula that concatenates the First Name and Last Name columns. I could do that part easily enough. But I also need it to look to see if there’s a value in the Preferred Name field and if that field has a value in it, to substitute that for the First Name. Is this possible? How would I do this? Here’s a screenshot, for reference:

113
X
S
Shelly_JansenNew Participant
posted in Formulas
New to AirTable - Need some help

I have an Inventory base that was not created by me, but I’m the owner now. I have a Materials Checked In table, that I need to sum up the qty received by PO and description. I tried using the Roll up, but it pulls all of the items by description, which is not helpful when I need to see items received by PO. This way I can see the Ordered Qty, Received Qty, then have an Open balance remaining per PO.

11
N

Badge winners

Show all badges
Terms & Conditions