Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,227 Replies
5939 Topics
J
Jason_BautistaNew Participant
posted in Formulas
If and Find Formula

We are trying to add a formula that if one room is available even though others are not it should still says “Available”. If one room says Becoming Available but there’s no room that is available then it should say “Becoming Available”. If all rooms are rented then it should say “Rented”

54
J
M
Ma_TeNew Participant
posted in Formulas
Comparing two tables and update the status and new data

Hi community, I am looking for a solution where I can compare two tables and update the main table with both status and also the new data entries from the second table. Appreciate if anyone can shed a light on how this could be achieved. Basically we have a data scraping code that writes the entire dataset to a table (let’s call this secondary table) on regular basis. And we have a main table used in production. What we want to achieve is to use the secondary table to update the main one. If the dataset does already exist in the main table and also exist during the latest script run on the secondary table (there is timestamp), then the “status” field in the main should be updated to “available”. If the dataset does not exist in the main table but show up during the latest script run on the secondary table, then the “status” field in the main table should be “new” If the dataset do exist in the main table but does not exist anymore during the latest script run on the secondary tab

312
M
S
Sylvie_MNew Participant
posted in Formulas
Edit and Replace in one Multipe Select Field?

Hi I am new to Airtable… big learning curve. I am using it for Shopify with the Shopify App Airpower for bulk uploading including images! (Amazing !). However I am changing themes and need to add to the text in my multiple select field. example. I use tags… so in a single field my current tage are formatted with a underscore… I have 20 tags…: color_blue, color_green, size_7", size_9", shape_tube, shape_slant, skirted_vinyl I need to keep the existing “tags” for now (during the transiton of themes) but for my new theme I need all the same tag but WITHOUT the first workd and underscore example: blue, green, 7", 9", tube, slant, vinyl… how can I creat an automation? I am not proficient in formulas other than excel. Please help! Thank you!

454
S
A
Andrew_DaviesInspiring
posted in Formulas
Formula to lookup values from another table

Hi I have two tables Transactions - with a date Periods of time - with a Title, Start date and End date. ie “Year 1” runs from 1st April 2019 to 31st May 2020, “Year 2” runs from 1st June 2020 to 31st May 2021. I’d like to have a field in the transactions table, which looks up from the periods of time table, which year a transaction took place. So if the transaction was on the 1st July 2020, it would be in the Year 2 fiscal period. If it took place on the 1st May 2019, it was in the Year 1 fiscal period. I know I could write that with a long IF statement, but that is cumbersome! If my fiscal periods were whole years, (ie 1st Jan to 31st Dec) it would be so easy! Any ideas or suggestions very welcome. Thanks, Andrew

122
A
I
itoldusoandsoInspiring
posted in Formulas
Easy trivia problem and almost broke mind, I guess getting old now

I have a complex formula about 2 letter size pages long. The formula didn’t work and I spent 2 hours of my life trying to figure out why it didn’t. Looking at the formula, after procrastinating a bit I went on crunching through it but I was expecting to be rewarded by a nice catch worth my time going on through this fishing expedition. Instead I was taken by surprise of how sometimes complex problems require simple solutions. Here is the over-simplified formula. The issue was I wasn’t negating the formula on the LEFT correctly. To negate the formula on the LEFT, I need to negate also function “OR” and turn it into “AND”. I guess this is a 101 basic of Math but it has been so many years I don’t remmeber. I guess I am getting old now. The first column on the left is PRICE and the field can be empty, zero or non-zero. The formula is check if the field PRICE is empty or zero and show “YES”, otherwise show “NO”. The first picture shows the formula wasn’t converted correclty on the RIGHT. Th

