Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,225 Replies
5939 Topics
R
Renaud_SavignarInspiring
asked in Formulas
How to avoid the display of null numbers?

Hi, can I get a blank space/nothing instead of “0” as the result of a formula ?

335
R
E
Eduardo_MatosNew Participant
posted in Formulas
Help with Formula: if contains piece of Text

Hey guys! I need a little help with a formula. Basically I’m sending data to Airtable that contains text separated by commas, and I need to check if a specific column contains a part of that text that I’m sending. I’m currently using Zapier with the Find function, but I’m having problems because it’s trying to find the complete string of text in the table and not considering the commas. In short, I want to know if this record in my column contains a part of the full text that I am sending. Here’s a simple graphic to explain:

20
E
G
Gabriella_GirolNew Participant
posted in Formulas
Using placeholders in formulas

Hello, I don’t know if it’s a stupid question or not, but I was wondering if this exists. In a CONCATENATE formula I was collecting names of ingredients and quantities, but I didn’t like how “0.25 lt” was looking like, so I thought of changing “0,25” with “¼” using an IF function: CONCATENATE(IF({Q.ty} = '0.25',"¼", IF({Q.ty} = '0.5',"½",IF({Q.ty} = '0.75',"¾",{Q.ty}))),' ',Ingredient) And it works, but obviously it works only with 0.25 and not with 1.25, 2.25 or more. (I don’t know why but every attempt to upload the example picture fails.) I was wondering if I could use a letter or something, like you can do in Math with the letter “n”, to write the expression like this: CONCATENATE(IF({Q.ty} = 'n.25',"n ¼", IF({Q.ty} = 'n.5',"n ½",IF({Q.ty} = 'n.75',"n ¾",{Q.ty}))),' ',Ingredient) Probably I could do something like changing every “.25” with a “¼”, but it could works with every number except 0, because it will result in “0¼” which is more ugly :grinning_face_with_sweat: I know I co

431
kuovonne
M
Mitchell_BaratzNew Participant
posted in Formulas
Sorting a table using a formula column

We have a base that we use for our accounts receivable process. We created a column that calculates how many days old the invoice is with the following formula: DATETIME_DIFF(TODAY(),{Date of Delivery},‘days’) . This takes today and subtracts the day of the invoice and returns a total number of days old. We have the base grouped by company. We want the company that has the highest number for days old (Meaning most overdue invoice) to be on the top of the base. We are not getting this result when we group by the calculation, it is only arranging within the grouped field by this calculation. How do we get the whole base to continue to sort by this field every time an order is marked as paid, and therefore removed from the base?

323
J
Michael_Benson
Michael_BensonParticipating Frequently
asked in Formulas
Formula to show which fields are blank

Hi there, Lets say I have four fields: A B C D I would like to create a single formula field that could show which of the above fields are blank. So, for example: If only A is blank, the field should say “Field A is blank” If A and B are blank, the field should say “Field A and B are blank” And so on … I have been able to create a formula that tracks whether all the fields are blank, but I am struggling to find a way to use the formula field to display which specific fields are blank. If anyone has any advice, that would be much much appreciated!

134
Michael_Benson
M
Matt_EngageNew Participant
asked in Formulas
Multiple IF Statements - Help Needed

Please could somebody help me troubleshoot this formula? I’m building a financial forecast and have a number of new business opportunities to build in. Not all opportunities have the same probability of converting into a sale however so I need to create a weighted value for each opportunity to give me a more realistic view of what my pipeline value is. The calculation needs to be Contract Value x Probability = Weighted Value, however I’m struggling to work out how I incorporate the multiple options into my formula. I have 6 probability options in total: 100% 75% 50% 30% 10% Lost (0%) If anyone could offer some advice, that would be much appreciated! Thank you.

113
M
J
Jorge_GonzalezNew Participant
posted in Formulas
Count unique lines on a long text field

I have some entries that have a long text field for example example.com example2.com HugeDomains Example3.com is for sale | HugeDomains Since 2005, we have helped thousands of people get the perfect domain name. example2.com Is there a way to show the total unique urls? I have this formula ABS(IF(LEN(SUBSTITUTE(TRIM({URL/Video Match URL}),"\n","")) > 1, LEN(SUBSTITUTE(TRIM({URL/Video Match URL}),"\n","")) - 1 , LEN(SUBSTITUTE(TRIM({URL/Video Match URL}),"\n",""))) - LEN(TRIM({URL/Video Match URL}))) But this formula only counts the total of the lines/urls. I wish to only count the unique urls. The name of the field is: URL/Video Match URL

00
J
K
Kristen_Lund_CTNew Participant
posted in Formulas
Calculating scheduling capacity

