Formulas

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

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

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!

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.

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

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?

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

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?

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?

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!!

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!

Hello all, I’m trying to figure out a clean way of writing a formula for a Lookup table that can hold up to 4 variables at any time (A,B,C,D). Based on these variables, I want the formula output the following: IF(Lookup contains: “A or B or C or A,B or A,B,C” - in any given order), output “Inhouse”, but if D is also present, still output “Inhouse”. If only D is present, then output “Foreign”. Is there a clean way to write this? Thanks so much in advance!

I am trying to build a table for my husband’s business that will show the commission earned for each of his employees. He has a column for Grand Total of Sale, Salesperson Commission %, and Salesperson $. He would put in the Grand Total and the Salesperson commission percent but needs the Salesperson $ field to populate. I know this is a very easy formula for all of you but I am obviously new to this. Thank you in advance for all your help.

Hello everyone, I am looking to create a formula to match candidates and job openings. Each “job” & “candidate” have hard & soft skills wich are tags (found each in separate tables)in our base. The goal is to create a function helping us find which candidates have the most tags (skills) in common with the tags found in the job descriptions. Would love some help from the community.

I can’t find any examples of exactly what I’m trying to do. It’s very easy to do in a spreadsheet and possible to build with queries but I’m trying to figure it out in Airtable. I’m a charity distributing funds to organizations across regions first and by size second. So a donation needs to be put into a regional designation first and then a size grouping and then divided amongst the orgs that qualify. What I’ve done so far is make a table with all the distributions and each field represents a Region & Grouping. I’ve labeled these RGIDs (Regional/Grouping ID). How do I put the dollar amount in the table into the proper RGID field? Let me know if I’m describing it poorly!

Hi beloved Airtable Community! I’ve got an issue with a VALUE formula. I have a Rollup field that does a ARRAYUNIQUE job from a Lookup Field which is formatted as a % (i.e. 36%). The output I get from the Rollup field is a string (i.e. 0.357142852346992). I tried various ways to convert it back to a number like: Within the formula of the Rollup, try VALUE(ARRAYUNIQUE(values)) Create a new formula field that performs VALUE(ROLLUP FIELD) But both outputs give me “#ERROR” I’ve read some articles about VALUE behavior but not sure to understand how it might help in that particular case. Would you have any hints? Thanks a lot, appreciate it!

