Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,949 Topics
  • 18,277 Replies
5949 Topics
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.

312
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.

172
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.

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

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

82
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

251
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?

156
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
N
Nathan_VandermoNew Participant
posted in Formulas
Best Formula Configuration

Greetings, I put together a video that illustrates what I am looking for. Transcript of video: Greetings or table community. So what I’m trying to do is I’m trying to have this column, the leads balance column reflect the amount of leads that are left. So we can see that the, each, each row is a new lead and it’s organized by the date. And we can also see that we have our two columns here, requests credit and credit issued. So the workflow is that a client would potentially request a credit just by selecting the column and by selecting. Yes. And if they select, yes, then I ideally would get an email notification and then would have to request a credit. Once the credit is approved, I would place a one in this column. And then that would show the balance here. So in the, our leads balance, I’m using the customized field type, I’m using the formula and I’m trying to figure out what formula to use. I’m used to Excel some, having some difficulty trying t

113
Justin_Barrett
R
RVBNew Participant
posted in Formulas
Replace text using a regular expression

Hi! I’m new to regular expressions and I want to create a formula in Airtable which switches the text left from the ‘-’ to the right and visa versa. So U27Y11/12-U13Y07/08 needs to become U13Y07/08-U27Y11/12 Basically swapping the right and left side. (the ‘-’ is always the separator) Can you guys help me out?

234
R
E
Edward_ZevNew Participant
asked in Formulas
Multiple choice field as a parameter in the IF formula

Hi everyone So I have this simple IF formula IF({Method}=“Digital”,1,0) Where {Method} is a multiple choice field, and can contain different type beside “Digital”. My problem is that this formula returns 1, only when {Method} is strictly equal “Digital” and nothing else. And I want it to return 1 if one of values is “Digital”. For example: Digital, Offset, Heatpress

92
E
G
Ghazi_ShahNew Participant
posted in Formulas
Formula for applying a condition based on a Single Select options of another cell

Hi, I’m setting up a bookkeeping system for my business. What I need is that when I select the option “Expense” from the Single Select option in the Type field, I want to add a negative sign with the Received Amount field so that it is subtracted from the sum. I’m attaching a screenshot for clarity. I’d really appreciate it if someone can help me with the formula for this. Thanks a lot :slightly_smiling_face:

11
A
Burner
BurnerKnown Participant
posted in Formulas
Extract text from dynamic string

Hi, I’m trying to come up with a formula that extracts the first name, last name, and email that I’m getting into Airtable from another application. The format of the UID I’m receiving is shown in the couple of examples below: John_Smith_john@example.com Tracy_Jones_tracy@example.com Basically, it’s going to be first name underscore last name underscore email. I need to write three formulas, in my Table for the First Name, Last Name, and Email columns. I’m not sure how to proceed especially because the UID string length will be dynamic based on users’ names and emails, so I can’t use a LEFT or RIGHT formula to count and extract the text. I know there should be a way to do this, but could really use some help with the formulas. Thank you.

31
A
R
RibbletonNew Participant
posted in Formulas
Formula help: 3 different columns

Hi! We’re trying to figure out what formula is to be set in place for this scenario: We have three forms, all connected to autopopulate on Airtable. Column A = Form A Column B = Form B Column C = Form C Basically, what we have a column with the formula that determines what form a client has filled out based on what Column has been autopopulated - let’s call it Column X. What we want to happen is = if Column A has been populated, Column X says “Form A”; if Column B is populated, Column X says “Form B” and so on. Only one column will be populated at a time per row (ex. Row 1 Column A populated, Row 2 Column B etc), so I hope that makes the formula creation easier. Thank you in advance!

155
R
G
Gregor11New Participant
posted in Formulas
Delete repeated words

Hi! Is it possible to use a formula or function to remove repeated words in a sentence?

41
kuovonne
D
Declan_OwensNew Participant
posted in Formulas
Clean and convert text value into currency value

Hi there! I have an AirTable sheet where I am collecting data from a form colleagues use. One field in this form is monetary values, but I’ve switch the receiving column to a text format because users don’t always enter integers only and I have no way to constrain the field in the form. Values can be ‘$100’, ‘100USD’, ‘100 USD’ when what I need is ‘100’. So what I’d like to do is still receive the data in text format column that accepts all entries, and then grag this data, cleanse it, and keep only the numbers, to have an end result that’s an integer. So ‘100USD’ would become ‘100’. I’ve looked at formula documentation but I can’t figure out what to do, would anyone have an idea that could help me please? Thanks!

