Formulas

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

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 amou

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 t

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 +

SOS!!! I just need to know when to do a mid-evaluation for an employee…so I need this formula to tell me when they are three months in. Thank you :sweat:

I have a short text field with a record containing the text ‘Açaí Bowl.png’ I have another formula field that strips the file extension from text in this field, but instead of the expected result of ‘Açaí Bowl’, I get ‘Açaí Bong’. :smiling_face_with_halo: Somehow the last two characters of ‘Bowl’ are transforming? Here is my formula text, which usually works fine: IF( OR( RIGHT({File name}, 4) = ".jpg", RIGHT({File name}, 4) = ".png", RIGHT({File name}, 4) = ".pdf" ), REPLACE({File name}, LEN({File name}) - 3, 4, ""), IF(RIGHT({File name}, 5) = ".jpeg", REPLACE({File name}, LEN({File name}) - 4, 5, ""), "..." ) ) Does it behave the same way on your base? Is there a way to prevent this? Not a huge deal since not a common case but it would be nice to know.

Hi community, I need some help in regards to triggering a reminder for an inventory base. I’ve got it working for a 1 month basis but needs to be recurring every month. For example: I receive a product that has 9 cartons, on a date 11/20/2021 (Date Received), but i charge a storage fee every month this product doesn’t get shipped out. I set a 1 month deadline date of 12/20/2021 (Storage Deadline). I have a column that has # CTNS (what originally was received) and a column that shows CTNS SHIPPED and CTNS LEFT My formula is as follows: IF({Storage Deadline}, IF( AND({Date Received},{# CTNS} - {CTNS SHIPPED} > 0,{Storage Deadline} <= NOW() ), "Trigger Reminder" )) My automation runs only when the deadline reminder column displays “Trigger Reminder.” Was wondering if there is a way to get it to trigger eve

I was wondering if someone was able to help me out. I am looking use a formula to calculate the amount of days until an item has to be renewed. What I would like is that when it’s nested within an IF statement it is calculated as a VALUE rather than a STRING. The reason for the IF statement is that when the Expiry Date is empty, no value will show in the “Days Remaining” Field. Here is the formula: IF({Passport (Expiry)}=BLANK(),’’, DATETIME_DIFF({Passport (Expiry)},TODAY(),‘days’)) I’m likely missing something really obvious in this regard.

Hello guys! I would like help to do a subtraction according to the values ​​that are being established. The situation is as follows: I work in credit format with my clients, so they start the month with 8 credits, as they request me, I establish the value in credits and these are consumed. Because of this situation, I would like to create 3 columns, 1 column “Cost in credits”, 1 column “Remaining credits” and 1 column “Total credits”. As I establish the cost of each material, I would like the column “Remaining credits” to be the result of “Total credits”-“Cost in credits”(for each row). I know Airtable has a different focus than spreadsheets, but the point is that I’m really enjoying the platform and I’d like to centralize as much activities as possible here, my company is just starting out, so splitting things across multiple platforms ends up messing up the flow of work. If there is a way to do what I said, I would be very happy. Thanks in advance!

I keep receiving an invalid error when trying write this formula: IF({Close ID} != “”, "imported”, IF(AND({Commercial?} = “yes”, {Close ID} = “”), "ready to import”)) field 1 is a single line text field and field 2 is a single select field. Both If statements work when using them separately. Any help would be much appreciated!!

Hello, So far a big fan of the product, however I am a bit puzzled how to tackle this problem. I have a customer list which are either ‘active’ or ‘in-active’; based on their plan. I would like to make an overview which automatically calculated the monthly recurring revenue from people with an active plan. Currently I set manually the active & in-active date. So my logic was as followed: January 2021: IF ‘month= 01’ & ‘year= 2021’ is WITHIN ‘active date’ AND ‘in-active date’ THEN SUM ‘Monthly recurring revenue’ To add would like to have also a list of the amount of active customers: January 2021: IF ‘month= 01’ & ‘year= 2021’ is WITHIN ‘active date’ AND ‘in-active date’ THEN COUNT ‘Customers’ As I am not an experienced Airtable user; would like to know if someone can help me. Since tried a few things; but I seem not to find my way

Hello to everyone, I really need to know if some one could help me, I made my formula but ir give me errors, I let you my formula to be reviewed: https://airtable.com/shrPSoWMs4e0xYtGzprefill_nombres=receqiENsD9NZtYwT I am not sure how to get the correct syntaxis. Regards

I wrote a formula to add today’s date when a specific text appeared in another (single select) field. But it doesn’t hold that date; it changes each day. Is there a way to freeze that date to when the text first appeared? Thanks in advance!

is there a formula to count the number of days between two date fields?