Hello! Thank you everyone in advance for your help. I use Airtable to manage the availability of a little over 100 therapists who work for my company. Most of these people work part time and, thus, have wildly different working hours. I have a formula field that calculates their total daily available work time and that field is summed within their group so we also have access to their weekly availability. The Max 30 Min Sessions/Day column uses this formula: ROUND(((({Hours per Day}*.6)/3600)*2),0) to calculate the maximum number of 30 minute sessions that fit into 60% of their daily availability. (They need work time outside of therapy sessions.) In general we’ve been using this formula to determine how many students maximum a therapist can be assigned. So my first question is: does this formula actually tell us how many 30 minute sessions fit into 60% of a person’s day? Or does it need to be changed? We use the same general formula to calculate a therapist’s capacity for seeing stud

240
K
M
Michael_CoryNew Participant
posted in Formulas
Finding the fist date of multiples for a to do list

I am trying to create a to do list view. Some items are linked to multiple days and are grouped together when sorted by date. I am looking for a formula that woulds sort them by the first date only. I have tried a rollup with the MIN function and just get a result of 0.

03
Justin_Barrett
S
Stacy_GoodchildNew Participant
asked in Formulas
Nested IF Statement

Hello, I’m new to Airtable & I have a formula that an Airtable support doc says should work & I can get to work in Excel, but keeps returning an error in my Airtable base. What I want to accomplish is this: IF the Jan 22 Commission Field is not blank, Jan 22 Prof Calc should equal that. Otherwise, IF Jan 22 Actual is blank, Jan Prof Calc should equal zero. IF it’s not blank, Jan 22 Prof Calc should equal Jan 22 Remaining. Thanks in advance!

203
S
B
Blair_MoradNew Participant
posted in Formulas
Rollup of rollups?

Hi! I’m trying to make an aggregate of data in a table and wondering what might be a good strategy. I am trying to build a table of tasks and their “effort”. some tasks are associated with other tasks. How would I go about doing this? My initial thought was to create a linked field called “predecessor”, rollup the effort field, and then sum in with the effort for the current task. This works fine for current task and 1 predecessor, but won’t go beyond that. I thought about doing a rollup of the rollups, but that led to circular referencing. Is there a formula I’m missing to make this work?

40
B
A
Alijah_Jevin_SoNew Participant
posted in Formulas
Counting the number of times a client scheduled a call from calendly

Is there a way for us to set up a column that can count the number times of the Last Date Modified was updated. My Airtable is connected to Calendly through Zapier and everytime they book a call, the record is just updated. I would like to see how many times that record is updated (to know how many times they have booked a call). Any ways to achieve this?

01
Dimitris_Goudis
K
KCP121New Participant
posted in Formulas
IF AND formula with blank/not blank text fields

Hello, I am new to Airtable and am trying to translate Excel formulas into the new syntax. I wrote a formula that works for number fields (below), and would like to create a similar formula for text fields. However, I am not sure how to substitute blank or not blank in place of the = and > in the formula. IF( AND( {2021 FIELD}>0, {2022 FIELD}>0 ), {2022 FIELD}, IF( AND( {2021 FIELD}>0, {2022 FIELD}=0 ), {2021 FIELD}, IF( AND( {2021 FIELD}=0, {2022 FIELD}>0 ), {2022 FIELD}, BLANK()))) This should be the equivalent of: if 2021 and 2022 are filled, use 2022. If 2021 is filled and 2022 is not filled, use 2021, if 2021 is not filled and 2022 is filled, use 2022, otherwise, blank Any help would be appreciated!

1063
Kamille_Parks11
L
LordNikon831New Participant
posted in Formulas
Caculating runtimes with duration

So maybe this is far more complex than I hope it is. I’m trying to build a television show format in airtable. I want the first item to be 0:00 (hh:mm:ss) and then to have a duration field where I can put the duration of the segment in, 0:30 (30 seconds). Airtable would then take that number and make the available start time of the next rows start duration. Trying to move objects around based on start / end durations, but it would be nice if the start of the next item was the time left off of the object before it. Can airtable do that without scripting?

122
Justin_Barrett
K
Kristen_SimentaNew Participant
posted in Formulas
Collecting and calculating weekly sums in another table + Zaps

Hi all - I have a Zap set up that sends info to Airtable from Submittable. It adds a “1” every time a reader adds a note in Submittable. This way, I can keep track of what everyone is reading every week. I’m trying to collect the running data somehow so that I can look at the information over time, week to week. So, for example: I need to know what Daniel has read every week and over time. It would be great to have another table where I can see (week to week) what the numbers are so I can fill out my reports. Does any of this make sense? I’ve been playing with rollups, but I’m not getting it. Thanks for any help you can give.

273
Hannah_Wiginton
K
Keith_LandaleNew Participant
posted in Formulas
Date formula problem

Hi I have the following formula in my table ID column, which works great, it puts together the customer name, date of photoshoot and type of photoshoot. Leaving it blank with no error message until the data is entered, but for some strange reason the date says 23/4/2022, but the ID cell puts it together as 22/4/2022 instead. See photo…… IF(OR({Customer Name}="",{Type of Photoshoot}="",{Date and time of Photoshoot}=""),"",CONCATENATE({Customer Name}," - “,{Type of Photoshoot},” - ",DATETIME_FORMAT({Date and time of Photoshoot}, “D/M/YYYY”)))