10
I
A
Arnau_ForescatNew Participant
posted in Formulas
Helps please! (division formula // IF)

Hi everybody how are you? I am trying to create a formula that divides by 2 the amount of the field “Quantitat” only if in the “Tipus” field it says “sacas” and places it in the field “Tones”. Is it possible? I am using the following formula: “IF (Tipus =” sacas “,” Quantitat "/ 2) is it correct? Thank you very much for the responses, greetings.

132
A
P
Phil_RavierNew Participant
posted in Formulas
IF formula & empty / not empty cells

Hello, I’m Phil And I’m working for a video production company. We create video for others companies, all the production follow up is made with airtable. I’m working on a formula that can increment a cell (0,1,2,3) if specific date cells are filled let’s illustrate my use case I need to log the delivery date of video production (SLA are calculated using those dates) if {v1} is empty = work in progress = 0 if {v1} contains a date = done = 1 if {v2} contains a date = correction done = 2 if {v3} contains a date = re-correction done = 3 then I’d like to use that “increment calculation” to calculate my production costs using another formula {unit_cost}*{increment_calculation} something like that. Tell me what’s your advices :slightly_smiling_face: thanks

61
J
T
Toby_MurdockNew Participant
posted in Formulas
IF formula failing on Lookup Value

I am using a simple formula: IF(TDay > TODAY(), 1, 0) It works fine in a table where TDay is a field containing Date values. However when I use the formula in another table where TDay is a field containing Date values that are pulled from another table via a lookup then the formula does not work (it either provides all 0s or all 1s and does not perform the evaluation function). Any thoughts on what I’m doing wrong? Thanks in advance.

261
Justin_Barrett
L
Ludom_InitiativParticipating Frequently
posted in Formulas
[Formula] use option IDs instead of values for single/multiple select fields

Hi everyone, I can’t find anything about this in the documentation or in the Community posts: is it possible, in a formula, to refer to field option IDs instead of option values? Let’s take a basic single select field named “Choice” with 2 values: “1” and “0”. It’s likely that these values could be changed to “Yes” and “No” or “Acknowledge” and “Refuse” in the future. To avoid any issue with the formula when that happens, I’d like to refer to the option IDs matching the value “1” (something like “selQHcj58xhh44C1J”) instead of the values: instead of IF(Choice="1","blah","") I’d have liked to use IF(Choice="selQHcj58xhh44C1J","blah","") - but it’s not working. Any way this would be possible in a formula (not in a script)? Best, Ludo

132
Justin_Barrett
J
Jason_CohenNew Participant
posted in Formulas
Need help with NaN when using formula to track difference in dates

Hi, having trouble getting rid of the “NaN” when trying to figure out the average difference in dates per sales manager. In one table (named “Tracking Log)” have a date field for “Transport Order Date” and a date field for “Commitment Date”. I used the following formula to look determine the difference in dates by number of days for each sale they made in a field I named “Time to Order Transport (days)”. IF( AND( {Transport Order Date}, {Commitment Date} ),DATETIME_DIFF({Transport Order Date},{Commitment Date},‘days’),BLANK()) Each manager is doing dozens of these transactions each month so I now want to see the Average days per manager for this date difference. I have another table (named “Sales Manager”) where I did a look-up of the field from Tracking log named “Time to Order Transport (days)”. I then created a new a rollup field, as well as a formula field using “Average”, however I have some new managers who have just started and have blank fields and I can’t figure out how

52
Justin_Barrett
R
Rosa_RamosInspiring
asked in Formulas
Extracting month from a string of dates

Thanks in advance to anyone who even scans this query! :blush: I have a rollup field that is flattening an array of dates - that’s working great! What I want to do is produce this same information showing only the month. For example, I have this list (a flattened list of dates from 3 different records showing only unique dates): 13/09/2021, 20/09/2021, 27/09/2021, 04/10/2021, 06/10/2021, 13/10/2021, 20/10/2021, 13/12/2021, 29/11/2021, 22/11/2021, 15/11/2021, 08/11/2021, 25/10/2021, 20/12/2021, 09/12/2021, 01/10/2021, 08/10/2021, 18/10/2021, 22/10/2021, 29/10/2021, 05/11/2021, 12/11/2021, 19/11/2021, 26/11/2021, 10/12/2021, 17/12/2021, 03/12/2021 I want to create a formula that converts this list of dates into (just the month of each date as an array): 09,09,09,10,10,10,10,12,11,11,11,11,10,12,12,10 etc… If I extract the month before producing the full array of dates, of course, it only ever gives me 1 instance of each month which is not what I want. I’ve been trying to work out the

385
R
C
Chioma_KaluNew Participant
asked in Formulas
My multiplication keeps giving an error

Hi, I am trying to figure out why I keep getting this error message: The Impact Value is the average of the Confidentiality, Integrity and Availability column. (Confidentiality+Integrity+Availability)/3 The Risk Value should be multiplying the Impact Value with the Likelihood Value. But I am getting an error message with this basic formula {Impact Value}*{Likelihood Value} Please help.

94
kuovonne
B
Business_DeveloNew Participant
posted in Formulas
Is it possible to combine formulas to avoid having multiple formula columns

I have 4 sets of formulas. SUBSTITUTE({Status}, “Floater Membership”, “Available”) SUBSTITUTE({Status}, “Pending Lease”, “Available”) SUBSTITUTE({Status},“Pending Payment”,“Available”) SUBSTITUTE({Status}, “Floater Membership”, “Available”) We wanted to merge them into one. I tried to do it with this formula SUBSTITUTE(SUBSTITUTE({Status}, “Pending Transfer”, “Available”), “Pending Lease”, “Available”), “Pending Payment”, “Available”), “Floater Membership”, “Available”) but it is not working. What is your workaround with this?

