Formulas

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?

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”))),

Greetings, I put together a video that illustrates what I am looking for.

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?

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

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:

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.

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!

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

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!

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.

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.

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]

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,

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!

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?

Hi AirTable Community, I am trying to create an IF formula to format a number field to an abbreviated format depending on if it is 100,000 or 1,000,000… eg. “780000” becomes “780k” “1000000” becomes “1M” Purpose is because I am trying to Concat the abbreviated number together with other fields. Does anyone know how to write such a nested IF statement? Thankyou in advance!!

I have a marriage date for some people and no date for others. I created a field called THIS YEARS ANNIVERSARY as shown below. IF({Marriage Date},DATETIME_PARSE( DATETIME_FORMAT( {Marriage Date}, ‘MMMM D’ )& ’ '& YEAR( TODAY() ), ‘MMM D YYYY’ )) I need a formula for NEXT year’s anniversary. If THIS YEARS ANNIVERSARY was a simple date field, I know how to use the DATE_Add formula but I am struggling with how to use the DATE Add formula to this formula field so it is also seen as a date so I can use it as a date field in a calendar. Any help would be appreciated.

I have a measurement form. Users can add numbers into the appropriate fields (labeled number only fields). I think have a formula field that formats the numbers into something that can be readable. Each field is added on its own line in a formula field with a * by it, so the end result looks like this (sample formula below): Height: Bust: Waist: Thigh: CONCATENATE( IF({Height}, "* Height: " & {Height} & "cm\n"), IF({Head Circumference}, "* Head Circumference: " & {Head Circumference} & "cm\n"), IF({Neck Circumference}, "* Neck Circumference: " & {Neck Circumference} & "cm\n"), IF({Chest Circumference}, "* Chest Circumference: " & {Chest Circumference} & "cm\n"), This works great. However I have an “Additional Measurements” field that is a long text field, where a user can add any missing measurements. For example: Calf Circumference: Hip