262
Kamille_Parks11
A
Angela_CreativeNew Participant
posted in Formulas
List all dates in a period that falls on the given DOW

Hi there. I’m a newbie to Airtable formulas I have a field {DOW} which holds a value like Thursday. I also have values in a different table {Start Date} and {End Date} based on a foreign key. How do I obtain a list of all the dates between {Start Date} and {End Date} that is the given {DOW}. Eg Start Date is 28/Jan/2022 and End Date is 8/Apr/2022 and DOW is Thursday. I want to find all the Thursdays between the given dates. The list may be stored in 1 field (preferred) or have 1 date stored per field (hence having 10 columns for the 10 Thursdays found in the date period). Thank you.

61
Justin_Barrett
J
Jennifer_FelkerNew Participant
posted in Formulas
Concatenate Adding Unwanted Quote Marks Around Linked Fields Containing Commas - Solution

PROBLEM In our TABLE 1 (PROJECTS): We want our concatenated field to read: P1234 (Joe Smith - Smith, Joseph & Jones) What we get: P1234 (“Joe Smith - Smith, Joseph & Jones”) Where: Joe Smith - Smith, Joseph & Jones is a linked field to our TABLE 2 (CUSTOMERS) table. WHY: Airtable is concatenating a linked field that contains one or more commas, and for whatever reason surrounds the entire string with quote marks. You should see that any concatenated fields containing linked fields without commas do not show the quote marks. EXAMPLE DATA / FORMULAS (Solution Below): This example shows Table 1 concatenating Table 2’s primary field (which is also a concatenated formula). Even if your primary field in Table 2 is a write-in field containing one or more commas, the result & solution are the same. TABLE 1: PROJECTS Column 1: Project (formula field: Concatenate({Project Number}, " (", {Customer}, ")") This is the field that will show the unwanted quote marks beca

180
J
J
Jesse_GroenewolNew Participant
posted in Formulas
Summarizing number of each type of unique item

Goal: take Rollup results, and summarize the unique values with a count of each unique value. For example, instead of showing [10, 10, 10, 2, 2, 1, 1, 1, 1, 0.5], I want to show [3 x 10, 2 x 2, 4 x 1, 0.5]. Or, if needed, I can show [3 x 10, 2 x 2, 4 x 1, 1 x 0.5]. Or, similarly, instead of showing [carrot, carrot, carrot, potato, potato, onion], I want to show [3 x carrot, 2 x potato, onion]. The ARRAYJOIN seems to be working as designed so far, but I’m hoping to summarize the results in a way that is a bit more user-friendly. I suspect there is some way of using the ARRAYUNIQUE function to help with this, but I haven’t found a solution. PS: this is my first post here, so feel free to let me know if I’m doing anything wrong and I’ll work to correct it. [edits: spelling correction; new example and explanation]

162
J
J
Joe_PepperNew Participant
posted in Formulas
How to find the record with the maximum entry in a certain field

How do I find the record with the maximum entry in a certain field. Note, I do not want the maximum NUMBER in this field, rather the record it corresponds to. For more info: I have a list of names, with how many tests they conducted. I want to find who has done the most tests. However, doing this once isn’t enough. I want to be able to replicate this given certain conditions. For example, of all the people with a specific bank (I have a field for this too), who has completed the most tests? I’m more than happy to provide more info if needed,

51
Justin_Barrett
D
DalkerNew Participant
posted in Formulas
Returns data from another Table with condition

Hi, I need to insert data in a column of table 2 in table 1, automatically, if there is the same value (text type) as the NAME of table 1 in table 2. I’ve tried some formulas and I couldn’t. Please help me. Attached sample images! Thanks!

31
Kamille_Parks11
E
Edoardo_StefaniNew Participant
asked in Formulas
Formula to convert date to numbers

I extract only year from date field with DATETIME_FORMAT(Date, 'YYYY') and assign it to new field. If I order by that field it isn’t recognized as number, but as a string (the option is A-Z or Z-A). How can I change it to a number?

331
ScottWorld

Badge winners

Show all badges
Terms & Conditions