92
Justin_Barrett
D
David_YNew Participant
asked in Formulas
Extract dates from a field

Hello ! I’m new to airtable and struggling to write a formula that allows me to extract dates from a field. I have tried many different formulas found online but nothing matches my case. I have a SPRINT column that hosts in each field a set of dates: 2022-01-03, 2022-01-10, 2022-01-17… (can go up to 53 dates). I would like to extract from any of the fields in the SPRINT column the FIRST date (2022-01-03) and send it to another field in a new column named START. Then, extract from any of the fields in the SPRINT column the LAST date (2022-01-17) and send it to another field in a new column named END. Do you think that is possible and if so could you help me find out the correct formula ? Thanks !

344
D
G
Gillon_DobieNew Participant
posted in Formulas
Sum daily records from one table according to date range in another

Hello, I am new to Airtable and am recreating an unwieldy spreadsheet into a (hopefully) elegant database! We harvest crops according to a varying ‘Grow Cycle’. The cycle is bookended from the ‘Seeding Date’ to the ‘Cleandown Date’. We record information like ‘Harvest Yield’ for each Grow Cycle. We also conduct daily checks of water and electricity meters to record our water and electricity consumption. In Excel using the SUMIFS function we can automatically sum all the water and electricity records between each Seeding Date and Cleandown Date to calculate the water and electricity consumption during each Grow Cycle. This is useful for calculating electricity and water intensity. I cannot work out how to replicate this in Air Table. Ideally I would like to use a Form so that Daily Checks Data can uploaded to the Data Base each day. I would then like to use another Form to upload Grow Cycle Data (e.g. Seed Date, Cleandown Date) and use a function to automatically sum all the Daily Check

71
Rupert_Hoffsch1
P
Phil_GNew Participant
asked in Formulas
Just making sure... no easy way of having a field with percentage of sum, correct? 🤔

Hi there! :grinning: Thanks in advance for taking the time to help me out. I’m new to airtable and I’m currently evaluating if I can use it for my small business to do the budgeting for my projects. I actually think what I’m looking for is too much of a spreadsheet functionality which isn’t supported by airtable as a database tool. But I would love to be wrong because it would make my life so much easier if it was possible: I’m trying to calculate the costs of each of my departments and usually just have a long list of items and staff. Some fields I need to be the percentage of the total costs (and update automatically if the total sum changes). For instance I would like to have a field with contingency costs of 5% of the total sum. Am I right assuming that there is no easy formula that can do that? And I would have to follow a complicated workaround with rollup fields and a linked table instead, because you can’t use summary values in formula fields? I feel like I’m really in a pick

60
P
R
Rachael_CastelaKnown Participant
posted in Formulas
Help with nested IF statements

Hello! Here’s what I’m hoping to accomplish: If {Inventory Arrived?} = ‘Inventory Arrived’ and HAS a date in {When did Inventory Land?} use that date. If {Inventory Arrived?} = ‘Inventory Arrived’ and DOESN’T have a date, use {Updated Estimated Delivery Date} date If {Inventory Arrived?} = ‘Inventory Not Arrived’, use {Updated Estimated Delivery Date} Thanks for your help!

31
A
B
Blending_ProducKnown Participant
asked in Formulas
Closest "X" day

Hey guys! I would like help to develop a formula that would give me the day “02” closest to the date of a specific field. For example: If in the {Purchase day} field the date is 02/25/2021, I would like the formula to show me 03/02/2021, if {Purchase day} is 05/14/2021, I would like the field show me 02/06/2021. I hope I managed to explain. Thanks in advance! PS: I also need help creating a formula that shows me the “02” day of the next month if the day of {date of purchase} is greater than 02. I’m trying to automate credit card billing. I’m racking my brain a lot.

