Formulas

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

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. TABLE 1: PROJECTS Column 1: Project (formula field: Concatenate({Project Number}, " (", {Customer}, ")") This is the field that will show the unwanted quote marks beca

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 Circumference: I am wanting to know if I can add a * to each new line like in the other formula? The above user in

Hello Everyone, Maybe somebody form the community will help with something I’m struggling with. I’d like to make an API call with a Filter formula on a lookup field, which includes comme separated dates, see below : As you can see, there is a simple calculation field that is : IS_BEFORE(TODAY(),Booked_Dates) that doesn’t seem to be very happy with the multiple item field on Line 1. However, when I’m filtering lookup fields with conditions in Airtable it seems to work perfectly fine (don’t notice both conditions, only the second one): And the result : So my question is : Is there a formula I can use to have the same behavior than Airtable’s lookup filter so I can make the appropriate API call in the FilterByformula query ? Or is there any workaround that one of you might suggest ? The idea here is to look for an available timeframe in a booking system… Thank you for your help. Best, D

I am making an automatically repopulating task list. I can’t use the Workday function because we work on Saturdays and have a non-standard holiday schedule. The formula I am using checks a field which adds days based on a daily, weekly, monthly assignment to the old due date. I have a formula that successfully checks that new date against the holiday string rollup, but it can only add 1 date at a time- so if there are multiple holidays in a row, it doesn’t recognize that. Is there a way to make this formula keep checking the Date Calculation 1 sequentially against my rollup of days to skip until it reaches a workday again? IF(FIND({Date Calculation 1 String}, {Skip String Rollup}&""),(DATEADD({Date Calculation 1}, 1, ‘day’)), {Date Calculation 1}) I’m guessing I need to change the Dateadd value of 1 day, but I’m not sure how. Any help is appreciated.