Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,940 Topics
  • 18,230 Replies
5940 Topics
R
Richard_Eddy1Participating Frequently
posted in Formulas
IF(OR()) formula rejected

This formula is being accepted in a field, and is working: IF({NOTES}=“to June 2022”,“Yes”,“No”) but both of these more elaborate versions are being rejected: IF(OR({NOTES} = “to Dec 2021”, {NOTES} = “Life”, {NOTES} = “Prepaid 2022-2023”), ”Yes", “No”) IF( OR( {NOTES} = “to June 2022”, {NOTES} = “to Dec 2021” ), ”Yes”, “No” ) Can anyone enlighten me on what I am doing wrong please?

74
R
S
Sophia11New Participant
asked in Formulas
Setting a specific time

Hello, Currently, this is the formula in the column to make it display the date of the upcoming Wednesday, but the times are random. I want to make the times in this column to all be 7:00am. Is there anyway to do this? IF(3-WEEKDAY({Submitted at})<=0, DATEADD({Submitted at},3-(WEEKDAY({Submitted at}))+7,'days'), DATEADD({Submitted at},3-(WEEKDAY({Submitted at})),'days'))

111
JonathanBowen
T
Trung_NguyenKnown Participant
posted in Formulas
Formula to retrieve name of field using Field ID

Hi all, I’ve got Field ID and would like to get its Field Name in a formula field. Is it possible at all?

41
kuovonne
A
arne_noriParticipating Frequently
posted in Formulas
Rollup and Count Duplicates - then output "count x name, count x name2"

Hi - i used search but only found different things asked … and the only similar question was closed and not aswered. … so i´ll try my luck here :slightly_smiling_face: It basically is something like an inventory roundup or item count on an invoice. I want to create a rollup field that gives me the counts of duplicates in a group in another table. Table 1 contains “roles” I can have multiple lines with the same role. eg: Field1 - Field2 Day1 - Role1 Day1 - Role1 Day1 - Role1 Day1 - Role2 Day1 - Role2 Day2 - Role1 Day2 - Role2 In table two i want to roll them up to something like: Day1: 3x Role1, 2x Role2 Day2: 1X Role1, 1x Role2 i found a solution where all options are hard coded … that is not usable as there are over 500 “Roles” already and we need to add more over time. any way of doing this? probably very easy and im just to blind to see the light … thanks!

224
kuovonne
E
EvanBNew Participant
posted in Formulas
Use formula to auto populate field in main table based on data from second table

Kind of new to using Airtable. Let’s say I have two tables. The first table is a master table with a whole bunch of information. For purposes of this ask, I am condensing to exactly what I need to know, but ideally this table will have a lot more info based on a client. The second table is a list of people involved and their role and what clients they have. A client can have 1 manager. But a manager can have multiple clients. So, what I want to do, is have a formula (or some other function) set up so that the Table1:Manager field auto-populates the Table2:ManagerName based on if the Table1:ClientCode value exists in Table2:Clients. I want it this way so that if clients get moved around managers, then it can update easily on my master table without as much manual intervention. Basically: Grab Table2:ManagerName where Table1:Clients is in Table2:Clients and insert it into Table1:Manager for Table1:ClientCode. Table1 Name | ClientCode | Manager GoKart | GK | ?? RollerSkate | RS | ?? FunZo

311
Kamille_Parks11
C
Christina_KNew Participant
posted in Formulas
Formula for extracting last text/digits from a code

hi i have a column with codes like this “0010o00002Fs719AAB” What will the formula to extract the 8 of the code? it should become Fs719AAB. Thanks in advance! Chris

9122
L
L
Luciano_AugustoNew Participant
posted in Formulas
How to select a value, within a record with several values.?

Hello, I need help please, consult, I need to know how to use in a formula only one term within a record. example: within the field price m2 printed + cut I have two values, how do I select only one if I have two or more? Is it possible? Thank you!

4710
Justin_Barrett
A
Aly_FossettNew Participant
posted in Formulas
IF(Find) formula

Hi, I am having trouble with my IF(Find) formula. It is working for the first half of the formula but not catching the other "IF(Find). Here is what I have so far: IF(FIND(‘TR’,{Name}),‘Trade’,IF(FIND(‘PR’,{Name}),‘Private’)) It is making them all “Trade” and not picking up on the “Private”. Screenshot below for reference

102
A
P
Per_CarlenInspiring
asked in Formulas
$100 Reward! Calculating upcoming billing date

I have three fields like this: | Start Date | Billing | Next Billing | In the Start Date field, there’s a past date In the Billing field, there’s a dropdown list with the values “Monthly”, “Quarterly”, “Semi-annually” and “Annually” In the Next Billing field, I want a formula that calculates next billing date based on the values in the two previous fields. Next Billing should always be a upcoming date (or today). Examples: If Start Date is “2018-01-01” and Billing is “Monthly”, I want Next Billing to say “2021-09-01” If Start Date is “2020-06-18” and Billing is “Semi-annually”, I want Next Billing to say “2021-12-18” If Start Date is “2016-10-24” and Billing is “Annually”, I want Next Billing to say “2021-10-24” I have tried to do this myself but I can’t work out how to do it. Can someone please help? Thanks! Airtable Next Billing Problem - Airtable Explore the "Next Billing Problem" view on Airtable.

1719
kuovonne
D
Dennis_PetrouKnown Participant
asked in Formulas
Conditional formula for character count

Hello, I am using this formula to display a red circle if a character count is over 310, or a green check if its not. I would like to add another condition but cannot get the syntax right, any help? I would like to add a 3rd condition, so if it is over 260 but under 310 it has a third symbol… LEN({Description}) & " " & IF(LEN({Description})>310, “ :o: ”, “ :white_check_mark: ”) Thanks!

164
M
M
Maria_RekhelsNew Participant
posted in Formulas
Adding the blank function to a formula

Trying to do the following in my base: if field a = X, field b = X and field c = X or blank. Result will be pass, fail. For more detail X = approved. What is the current function?

11
Justin_Barrett
J
Joel_KayNew Participant
asked in Formulas
IF, ROUNDUP Function

Hello, I am trying to write an equation for total pallets used. If the total cartons is over 4it gets palletized. My equation is IF(Cartons > 4, ROUNDUP(Cartons/{Cartons per Pallet}), 0). but Airtable wont accept it. Any advice?

103
Justin_Barrett
A
Aysia_SaylorKnown Participant
asked in Formulas
How to fix a combined nested IF and WORKDAY formula

Trying to create a formula that adds x number of business days if priority is x. This is what I have so far: IF(Priority=“High”,WORKDAY({Writer Assignment Date},3, IF(Priority=“Normal”,WORKDAY({Writer Assignment Date},5, IF(Priority=“Low”,WORKDAY({Writer Assignment Date},10)))))) It accepts this formula, but populates with an “#ERROR” in some fields and blank in others. All fields have the required selections. Trying to create a due date field that is customized by priority. If it is high priority for instance, the person would need to complete the assignment within 3 business days by that date.

172
A
M
Mohammed_HamdyNew Participant
asked in Formulas
Can I filter on a reference field using formula?

So I have a table with a field Client that references a record from another table. Client column as such can contain a value such as ['rec1234abc']. Now I want to do a filter to return all records that reference a specific record. I tried this (using the Python client): my_table.get_all(formula="ARRAYJOIN(Client, '')='rec1234abc'") But that returns me no results. How can I accomplish this?

683
Justin_Barrett
K
Kiana_BrownNew Participant
posted in Formulas
Can I tell an IF statement to ignore certain existing data?

(I’m using a very pared-down example here in hopes that I can convey what I’m trying to do, and then apply a solution to my actual base where I’m having this issue.) I paste data into my base from an internal report weekly. When I paste records from that report, some of the records already have numerical data in Column 2, and those records will be blank in Column 1. If a pasted record does NOT have numerical data in Column 2, that means it will have existing numerical data in Column 1 (and it’s data that is essentially related to Column 2). It is more important to me that every record has data in Column 2. For records with data in Column 1, I can calculate what belongs in Column 2 by using a simple formula - for the sake of example, let’s say the formula is {Column 1} * 2 . However, if there is existing data in Column 2 (i.e. Column 1 is blank), I need the existing data in Column 2 to stay put. I want to write an IF statement for Column 2 that essentially says: “IF Column 1 has a nu

51
Justin_Barrett
K
Kristina_CadleNew Participant
posted in Formulas
IF/AND formula to find new value for some data but leave other data alone

Hello! My airtable base gets new data imported into it every week from a separate report that is run. One of the drawbacks of the report is that it calculates revenue for our contracted associates, but not for our direct hires. In order to find the revenue for our direct hires, we have to divide the direct hire fee (DP Fee) by 13. Since we have the data for the contracted associates, is there a way to manipulate this field to leave certain data alone, but also divide other data by a factor? Screenshot below of data set to give you a better idea! Note that the field in question is “PIT Spread”. We are currently calculating these values for direct hires prior to adding new data to our database.

85
Justin_Barrett
S
Sushmit_GhoshNew Participant
posted in Formulas
Formula for creating value dependent select lists

Hi all, I have a field called CATEGORY with Single select values like "Alcohol, Snacks etc ". For each of these values I want to maintain a SUB_CATEGORY field for example : For Alcohol, sub categories may be Beer, Gin, Whiskey. For Snacks, Sub categories may be Chips, Pringles. Now my requirement is: I want to create a formula that reads the CATEGORY and only displays the relevant sub categories for that category in the drop down. WHat is the best practise to achieve this. Thanks a lot in advance. Regards Sushmit

20
S
S
Sally_de_WittParticipating Frequently
asked in Formulas
Creating a formula for [date] subtract [specific number of days]

Hi there, I want to add a formula to result in a date. I have a delivery date column. and I want formula to calculate a date 7weeks (49 days) before the delivery date. I have this working in Excel & numbers, but copying the formula into Airtable does not work - results in NaN. Please may you help. Thank you, Sally

652
S
B
Bianca_BaileyNew Participant
posted in Formulas
Formula to determine blank due dates (across 3 fields)

I am currently working on a formula which is able to detect which 1 out of 3 date fields are filled. I have done this with a similar table which detects which 1 of 2 date fields are filled but cannot seems to make the formula work with 3 options. IF(OR({Date A}=BLANK(),{Date B})=BLANK(),{Date A},{Date B}) I have tried the following 2 options, could someone help me identify where I am going wrong? IF(OR({Date A}=BLANK(),{Date B})=BLANK(),{Date C})=BLANK(){Date A},{Date B},{Date C}) IF(OR({Date A}=BLANK(),{Date B})=BLANK(),{Date A},{Date B}),IF{{Date C}, {Date C})

