Formulas

I would like users to be able to select an individual name when filling a form. I created a calculated field with the Last and First Names. But it seems I cannot add a calculated field to a form. How else can I do this? Just to clarify. If I have calculated two entries “Smith, John” and “Smith, Peter” how can I list them in the form?

Looking for a suggestion on how to best achieve the following: I have an order date, say “4th July 2018” and i have 3 workdays in which to complete the order. I’d like the formula to say “3 days left” or “2 days left” depending on today’s date. WORKDAY({Order Received},3) will give me the due date. But how to calculate the number of days left until we hit the due date?

Basically would like to do Lookup, {Products ordered} Double Scoop (Chocolate + Vanilla), Single Scoop (Chocolate), Triple Scoop (Chocolate, Vanilla, Mint) Formula field {Flavour categorizing} Chocolate, Vanilla, Chocolate, Chocolate, Vanilla, Mint The issue is that, the Lookup field can be different products at different arrangements, but would love to have the formula field to be able to SEARCH a certain product and state its flavour of choice. like the example above! Any help will be much appreciated!

This seems like it shouldn’t be that hard… SWITCH({Content Type}, Long-read, “$100 USD”, Standard-length, “$25 USD”, Photo, “$25 USD”, Video, “$40 USD”, Cartoon, “$25 USD”, )

I have a rollup field that uses ARRAYJOIN() to produce string values separated by commas. Next, I want to be able to count all instances of a particular string.For Example, the field I want to count contains: BT,DT,BT, DT. I want to count only the “DT.” Any suggestions on what formulas are able to do this? I would use a conditional rollup, but it doesn’t feature any “IF” statements.

I’ve found it extremely useful to have records with autonumber and “Title” field, while setting Name to something like "tn"&ID&" "&Title (where tn is an abbreviation for table name). Very helpful to distinguish events with similar agendas, etc. However, this means I no longer can auto-fill the name when creating records as a linked record field, from calendar view etc. and each time have to expand the record and fill it second time. Can I somehow fill a different field when creating the record?

Hi community! I am building a job site with Webflow and using Airtable as the database. Note: Job site not job board, which means the candidates will apply on the same site, instead of linking them to the company’s “jobs” page, as Job boards do. With the tutorials I saw online, including the one by @Aron, the “Apply Now” button links to an external site. With my Job site, I’m linking it to an Application form, where I need to prefill 2 things: The Company and the Position. I currently have a “Job Posts” table, a “Candidates” table, and a “Apply now” form/table. Every Job Post has an associated “Apply Now” URL which can be customized to have either the company name or the position prefilled (whichever is the primary field). The “Apply Now” form links to the “Job Posts” table and picks the primary field. I need both: the company name and the position to be prefilled into the form. How do I do this?

As you see in my lookup field, certain names (Mickey, Annie, Codi) are listed twice, since they’re responsible for multiple steps within one objective. How can I either create another field or change that lookup field so it’s displaying unique step owners (associated with steps). Thank you!

Hey team! I want to be able to show my “Current Step in Progress”, based on the information in my “Steps” table. How it should look: If there is an actual completion date, the formula should look at the next upcoming step, based on the date found in the projected completion date. In this case, the “Current Step in Progress” for the Merchandising Acceleration Program should be S2, since S1 has an actual completion date. Thanks in advance!

I have a formula where this year’s anniversary is based on the date of Marriage but it returns an ERROR if the marriage date is blank and I would like it show as a blank instead. Here is my old formula. DATETIME_PARSE( DATETIME_FORMAT( {Marriage Date}, ‘MMMM D’ )& ’ '& YEAR( TODAY() ), ‘MMM D YYYY’ ) II have tried using an IF statement and it does work for the blank marriage dates but for some reason, the ones with Marriage dates are no longer formatted as MMM/D/YYYY and instead show 2020-03-31T00:00:00.000Z. . Here is my new Formula with the wrong formatting: IF({Marriage Date},DATETIME_PARSE( DATETIME_FORMAT( {Marriage Date}, ‘MMMM D’ )& ’ '& YEAR( TODAY() ), ‘MMM D YYYY’ ),"") I can’t figure out where to move the date formatting so it shows up as 3/31/2020 Any help would be appreciated.

