Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,228 Replies
5939 Topics
C
Christine_AyresNew Participant
asked in Formulas
Piece of IF formula not returning a result

I am having trouble with the second IF part of this formula, it doesn’t return any results. I also tried it on it’s own and it still doesn’t return a result. IF({Writer Owned (adds up to 100%)}=.33330, VALUE(“16.66”), IF({Writer Owned (adds up to 100%)}=.33340, VALUE(“16.68”)))

92
C
R
RomualdNew Participant
posted in Formulas
Formula results depends on links not value

hi! I have a strange issue. My formula (nested conditionals based on fields you can see in the image) provides different results for the same values! It turned out that the reason lies in the ID Roadmap link field. The first record has only one linked record while the second one has two - one of which is has empty values. When I delete that link, the Result formula changes to “ID excl from denominator”. However, I’m not able to delete those links one by one (it’s a long story). The question is: how do I create a formula that will be based only on values I can see?

20
R
A
Almas_SayyedNew Participant
posted in Formulas
Require counter to be displayed for upvote/downvote

Hi team, Building a similar clone as to the Producthunt upvote feature. I would like to know how I can use the counter in airtable fields. As in when the user clicks on the Frontend the counter should get updated on the frontend as well as the record/database. Also a limitation in doing this the following way Is it possible to add a vote/like button to a public air table (without the need to have collaborators) Base design Do you mean something like this: [image] The button links to a form with the option prefilled. [image] Submitting the form redirects to the original page where you can see the updated vote counts. Here is the live demo. is one can vote only through submitting the form. Thats, not the whole idea if I just wish to upvote the content I needn’t fill the whole submission form for it. Any suggestion would be helpful here Almas

242
A
J
Jan_KiekebenParticipating Frequently
asked in Formulas
Color records linked with a record of "special kind"

I have a projects table where I link multiple records from a resources table. The linked records (resources) have different attributes (e.g. aaa, bbb, ccc, ddd) that come from a “single-select” field. My challenge now is to find projects where resources of a special attribute (e.g. bbb) are linked. Is there a function/formula field I could use to color records in my view? I’m not the full Expert on automation or formulas, but I think of a formula that goes to the link of the record, looks for the attribute, and then makes it possible to color or maybe write a “true/false” to another field and I color/filter/group for that. Currently the only solution I could find is to separate the resource-link field into a "link “bbb” resources here (links based on a filtered view) and all other resources on another field. Thus I see in a coulum quickly if a resource with the attribute “bbb” is linked. I don’t like this approach as I want to have all resources linked to a project in one field. It wou

3111
J
I
Isaac_Tanner-DeParticipating Frequently
asked in Formulas
Estimated Delivery Date from todays date or date Payment is Received (nested if formula)

4 columns to do this Shipping Method - single select: boat, shipping Payment Received - checkbox Date of payment - date modified linked to payment received Estimated Delivery Date - formula. I want to write an if statement. This is what I have so far but I’m not sure how to write the second if statement IF(Shipping=“boat”,DATEADD({Date Quoted},52,‘days’),DATEADD({Date Quoted},21,‘days’)) I would like to know how to write is so that is when payment is received estimate delivery is from payment date if date modified is blank then from today boat +52days if flight +21days if date modified is not blank then from date modified boat +52days if flight +21days

124
Dimitris_Goudis
1
111267New Participant
posted in Formulas
Show unselected items from a multi select field

Hello. I have the same question as here - Display multi-select items not selected But it seems as though there is no answer in that thread. Does anybody know the solution to this? What formula can show only unselected in some field items?

156
R
P
Philip_BegelInspiring
posted in Formulas
Airtable cells to Mural sticky notes

Hi, I’m trying to copy Airtable cells into the whiteboarding tool called Mural. I have a formula that removes line breaks in the text field it’s calling and I want to take those cells and copy them into Mural to generate sticky notes for each individual cell. Now, if I copy a few cells at a time, it works fine, but this is tedious. So if I copy all the cells, then Mural seems to combine some of the cells for some reason into one sticky note. If I created a formula that aggregates all the cells into one in a different table (separated by a line break in the cell), when pasting it into Mural it adds quotation marks around the entire thing so Mural treats it as one giant sticky note. Any ideas on how to mass copy and paste from Airtable cells into Mural?

160
P
M
Matthieu_LABERIInspiring
posted in Formulas
Formula If, date

Hi the community. I have some difficulties with a formla. I deliver package and i want to calcul the date of the preparation in relation to the delivery date decided by the customer I want to calcul a date but this is conditionate with the result of a column. Column A, i have a date : 12/04/2021 Column B, i have two type on result : “GLS” or “Chronopost” This is what i want is : If GLS = Date of the column A - 2 day. With my example the result : 10/04/2021 If Chronopost = Date of the column A - 1 day. With my example the result : 11/04/2021 Sorry for my english. Could you help me please ? Thank’s a lot, Matthieu from www.lagreentouch.fr