31
Justin_Barrett
P
PeterBInspiring
asked in Formulas
Hidden difference between values that appear the same in two columns

I have a table containing a PostCode column, and Address columns 1 to 6, all of type “single line text”. I wish to know which of the columns contains the PostCode and will use SWITCH(PostCode,Address1,1,Address2,2 …). However even when I can see which column contains the PostCode, let’s say Address3, a formula of PostCode = Address3 is returning false, when it should be true as they appear equal. Adding LEN() to both sides of the formula confirms they are the same expected length. The values I see in both columns appear identical. This behaviour is not the same in a table created with just these 2 columns and the values manually input. What differences in the values could there be that is not visible and how might the difference be eliminated?

65
P
M
Markus_WernigInspiring
asked in Formulas
How to display formula as currency?

I have a formula that’s doing what I want it to do. But I’d like the field {ACTUAL VALUE} to be currency formatted like {EST. VALUE} - I’d also like to get a SUM at the bottom of this column, so I need it to be a number. This is what I have so far. IF(STAGE="Executed", {EST. VALUE},"") What do I need to add? Thank you so much in advance.

3453
M
P
Pedro_HenriqueNew Participant
asked in Formulas
Date formula conflict

Hey, guys! I’m trying to add a column that return to me a specific message when attends some date criteria. Actually, I did the formula and it was working well: IF(AND(DAY({Final date})=DAY({Request date}),MONTH({Final date})=MONTH({Request date}),YEAR({Final date})=YEAR({Request date})),“ :warning: Warning”, IF({Final date}<{Request date},“ :red_circle: Wrong”,“ :white_check_mark: Right”)) But after some tests I noticed that something weird was happening How you can see, the first and the second line returned strange results… Is there something wrong with the formula? Thanks!