106
B
J
juseNew Participant
posted in Formulas
Auto increment number for each customer

I have a table where I track sessions with customers. I’d like each customer to have a session number that automatically increments over time. That means that this number would not be unique in the table. Is there a way to automate this? Something like this: John Smith 12/18/2021 1 Nancy Blume 12/19/2021 1 John Smith 12/20/2021 2 #autonumber

2462
Kamille_Parks11
M
MaciejNew Participant
posted in Formulas
Need formula to have empty input when month already above exits

Hi, I have this situation: But I need to have December 2021 only on the first row created in December. When a new row will be added in January only the first row in January will have “January 2021” on column Month. Tried to do it with IF and AND but still gets an error. Could you please help me with this?

54
Justin_Barrett
E
EricB11New Participant
posted in Formulas
IF formulas working that doesn't work when joining them using IF(OR formulas, help please :)

Hello everyone, I’m sure this is an easy one but well, hard enough for me it seems :winking_face: I have 2 working IF formulas, that run correctly independently but I need to run them together using IF(OR formula and it doesn’t work the way I do it. Thanks in advance for your help, surely an easy one for many of you guys. 2 working IF formulas: IF(LEN({Langue Maternelle})-LEN(SUBSTITUTE({Langue Maternelle},"Français",", "))>LEN("Français"),"Yes","No") IF(LEN(Langues)-LEN(SUBSTITUTE(Langues,“Français”,", "))>LEN(“Français”),“Yes”,“No”) Non-working combine IF(OR formula: IF(OR(LEN(Langues)-LEN(SUBSTITUTE(Langues,"Français",", "))>LEN("Français"),(LEN({Langue Maternelle})-LEN(SUBSTITUTE({Langue Maternelle},"Français",", "))>LEN("Français")),"Yes","No")) Airtable accepts it but it returns blank cells everywhere instead of “yes” or “no”. Thanks again ! Eric

74
Alexey_Gusev
M
Michael_TownsenInspiring
posted in Formulas
The Link field name is wrong

The field name to identify the table (when linking a record) is often wrong. “Field on this table that links to the records you want to summarize” Any idea why this is and do others have this issue?

73
kuovonne
L
Lauren_AzicriKnown Participant
asked in Formulas
Formula help, Concatenate with multiple linked records

I have a column which has multiple linked records as Contacts in. Then I have lookup fields for the contact information (email and phone number) for those linked records. I am trying to make another column as a concatenate formula which shows “{Contact Name 1}, {Email Address 1}, {Phone Number 1}”. But because I have multiple linked records in a column it is instead coming up as “{Contact Name 1}, {Contact Name 2} {Email Address 1}, {Email Address 2} {Phone Number 1}, {Phone Number 2}”. Is there a way in the formula for me to limit how the information populates by linked record vs information requested?

714
L
H
Heidi_WussowNew Participant
posted in Formulas
And statement and two separate fields

Hello, I’m new to airtable and struggling to write a formula to ask the system to look at two separate fields and apply a label if the conditions are met. I have this formula already IF(IS_BEFORE({Materials Received Date (hide)},“12/16/21”),“Early Deadline”) and I’d like to add that the system also look at another field, application submitted, before 12/18/21 and also apply the label Early Deadline. Both statements should be true in order for the label to be applied. I was thinking I could just copy my original formula that worked and join them with an AND but I get an error. Thanks in advance!!

01
ScottWorld
L
Luong_DoNew Participant
posted in Formulas
How do I find the date range between different transactions?

Hello! I’m trying to build a CRM tracker that allows me to calculate the number of days between each order placed by a unique customer. So far, this format I have is great and want to build on it, but what I want is to find the day range between each order number (so for eg: days between order #100090 and #100092, and then #100092 and #100132). Have been trying for awhile and still can’t figure this out Any help is greatly appreciated!

202
kuovonne

Badge winners

  • Welcome
    youssefhas earned the badge Welcome
  • First Topic
    _Chris_has earned the badge First Topic
  • First Topic
    GegHhas earned the badge First Topic
  • Welcome
    blessing333has earned the badge Welcome
  • Welcome
    margaryhas earned the badge Welcome
Show all badges
Terms & Conditions