Hello all! I’m trying to create a formula that will extract all text from a string that comes after the last comma in that string. For example: From the string “Fashion sketch from A. Beller & Co. of a Lelong Cape, Blouse, and Skirt, circa 1926” I need to extract “circa 1926” I’m struggling to find a way to point a formula to that last comma and space in the string. Thanks!

Hey there! I have a Status Report table built to allow for all updates about each step to be created. In the Steps table, I need to create a field / formula that says "if the most recent update for each step is more than 2 weeks from today’s date (in the past), change status to “NEEDS UPDATE”. The “Formula” field in Status reports shows “true” if it’s the most recent update for the step, which is the row I’d want to reference as my most recent update. Thanks in advance for your help!

Hello! In my Objectives tab, I want to display the “Upcoming Step Due Date”, which should be calculated by using the most recent date between the Revised Completion date and Projected completion date, found in the Steps Table. If the Revised Completion date is empty (row 4), it should default to the Projected completion date. I’ll then want to determine how many weeks until that object is due. Can you help me determine 1) how to get the most recent date and 2) how to create a formula for the weeks until that date? THANK YOU!

I want to create an IF formula so that if a a calculation > 0, then the calculated positive % + “ :white_check_mark: ” should populate, but if the calculated formula is < 0, then the calculated negative % + “ :stop_sign: ” should populate. When I do write this formula, the correct number and emoji shows up, but the calculated value has many decimal places. How can I change the formula so that no decimal places will appear (e.g. 35%)? Formula: IF( (1-SUM({Apr 2020 Staffing 1},{Apr 2020 Staffing 2},{Apr 2020 Staffing 3}))>0, “ :white_check_mark: ” & " " & (1-SUM({Apr 2020 Staffing 1},{Apr 2020 Staffing 2},{Apr 2020 Staffing 3}))*100 & “%”, “ :stop_sign: ” & " " & (1-SUM({Apr 2020 Staffing 1},{Apr 2020 Staffing 2},{Apr 2020 Staffing 3}))*100 & “%”)

Hi There, I’m having a hard time figuring out how to put a value when a subscription occurs between to date : I’ve tried this :arrow_down: IF( {Date} < ‘30/09/2020’, “TPP Test phase”, IF( {Date} < ‘31/12/2020’, “TPP OND”, IF({Date} < ‘31/03/2021’, “TPP JFM”, IF({Date} < ‘30/06/2021’, “TPP AMJ” ) ) ) ) But it won’t work at all. Someone could help please ?

The formula is indicated on the screenshot. How to get Duration value, not a NaN?

IF + SWITCH formula can’t have a non-numeric value as True / False argument. If it does, the result of this formula is going to be ALWAYS non-numeric and thus can’t never be used as an input for calculation in subsequent formulas. For instance, if I have IF(Column1=“ABCD”,999999,“nonsense”)… the result of this formula is always non-numeric, no matter what the result. Even if the result is TRUE (that is Column contains ABCD), the result of the formula is non-numeric text 999999. To get numeric result, I have to change the formula to IF(Column1=“ABCD”,999999,“000000000000000”) and then I’ll get 999999 as numeric result. Same goes for SWITCH formula. This is an issue when the result of the IF or SWITCH formula is an input or variable in a subsequent formula because the result of the subsequent formula(s) produces an #ERROR! This is not a show-stopper but reduces the usability if I can’t have text “nonsense” as the result