We have created a scenario that I would greatly appreciate help on. The summary of the need is that we have a table that lists conversion data for each of our SKUs at our store (conversion data=if an item is sold in a case pack of 12 bags, then we need to convert the sale of 1 case into a total number bags we need to create…1 case coverts to 12 bags in Airtable Table. We have 75 skus and need to be able to automatically calculate total bags per SKU. Table 1 (current, This is a reference table and rarely changes) We have a table setup that defines the following: SKU in Column A the converted number for that SKU in Column B Table 2 (current) We also have another table that has raw sales data imported each day with the following columns: Order Date SKU Quantity (which, in this case, would be 1 case…hence the need for a conversion process) Table 3 (proposed) In a final 3rd sheet I would like to have the following display and be filterable based on Date Range (between 2 specific dates

Hi all! Thank you so much for helping with this math! I keep trying but short on solutions in how I’m thinking about this. I am attempting to write a formula that will assign Paid Time Off (PTO) to an Employee based on Start Date and consuming our Business Rules for assigning PTO. We have a Field labeled ‘Employee Type’ with values Hourly or Salary. Salary returns the result “Open PTO” and Hourly needs to do the math. Hourly PTO Rules Year 1 = 80 Hours PTO Year 2 and 3 120 Hours PTO Year 4 and beyond 160 Hours PTO Optional Override Field {PTO Override} - if a value is entered here use it and ignore the Yearly rules above. Ideally when an ‘Hourly’ employee ‘Starts’ they will get Annual PTO prorated up to 12/31/YEARNOW(). And the balance of their first 80 hours commencing on or before the {StartDate}. Then the pro-rated amount of Year 2 (120 Hours) up to 12/31/YEARNOW() +1. In other words, an employees Annual (1/1-12/31) PTO amount will be the combination of the remaining amount fro

Hello! I’m not great with formulas, but I’ve spent hours on google and airtable forums trying to figure out how to do this. Let me see if I can explain this well. I have a string of text and I’m trying to find the first name of the person listed in the text. The text looks something like this: Hi Stylist, You have a new reservation. Customer First Name: John Customer Last Name: Doe Customer Phone #: 1234567890 Customer Email: johndoe@gmail.com Location: Salon Date: Thursday, December 9, 2021 Service: Package 1 Employee: Stylist Time: 1:45pm Yours Truly, The Team I just want to grab the First name only from the string and I can’t for the life of me figure out how to format the formula. Any help would be much appreciated!!

Hello guys! I would like help creating a “Monthly Billing” table. The question is: I have a table called “Jobs” where I add all the jobs that my clients ask me for, this table contains several columns, including a “month/year”, and also a column referring to the value of each job requested. Now I would like to create a table referring to the “Monthly Billing”, containing the “Gross Monthly Billing” and the “Net Monthly Billing”. In “Monthly Gross Billing” I would like it to be the sum of the value of all jobs referring to month “X” in the “Jobs” table and in “Monthly Net Billing” I would like it to be {Monthly Gross Billing} - all values ​​from column “Cost/Month” of a third table called “Cost of Operation”. It would be very important that this table called “Monthly Billing” could be organized by Month. I’m new to Airtable so I’m still learning how it works, thanks so much for all the help. I put some pictures of my tables to make it easier to understand.

Hi, I am trying to get a record to delete after 24 hours. I have copied a formula from another thread and changed the time from hours to minutes. I have no idea about scripting so wasn’t able to implement the second part of that thread. I currently have the “delete” column set to add the word “delete” to the formula cell after 1440 minutes. This then updates another view filtered by “if delete is empty”. In other words, if delete is empty in the new view the record will show on the front-end currently Softr. This formula seems to work but it’s sluggish and doesn’t update on the 24-hour mark. IF(DATETIME_DIFF(NOW(), CREATED_TIME(), ‘minutes’) > 1440, “delete”) My long-winded and probably not well-explained question is: How do I auto delete a record after a 24 hour period? Thank you.

Help! I keep pasting this formula into Airtable, and it deletes it after the 5 Item section. Even if I can get it to “take” the 7 and 10 item part of the formula, it says there’s an error. Where is the error? IF(AND({Number of Items}=5, {VA Recipient?}),((65-{Item Discount})*1.06)+{Shipping Total Cust. Paid}, IF({Number of Items}=5, 65-{Item Discount}+{Shipping Total Cust. Paid}), IF(AND({Number of Items}=7, {VA Recipient?}),((85-{Item Discount})*1.06)+{Shipping Total Cust. Paid}, IF({Number of Items}=7, 85-{Item Discount}+{Shipping Total Cust. Paid}), IF(AND({Number of Items}=10, {VA Recipient?}),((100-{Item Discount})*1.06)+{Shipping Total Cust. Paid}, IF({Number of Items}=10, 100-{Item Discount}+{Shipping Total Cust. Paid})))) This is trying to say: If there are 5 items and a VA Recipient, then do X. If there are 5 items and NOT a VA Recipient, then do y. Then it repeats for 7 items and 10 items. Thoughts? Thank you all!!!

Hi, this is my first post, and forgive me because I’ve been newly converted to Airtable from notion and excel. I’m trying to create a formula, but my problem is that some cells are returning ERROR or negative numbers, which makes sense because in some cases the roles are ones without a physical start date, or their start date is in a future period. In excel I’d normally overlay this up with an iferror and also nest the future start date, but it does not seem to work in the same way. I’d appreciate any help with this. Thanks! IF(({Annual Base}/12/WORKDAY_DIFF(‘2021-12-01’,‘2021-12-31’)*WORKDAY_DIFF({Physical Start Date},‘2021-12-31’))>{Annual Base}/12,{Annual Base}/12,{Annual Base}/12/WORKDAY_DIFF(‘2021-12-01’,‘2021-12-31’)*WORKDAY_DIFF({Physical Start Date},‘2021-12-31’)) .

Here’s my entire formula: WORKDAY_DIFF({Start Date}, {End Date}, ‘2022-01-01,2021-12-25,2021-12-24’) Works great, but I would like it to only execute if the Start Date and End Date have been filled in. I’m trying to suppress the #ERROR that shows up in the cells otherwise. Thank you!

Hi everyone, I try searching the numerous existing posts but I couldn’t find my answer. Here it is, I need to migrate a database from Notion to Airtable and I’m not sure if the formulas in airtable enables me to do the same as the existing Notion one :slightly_smiling_face: Having a formulas counting different multiple select occurences and adding text within the results too (cf screenshot) So the idea, as a result, is to get 1 line per language with the total of the different languages occurences: Français : 4 Espagnol : 5 Anglais : 2 Etc. I tried something like IF(LEN(SUBSTITUTE(Maternelle, "Français", ""))>1,"Français:"+(LEN(Maternelle) - LEN(SUBSTITUTE(Maternelle, "Français", ""))) / 8,"") But I get NaN as a result → does it mean formulas can’t have calculation + text as a result ? and doing it for several languages IF(LEN(SUBSTITUTE(Maternelle, "Français", ""))>1,(LEN(Maternelle) - LEN(SUBSTITUTE(Maternelle, "Français", ""))) / 8,"")+IF(LEN(SUBSTITUTE(Maternelle, "Espagn