176
A
M
Maryam_Al_KaabiNew Participant
posted in Formulas
Overdue notification

Hello, In airtable how can i add the formula to send me a notification that is linked to the overdue of the Due date field? To send me an email notification that i need to follow up

131
D
H
Hannah_MorganParticipating Frequently
asked in Formulas
Formula field to calculate duration of a record from creation-->complete status

Hello! I’m looking to create a calculated field that shows me the number of days it takes to complete a record. The start date should be when the record is created, and the end date will be based on a dropdown field’s value (e.g. when the status field is set to “complete”). My thinking is that I need to create three formula fields: The date when a record is created (I’ve managed to achieve this part). The date when the status dropdown field is set to “complete”. The difference between the two dates in days (I’ve managed to achieve this part). So basically I’m missing how to create the 2nd field. Am I approaching this the wrong way? Is there a better way to show this report that doesn’t involve using an app add-on? Thank you!

335
Dimitris_Goudis
J
Jonas_Van_der_RNew Participant
posted in Formulas
Other select option?

Hi! I’m making a database for me to keep track of my train delays and the compensations I need to request, have requested and are approved or declined. I like to have an overview of the route I took. For this, I have two fields: Departure train station Arrival train station Both have these two options in a single select: Antwerpen-Centraal Noorderkempen Basically I want to only select the departure train station, and the formula should put the other option in the arrival train station. For example if I select “Noorderkempen”, the arrival train station should be set to “Antwerpen centraal”. How would I achieve that? Thanks in advance!

21
Kamille_Parks11
D
Dhaval_GorasiaParticipating Frequently
posted in Formulas
Formula displaying incorrect result

Hi, As you can see in the image, formula (price - discount + shipping charges) is returning 1 in first field as I have two values separated by commas that is 399,1076. Is there any way to write this formula, so it takes into account units separated by commas and return the correct result.

196
A
I
ianonlineParticipating Frequently
asked in Formulas
IF dates past 60 days then :white_check_mark:

Hello! Can anyone advise on the IF formula to state if a date has passed a number of days? So if the date in “DATE” column is more than 60 days ago, then :white_check_mark: or :no_entry: . I’ve been looking through date and time functions in the Formula Field Reference, plus lots of posts in this community, but haven’t been able to find an answer that works. I’ve got a formula to display the number of days since, and looked into creating a view filtering for those above 60 days (that didn’t work anyway), but I would prefer it to just display :white_check_mark: (60 days or more) or :no_entry: (less than 60 days). Thank you for your help. Ian

163
I
R
Rae_GroshanParticipating Frequently
posted in Formulas
Need assistance with syntax for merging two statements

