Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,227 Replies
5939 Topics
A
Alex_CristianParticipating Frequently
asked in Formulas
Formula IF/OR statement for multiple item conditions

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!

32
A
L
Lynn_WoodNew Participant
asked in Formulas
Salesperson Commission Formula

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.

62
L
P
Pierre_TillemenNew Participant
posted in Formulas
Matching jobs and candidates

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.

114
J
B
Benjamin_WeatheNew Participant
posted in Formulas
Cross-referencing fields in a table to a single field in another table

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!

83
Justin_Barrett
O
oceanseleven11Inspiring
asked in Formulas
Value function returning Error

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!

132
O
S
Seth_StevensNew Participant
posted in Formulas
Calculate totals based on column data across 3 Tables

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

61
Justin_Barrett
M
molasscoeNew Participant
posted in Formulas
Calculating Annual PaidTimeOff based on StartDate, WorkingDays, Holidays and Company PTO Rules

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

121
Justin_Barrett
E
Emily_PowellNew Participant
asked in Formulas
Formula to find a name within a text string

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

384
E
B
Blending_ProducKnown Participant
posted in Formulas
Creating a “Monthly Billing” table

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.

558
B
N
NoCode_NerdKnown Participant
posted in Formulas
How can I auto delete a record after a specific amount of time

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.

14212
N
T
TailoredTouchesParticipating Frequently
posted in Formulas
Nested IF AND formula

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

156
Kamille_Parks11
R
Rob_Lee1New Participant
posted in Formulas
If error and negative

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’)) .

81
Justin_Barrett
G
Gene_EllisNew Participant
posted in Formulas
Execute the formula only when the fields are not empty

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!

31
kuovonne
E
EricB11New Participant
posted in Formulas
PB with formula with multiple results + text (Notion like style) - is it doable with airtable?

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

31
E
C
Christine11New Participant
posted in Formulas
Need a formula that generates the 90th day worked from the start date

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:

31
Kamille_Parks11
S
Sticker_ninjaInspiring
posted in Formulas
Formula has bizarre interaction with accented characters

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.

282
Kamille_Parks11
D
dustin_leeNew Participant
posted in Formulas
Trigger recurring automatic email sent based off a deadline date

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 every month assuming the product has not been fully shipped out.

152
D
J
Jarryd_WilliamsParticipating Frequently
asked in Formulas
Date String: Number of Days as numeric value

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.

32
J
B
Blending_ProducKnown Participant
asked in Formulas
Subtract according to row values

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!

7613
B
N
notifications_BNew Participant
posted in Formulas
Trouble using nested IF formula with AND function

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

72
N
R
Roy_SelbachNew Participant
posted in Formulas
Overview per month MMR calculation

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

231
Justin_Barrett
G
Giner_OrdonezNew Participant
posted in Formulas
Update records by formulas

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

31
Justin_Barrett
G
Gordon_RothmanParticipating Frequently
posted in Formulas
Enter and maintain today's date?

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!

94
G
G
George_ZaloomNew Participant
posted in Formulas
Counting Days between two Date Fields

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

116
A
M
Mitchell_BarberNew Participant
asked in Formulas
Expiry date status - How can I add a "Not Expired" status

Hi All, I’ve got a formula that tells me when a job post is about to expire, when it is expiring today, and when it’s expired. I would like it to display an “Acvite” status for all other days, is this possible within this formula? Any help would be appreciated! IF(IS_AFTER(TODAY(), {Expiration Date}), “ :x: Expired”, IF(IS_SAME(TODAY(), {Expiration Date}), “ :firecracker: Expires Today”, IF(IS_AFTER(TODAY(), DATEADD({Expiration Date}, -3, ‘days’)), “ :raising_hand_man:t2: Expires Soon”))),

91
Kamille_Parks11

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