132
P
K
kxr1mNew Participant
asked in Formulas
If function using dates

Dear airtable community, i am looking for a function that: if a certain cell is modified, i get a value in a ‘last modified time’ field. after getting that, i want to know the work day difference between my ‘last modified time’ field and a ‘order date’ field. if the mentioned cell at first hasn’t been modified yet, i won’t get an return for the ‘last modified time’ field and so i want to have a blanc for the difference in work days. the function i was able to set up for now is: IF({found at},WORKDAY_DIFF({order date},{found at}, BLANK()) i’m aware of the fact that the point of formulating the conditon, isn’t fullfilled, but i have no idea what else to put there. if there’s an easier way of fullfilling my need than the if function, please. let me know :slightly_smiling_face: as always thanks in advance and stay safe guys

92
K
L
Lusenii_KromahNew Participant
posted in Formulas
Field value as condition in Rollup Condition

Can a conditional rollup take a field value from the current table as a variable? So in the screen shot above I would like to insert the field {Last Reload Date} from the current view, so that I can aggregate from that date.

233
Kamille_Parks11
A
Air_Table3New Participant
posted in Formulas
Product of an Array

Airtable can take the sum of an array through the rollup field type. Was wondering if there was the equivalent for the product of an array? For example if there is a series of returns (1+r1) (1+r2) (1+r3) With each return figure on its own record line, I want to figure out how to take the product of all three?

30
A

Badge winners

Show all badges
Terms & Conditions