Hi! I’m trying to convert a datetime entry from a linked field to a string as follows: DATETIME_FORMAT(SET_LOCALE(Datum, ‘nl’), ‘LLLL’) Where ‘Datum’ is a linked datetime field in another table in the same base. This goes well for e.g. ‘22 september 13:00’ but I get an error for the datetime ‘1 oktober 16:00’ (this is a datetime localized to the Netherlands - but a datetime nonetheless). The error in the field reads ‘#ERROR!’, so there’s no red warning when filling out the formula. When I try and use the exact same formula in the original table (i.e. de table that ‘Datum’ lives in), I don’t get that same error. Anyone knows how to fix this issue? EDIT: What I mean by a ‘linked field’ is that the column ‘Datum’ in the first table is ‘Linked to another record’ in a second table. The column that Datum is linked to in that second table is a ‘datetime’ column; i.e. a column that contains datetime entries.

Hi all, I’ve almost finished our CRM, but I’m totaly stuck to have a rolling forecast. To simplify it as much as possible: I have an “OPORTUNITIES” table. Each record has a “VALUE in €”, a “CREATED DATE” and a “CLOSING DATE”. I would like, on a second table named “ROLLING”, where each record is a “DATE”, to cumulate all the ‘OPORTUNITIES’ records witch are open at a “DATE” (inbetween “CREATED DATE” and “CLOSING DATE”) in order to have the rollup of all “VALUES in €” per day. –> Having a rolling forcast of my oportunities within time. Thanks a lot

Hi, I’m having trouble with the following formula: IF(LEN({Copy})>150,“ :rotating_light: ”,“ :ok_hand: ”). It returns “ :rotating_light: ” if the Copy cell quantity is longer than 150 characters and “ :ok_hand: ” if less than 150. However, I’d like it to return a blank value if the Copy cell is empty. I’ve tried adding “BLANK ()” to the formula a few different ways, but it either returns an error message or doesn’t take. Any help would be appreciated. Thank you!

Hi folks, just a quick announcement about a new formula article that walks through how to check for missing field values, and display them conditionally in a formula field. Here’s an example from the article of what the end result looks like. You can use and modify this approach for any number of workflows, so we hope it’s helpful! If you have any requests for support articles around formulas (or anything else!), just let me know :thumbs_up: Airtable - Support

Hello! I have an Airtable set up with an entire database of product for a client. Each product has a unique SKU and subsequently a record for that SKU. Every product falls within a collection of product and is tagged with that collection as well. For example 5 SKUs may fall under one Collection of product. From there each SKU (record) is tagged with features of that product. I created a table with a linked record for collections and am attempting to find a way to roll-up common features within a collection. In other words, if all SKUs within a collection have the feature ‘Waterproof’ then show ‘waterproof’ on this table. I am hoping this question makes sense.

I have 2 fields that contain email addresses. The first column contains 1 email address and the the 2nd column contains multiple email addresses. I want to create a 3rd column that combines both fields with the appropriate commas separating each email address. What formula can I use?

I have two rollup columns that each contain (potentially a lot) of text values. Rollup column 1 will most likely contain some, but not all, of the same text values of Rollup column 2. I need a way to write a formula for a third column that pulls out any unique text that exists in column 2 that does not exist in column 1 and then shows what those unique text values are in column 3. (Essentially I’m trying to find values that don’t match between the two columns, but I don’t want to calculate just a true or false statement as to whether the two columns match. I want to know what the text values are.) Anyone that can help? -Thanks

I feel like this is possible, but I’m not 100% sure. I know I can run a formula that says do “X” if the value is “Y” and otherwise do “Z”, but can that be done on whether the number is negative or positive? This is what I’m trying to do - I have a number field that captures the number of days before (negative) or after as listed in a contract. I need to calculate a date based on those numbers, but the date I calculate from depends on whether it is listed as before or after in a contract. So for example, if in the contract it says the survey is due 30 days before the end of feasibility, I need to use the feasibility date field based on that -30. If it says it is due 60 days after the effective date, I need to use the effective date field based on that 60. Hopefully this makes sense. Thank you!