I have a table where I am tracking employee trainings. I have this formula to output whether the staff member is up to date on a certain training: IF({Days} = BLANK(), “Overdue”, IF({Days} > {Max # of Days (from Training)}, “Overdue”, “Up to Date”)) I am trying to add another option. I have another formula that determines whether the staff member is almost due for training: IF(AND({Days} < {Max # of Days (from Training)}, {Days} > {Overdue Threshold (from Training)}), “Soon to be overdue”) I was wondering if I could get help with the syntax to combine the two formulas into one?

95
Alexey_Gusev
A
Ashley_HogrebeNew Participant
posted in Formulas
Formula Help - Multiple Linked Records Providing Error Message

Hey Airtable Friends! :slightly_smiling_face: I’m working on a project where we want to have an error message pop up if our team accidentally selects a billable item that is not authorized to bill for that specific product sold. It works perfectly when there is only one linked record in the ‘Product Sold’ field, but if there are two linked records, it will always give a ‘ :stop_sign: This Service is NOT Authorized to use this Billable Item’ message, even if the linked records are included in the ‘What Products Can Use This Billable Item’ field. This is Formula I’ve set up: IF(FIND({Product Sold (from Project)},{What Products Can Use This Billable Item}) <= 0, '🛑 This Service is NOT Authorized to use this Billable Item', '✅ This service is authorized to using this Billable Item') Is there a way to edit this formula to work even if there are multiple records in the Product Sold field? Thanks so much!

167
J
I
ianonlineParticipating Frequently
asked in Formulas
Help calculating age (since date)

Hello, I’m trying to get a formula working to determine age in years with one decimal place. The date is in a column called “Created”, the formula I’ve got is: DATETIME_DIFF(TODAY(), Created,'years') & ' years' Can anyone help add the decimal place so the result for example is “1.2 years”? Thank you.

72
I
C
Chattanooga_FunParticipating Frequently
posted in Formulas
Assigning a drop down with 2 separate values, to automatically populate other fields

I am wanting to use the multi drop function to automatically assign numerical values to my clients profile line once I choose their desired choice. For example, the client is borrowing money and they choose to have 5% interest rate and 3 points on the loan. Or if they choose 4% and 4 points, I would want the drop down to fill the other two pre made fields with the data instead of having to go and put it in there myself. I have looked a most videos and cannot see the steps to make this happen if possible. Thanks again wonderful community!

112
C
L
Lewis_SternbergKnown Participant
asked in Formulas
Extract first option from multiselct field

I have the following options in a multiselect field with: Table sample options: first row: “$5,400” & “10,400” second row: “10,400” & “Promo” How can I extract the first option; for first row would be “$5,400” and for second row should be “$10,400” Thank fo your help. The ‘comma’ is giving me hard time to extract the right record.

243
Kamille_Parks11
V
Vasishta_MonavaNew Participant
posted in Formulas
Calculate hours from Start Date + Time field and End Date + Time field

Hello - I am trying to calculate hours difference between two start date + time field & end date + time field, and looking for a formula that works to do that. Here is a screenshot:

22
V
F
FortGjort_TraniKnown Participant
posted in Formulas
Formula for calculating end date of license

My sister can somehow calculate the value for the field End date in her head. Can someone please give me the formula that she is using, as I need to use a formula? :grinning_face_with_big_eyes: https://airtable.com/shrWJArPUxWUy7HG2

2118
F
J
Jack_PInspiring
asked in Formulas
How to pull a decimal number out of an Array OR how to use conditional lookup in combination with MAX(values) to pull a number from an Array

Hi everyone, My goal here is to allow my master inventory list to have a dynamic price based on the most recent purchased price of an item. So if an item cost $10.00 in August, but costs $15.00 in September, my inventory will initially show a cost of $10.00, and when the September-priced item is purchased it will update to $15.00 I could get this done with an Automation but i’m looking for a cleaner way to do it without having to add formula columns and update their results to a text field in order to use the Find Records functionality of Automations I’m using a Rollup field to pull the most recent Linked Record by using the MAX(Values) condition shown below: This allows me to grab the most recent record in my purchasing table that’s linked to the inventory item. I would then like to pull the associated price from that Linked Record from my Purchasing table into my Inventory Table I’m trying to use the RIGHT() Function (with and without turning it into a string) but i’m losing the

22
J
G
Gabriel_GiannetNew Participant
posted in Formulas
Count how many times a phrase appears in a cell

Hi all, I am trying to count the number of ways this particular phrase appears in a cell So for the image above, count the amount of times the string “Late Payment” occurs, i.e would return ‘2’. Thanks team,

382
Justin_Barrett
S
Sara_StahlbergKnown Participant
asked in Formulas
Formula Help - Contingency and nested if/then?

Ok, I am working on a project and have been staring at my formula for quite a while now. Hoping somebody can help me out! Essentially, I would like to combine several fields into one, but include certain fields only when they are filled. Here is my formula: DATETIME_FORMAT({Clinic Date},‘MM-DD-YY’) &" “&{Clinic Name + Activity}&” - “&{Blue - Allocated}&” Blue"&", “&{Red Allocated}&” Red “&” , “&{Yellow - Allocated}&” Yellow"&" - HL Provider: “&{HL Provider}&” - ( “&{Lead Team}&” )" In this scenario, the following are true: Blue, Red, and Yellow allocations are numeric fields that may or may not be filled in on each row. I’d like them to only appear if they have a number greater than 0 populating that field on a given row (otherwise, I don’t need to see it in the formulated text) HL Provider, Clinic Name, Lead Team are all just other fields that I am interested in seeing in the text, and I’ve tried to format it accordin

2011
S
A
Ambroise_DhenaiInspiring
posted in Formulas
How to add a space every 3 digits? (european currency)

How would you convert 10000 to 10 000? Similarly, 10000,00 to 10 000,00. I couldn’t find an existing formula for this.

146
Justin_Barrett
R
Rafa_RamosNew Participant
posted in Formulas
How to operate formulas with specific cells?

Hi! I am trying to sum certain values ​​from a specific row and column and I don’t know how to do it. Better to explain it with an example: In this image I am trying to get the following value in the third column: (1 + 2 + 3 + 4) / 4 = 2.5. That is, I am trying to add the 4 items from the second column (1 + 2 + 3 + 4) and divide them by the value from the first column (4). The result will be displayed in the third column. Like this: 4 1 2.5 4 2 2.5 4 3 2.5 4 4 2.5 All this for various groupings, such as the one shown below. Like this: 4 1 2,5 4 2 2,5 4 3 2,5 4 4 2,5 7 1 1 7 1 1 7 1 1 7 1 1 7 1 1 7 1 1 7 1 1 Could someone guide me? Many thanks!!

903
Justin_Barrett

Badge winners

Show all badges
Terms & Conditions