Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,938 Topics
  • 18,225 Replies
5938 Topics
V
Vijay_chenKnown Participant
posted in Formulas
How do I use the ARRAYUNIQUE() for this two-line string

I want to get the unique value of the {Photos IDs} column, but ARRAYUNIQUE() doesn’t work, presumably because it has a newline symbol in it. What should I do

142
V
V
Vuk_RadicNew Participant
posted in Formulas
Check for duplicates before creating user name

I am completely out of my depth here, so I apologize for dumbing what I’m doing down. Also, talk to me like I’m a child when explaining what’s going on :grinning_face_with_big_eyes: I have built a simple website with Webflow and use Wized as the backend. Wized uses Airtable as the database. One of the functions of my website is that it gives the user a unique email address where they can send messages. I want this unique address to be created by Airtable. I’m assuming i use the first and last name tables with this formula {first} & {last} to get the first and last name into a new cell. Now i have two questions: how would i add to this first and last name the “@mydomain.com” part of the email? i know this is something super simple, but just can’t seem to find the right one more importantly, how would i get airtable to check the same column for duplicate email addresses before creating this one? and if it encounters a duplicate, get it to ad a number (1, 2, 3…) to the end of th

132
kuovonne
H
Harry_BattenParticipating Frequently
posted in Formulas
Sorting time earliest to latest

Hi, IS it possible to sort this list of times, earliest to latest? I can only sort A-Z and this is what it returns:

235
kuovonne
A
Andrew_DaviesInspiring
asked in Formulas
Removing special characters from text for a filename