83
Hannah_Wiginton
C
ClemCNew Participant
posted in Formulas
Extract the first 2 letters of each word

Hello, I’m trying to extract the 2nd letter of each word after a comma but I fail :frowning: I have : Hello you, how are you, good And I looking a formula to have this result : He_ho_go Do you have any idea ? Thanks a lot

20
C
A
Allison_DurinkeNew Participant
asked in Formulas
Count the number of new records per quarter

Hi everyone! Is there a formula to count the number of records were added per quarter/year/month? I would like to know how many were added in general and how many records were added during a specific period by category.

91
Kamille_Parks11
S
stefan_dayNew Participant
posted in Formulas
Is there a formula to read other fields to determine a "multiple select" field's selections?

Greetings! This is my first post here in the airtable community. I’m importing a data set that has a series of fields that are using binary (0’s and 1’s) to allocate tags to a record. Ie: The “green” field is either a 0 or 1 to tell me that the record is green. I’d like to create a formula or automation that will read these 6 fields of 0’s and 1’s into a single “multiple select” field that displays the associated tags. Any clues or articles I should start with? Very new to formulas and automations.

00
S
J
Jenny_HislopNew Participant
asked in Formulas
Finish to Start Formula

Hi, is Airtable able to do Finish to Start formulas that autofill dates similar to smartsheets? I’d love to have a sheet with the tasks and deadlines are autofilled based on the formula for how many days from Go-Live, so all I would enter is the Go-Live date and all tasks are automatically filled. Is this possible in Airtable?

151
kuovonne
P
Paul_SuurmeijerNew Participant
posted in Formulas
Extracting text from a field unless a character is a space

Hi there, I’m looking for some help! I have a formula in a cell that basically creates an automatic Project ID. For this Project ID I use two fields: A list of teams, which is formatted like “B – Brand” or “PM – Product Marketing” Autonumber Field I’m now using the following formula: LEFT({Requesting Team:},2 ) & "-" & Autonumber This formula is great, except for the fact that I am sometimes left with spaces: The first Brand project becomes B -1 I would like for that to be B-1 (minus the space) Any tips on how to achieve this? Can I use LEN 2 unless the second character is a space, and then use LEN 1? Thanks in advance!

173
E
L
Lucas_TjahjadiParticipating Frequently
asked in Formulas
Formula multiply and divide by single select column

Hai, can anyone help me to write formula but based on single select column. If single select = Kg will be multiply 1 ( * 1) then If single select = Box will be multiply 5 (* 5) and then if single select = 200 gram will be divide 5 (/ 5)

71
A
A
Aaron_Reynolds_Inspiring
posted in Formulas
Getting Quarter to Show Correctly

Need help seeing why the Quarter is not pulling correctly. I have the following formula trying to pull the Quarter, but it shows Q1 for all the months. Example 12 is showing Q1 when it should show Q4. IF(OR(FIND(01,{Month}),FIND(02,{Month}),FIND(03,{Month})),“Q1”, IF(OR(FIND(04,{Month}),FIND(05,{Month}),FIND(06,{Month})),“Q2”, IF(OR(FIND(07,{Month}),FIND(08,{Month}),FIND(09,{Month})),“Q3”, IF(OR(FIND(10,{Month}),FIND(11,{Month}),FIND(12,{Month})),“Q4”)))) Here is a picture of the column I am pulling from and the results which you can see the 12 are not correct as they should say Q4.

144
A
E
EYES_InternatioParticipating Frequently
posted in Formulas
IF function to allocate a membership fee

We are membership based organization. We currently are implementing a new membership fee structure based on how old the member is: <20= 250 € 20-25 = 500€ 26-30 = 800€ 31-34 = 1.200€ 35 = 1.200€ (older and equal to) 40 = 500 € I have all the members “ages” but would like to create a column next to it with a function that will automatically calculate what membership fee they are with that age. Is there someone who can help with this? I have tried different things I found online through forums and youtube but im struggling to make it work. Thanks so much in advance

00
E
D
Dipal_Ashra1New Participant
posted in Formulas
How can I build a formula that changes based on blank field values

I am using Airtable to create naming conventions. I have used the concatenate function to put together the code: CONCATENATE({Country Code},"-",{Key Solutions Code},"-",{Digital PRG Code},"-",{Digital Platform Code},"-",Year,"-",Quarter,"-",{Purpose Code},"-",Description) Digital PRG Type options are Paid Social, Paid Search, Web Content etc. Digital Platform options are LinkedIn, Facebook etc. What I am trying to figure out, is that when Digital PRG Type is selected as Web Content, then how can I build the formula to remove the hyphen in the formula campaign name. As of now, this is how the code looks in the front end: GLB-PP-WC–2022-Q1-LG-TEST (you’ll notice the extra hyphen next to WC (web content) because the value in the table is blank (intended). What I want to show is the code like this when Web Content is selected. GLB-PP-WC-2022-Q1-LG-TEST Thank you!

00
D

Badge winners

Show all badges
Terms & Conditions