Hi Anyone have any clever tips for removing special characters from text to make them filename firendly? For example < > { } ) ( etc. I could do it with a massive nested substitute formula but that seems clunky so hoping there is a cleverer way of doing it.

1322
A
J
J_W1Known Participant
asked in Formulas
Regex to split string into comma-separated tags

My datafile has a field with multiple tags that are joined like this: TagoneTagtwoTagthree Is there a formula possible, with help of regex, to put a comma between all lowercaseUppercase instances to seperate them?

544
Rupert_Hoffsch1
K
KoenSInspiring
asked in Formulas
How to do Partner field?

For a table with organizations I have a partner field that links back to the primary column itself. Suppose an organization has 10 partners and I put them in the Partner field, then all those organizations will get that one organization in their linked partner field. But I would like for them to get all the organizations in their partner field, ideally minus themselves. Thoughts on how to do that?

186
K
B
Benjamin_TonitaNew Participant
posted in Formulas
Is there a way to make a timer increment only when a field has a certain status?

I am trying to make a formula that tracks how long a record has a certain “Status”. If the status is “Open”, the timer starts. If the status is “Pending”, the timer stops, and stores the value that it was just before the field was switched to pending. I also want this timer to start incrementing again if the record is moved from “Pending” back to “Open” (so, not starting from zero, but starting from the paused time. I thought this should be fairly simple, but I’m not sure how to make it work. Thanks

105
Kamille_Parks11
A
Aaron_PrestonNew Participant
posted in Formulas
*Help* Formula for different hourly rate for multiple workers

Bit of a tricky one for me, im looking for a formula for this situation with a multiple select box: if ‘Jon’ selected =£18, if ‘Carl’ selected =£20, if ‘Jon’&‘Carl’ or ‘Carl&Jon’ selected £38 just a snippet of what im after, i have 7 opperatives on different rates, basically looking to then multiply by hours on site to get a final expenditure. Any help i would be greateful

43
A
P
Phil_VallenderNew Participant
posted in Formulas
IF formula based on percent not returning int values

Hello, I’m trying to do a fairly basic calculation that is conditional on a percentage field - if the percent is above a threshold, return a calculated number, if below return 0. My formula works with anything except zero for value 2. Here’s my formula: IF( (Potential*100) >= 490, {Estimated Points}*Potential, 0 ) Any help greatly appreciated.

208
Justin_Barrett
J
James_MasciovecNew Participant
posted in Formulas
Workday Formula Fridays

I am currently using a workday formula to calculate an end date based on a “Start Date and an Average Length of Completion” for a task. My issue is with single-day tasks, if the task begins on any day other than a Friday, the formula works. If it starts on a Friday, it won’t calculate as a single day. Formula is: DATEADD(WORKDAY({Start date}, {Length of Days}), -1, ‘days’) tl;dr: Monday-Thursday formula works and returns the correct end date. Friday start date calculate a Monday end date.

92
J
Z
Zion_BrockKnown Participant
asked in Formulas
DATETIME_DIFF sluggish to update

Not sure if this was covered before. I’m trying to use the datetime_diff function in a formula as a trigger to send out an email 5 minutes before the meeting starts. The problem is that this function is very sluggish to update. In other words it might be 10 or 15 minutes past the moment it should trigger before it refreshes and shows the correct difference in time. But by the time it updates it’s too late to send the email out. Is this a bug? Or is there a workaround? Thanks!

144
ScottWorld
B
Britt_MottolaParticipating Frequently
asked in Formulas
How do you create a comma separated list from linked fields?

I want to list each linked entry in the “facts copy” column as a separate bullet point in a bulleted list in another column. Here is the table: Here is the formula I tried to use unsuccessfully: REGEX_REPLACE(CONCATENATE({Facts copy}), “, “, “<br>⦁“) I am not a programmer so apologies for my lack of knowledge.

201
Kamille_Parks11
S
Smyth_CountyParticipating Frequently
asked in Formulas
Trying To Get Formula to Add Percentage

I know this is simple but I’m struggling for some reason. {Average Weight}/2000 * {Base Rate} + {Surcharge Calc} It adds the ‘Surcharge Calc’ which is a percentage just as a number. What I’m trying to accomplish: Average Weight = 10000, Base Rate = 15, Surchage Calc = 20% 10000/2000 X 15 + 20% = 90 I’m getting 75.20 Thanks

92
S
J
Josh_VaughnNew Participant
posted in Formulas
Formula? Summing Field Values Based on Checkbox True / False

Hi, First: Airtable is an incredibly useful program, both for business and personal use. It’s a lifesaver that I’ve been using for years. However … I have need for Formulas for the first time and having trouble getting my head around the syntax for this task. I need to conditionally add and output the sum of 3 Currency fields based on the status of the Checkboxes: IF the status of {$VALUE 1} is checked - output of the Formula field is $VALUE 01. Otherwise the output of the Formula field is 0. IF the status of {$VALUE 1} and {$VALUE 2} are checked - output of the Formula field is ($VALUE 01 + $VALUE 2). Otherwise the output of the Formula field remains $VALUE 1. IF the status of {$VALUE 1} and {$VALUE 2} and {$VALUE 3} are checked - output of the Formula field is ($VALUE 01 + $VALUE 2 + $VALUE 3). Otherwise the output of the Formula field remains $VALUE 1 + $VALUE 2). For this task the $VALUES will either be hit in sequence, or they won’t. ie: We will never have a case where we need to

403
TheTimeSavingCo
M
Melissa1Inspiring
asked in Formulas
If() stock calcul

Hello, I would like to set up a calculation to allow me to multiply a number according to conditions. IF(), but I don’t know how to go about it. Here is the request: My column: ( hide) Inventaire Composant sortant (Unique) corresponds to a set of products: ex: 1 table. "I could have called this ( hide) Inventaire Produit fini sortant (Unique) → This is what I take out of my stock but as a product as a whole (single). The question is for my column: Inventaire composant sortant ( Calcul pied), I would like according to conditions to be able to say if table with 2 feet, multiplies (( hide) Inventaire Composant sortant (Unique)) x 2 (# (hide)Pieds), if table with 3 feet, multiplies (( hide) Inventaire Composant sortant (Unique)) x 3 (# (hide)Pieds), if 4-foot table, multiplies (( hide) Inventaire Composant sortant (Unique)) x 4 (# (hide)Pieds), idem for if 1 feet and if Blank(), leave the field empty This would then allow me to subtract Inventaire composant sortant ( Calcul pied) from ( hi

76
M
L
Lev_KorotichNew Participant
posted in Formulas
Logical operators within the "IF" function

I have two values “PUt” and “SRt” With different values of these fields I need to output different “Profiles”. When I write a formula to compare these numbers, I get the wrong answer from the formula. Values: This is my formula: Logically, the formula should select “Profile 6” But the cell shows “Profile 5”.

12
L
T
Tristan_Clause1New Participant
asked in Formulas
Complete noob - looking for a sum!

Hi folks, Formulas aren’t my strong suit, but I’m hoping there’s a way I can do this. Essentially, I’m helping my school manage teacher assignments, so I’m building a simple database to help me organize and visualize this data. I have two tables: 1 for Courses and 1 for Teachers. I have them linked together such that if in the Courses Table I type in a teacher’s name, then that course will show up on the Teacher’s tab (and vice versa). In the Course tab each course has a numerical value assigned to it based on the number of times per week they meet. What I would like is a column that tabulates the total number of periods assigned to a teacher that is calculated based on the courses assigned to that teacher. Does anyone have any formulas that can do it? I was hoping a lookup field would be a sneaky shortcut, but no luck. See my attached screenshots (I’ve cut off the teacher names field for privacy’s sake):

132
T
C
Christyan_MatosParticipating Frequently
asked in Formulas
How to multiply the value returned by the IF Statement without showing an error

I’m using IF Statement with return numbers for two conditions: If it’s a car, one value, if it’s a motorcycle, another value. Everything is ok about that, however, when trying to multiply the presented result, it shows #ERROR!, how to solve it?

76
C
S
Scott_BelgardNew Participant
posted in Formulas
Multiple If Statements Based on Multiple Data Points

I have tried multiple Nested If statements to achieve this result: This is my ultimate goal: If “Date of Last Contact” is blank then return blank. If it is not, then if “Days Since Contact” is <3 then return “ :heavy_check_mark: Less Than 3 Days :heavy_check_mark: ” If it is not, then if “Days Since Contact” is <5 then return “ :warning: Almost More than 5 Days :warning: ” If it is not, then if “Days Since Contact” is >5 and Status Does Not = Revisit Later then “ :x: Greater Than 5 Days :x: ” If Status = “Revisit Later” and “Days Since Contact” is >30 then :x: Time To Revisit :x: If not then leave blank. IF({Date of Last Contact} = BLANK(),BLANK(),IF({Days Since Contact}<=3,“ :heavy_check_mark: Less Than 3 Days :heavy_check_mark: ”,IF({Days Since Contact}<=5,“ :warning: Almost More than 5 Days :warning: ”, IF(AND({Days Since Contact}>5,{Status}NOT(“Revisit Later”), “ :x: Greater Than 5 Days :x: ”, IF(AND({Status} = “Revisit Later”, {Days Since Cont

51
Kamille_Parks11
T
Tobin_PerryKnown Participant
posted in Formulas
Number of Wednesdays or (any other days of the week)

Is it possible to create a formula that tells you how many of a specific day of the week are in a month? Such as how many Wednesdays are in this month? I have a specific deliverable that needs to be done every Wednesday. I’d like a formula that would help me see how many of these are left in the month. Thanks!

11202
T
D
Darrell_MillerNew Participant
asked in Formulas
Remove #ERROR! when field is empty

I have three columns in my table. Lat/Long Combined - Example: 18.194608875237545, -63.08524490907154 Latitude - Example: 18.194608875237545 Longitude - Example: -63.08524490907154 The formula I’m using in the {Latitude} column is: IF( FIND(",", {Lat/Long Combined}), LEFT({Lat/Long Combined}, FIND(",", {Lat/Long Combined})-1 ), {Lat/Long Combined} ) The formula I’m using in the {Longitude} column is: LEFT( RIGHT({Lat/Long Combined}, LEN({Lat/Long Combined}) - LEN({Latitude}) - 2 ), FIND( ",", RIGHT({Lat/Long Combined}, LEN({Lat/Long Combined}) - LEN({Latitude}) - 2 ) & "," )-1 ) When there isn’t a value in the {Lat/Long Combined} column, the {Latitude} fields are blank, but the {Longitude} column is populating #ERROR! Any help would be much appreciated. Thank you

112
D
D
Dyanna_GarciaNew Participant
posted in Formulas
If and DateAdd statement giving me an error

Ok so I am trying to use an if statement to put in a date but then if it doesn’t meet the condition I want it to be blank. I keep getting an error. Here is my formula: IF({Sample Status} = Out, (DATEADD({Date Checked Out},7,“days”)), BLANK()) Can anyone help? Thanks

82
M
H
Harry_BattenParticipating Frequently
posted in Formulas
Extracting a number from a string

Hello, how can I extract the 6" and the 8" from these strings?

13214
kuovonne
U
Ujval_ShahInspiring
posted in Formulas
Script - issue saving data in DATE Type Field

I am using a script feature to add data in the table. One of my field type is “DATE”. Data coming from the API is in string. Hence, its not allowing me to add the data in AT, showing error - Error: Field “zzfldrJZNkO8gUQl69P” cannot accept the provided value.

11
Rupert_Hoffsch1
S
Sam_Giovenco1Participating Frequently
posted in Formulas
How to create buckets with a formula

Seeking help with a “If” formula to create buckets in a new column. For example, in column A i have series of different percentages. IN column B i wish to create a formula and returns a “text” if it meets the criteria…for example, if A1 = <100, “<100”, ifA1 = 100to120, “120”, ifA1 = 121to140,“140” and so on. Hope you can help Cheers

264
S

Badge winners

Show all badges
Terms & Conditions