Formulas

Hi! I am trying to write a conditional formula on a rollup… In table A, I have a dropdown column (“sponsored”) with multiple options, not all rows have a dropdown against them. I have table B that has a rollup column, that I would like to sum the values IF the row in table has a dropdown against it. I am using… IF({sponsored}, SUM(values)) …but it’s not working!

Thank you ahead. This is probably an easy one, but I could use the help since I’m not good at formulas or programming. Basically I am getting data from an API that is giving me IDs that need to be matched with Values. I got that happening but where the Values are Blank, I get an error, possibly because it’s trying to format it into a date. The errors are keeping it not working with some reports, so need them to result in blank() when erroring. Here’s my formula: DATETIME_PARSE((IF({Custom field items 1 id custom field}=“5a9866ffd6afbd6de1c0d07b”,{Custom field items 1 value date})&IF({Custom field items 2 id custom field}=“5a9866ffd6afbd6de1c0d07b”,{Custom field items 2 value date})&IF({Custom field items 3 id custom field}=“5a9866ffd6afbd6de1c0d07b”,{Custom field items 3 value date})&IF({Custom field items 4 id custom field}=“5a9866ffd6afbd6de1c0d07b”,{Custom field items 4 value date})&IF({Custom field items 5 id custom field}=“5a9866ffd6afbd6de1c0d07b”,{Custo

I’m trying to forecast revenue for my company. I have a table, and basically what I want it to do is tell me: If Order Date Created is “X”, and payment method is “Y”, add “Z days” to Order Date and output new date. Can’t quite figure it out. For example, if they paid with a credit card, I know their payment will settle today. But if they paid by check, I can forecast the revenue out 2 weeks or so. Anyone know how to do this?

I have a table like in Screen Shot 1. I spent a few hours here looking for a solution on how to get a column like the green one in the screenshot or another table like in the second screenshot. Any hint would be greatly appreciated. Thanks in advance , N

Hello! Currently I am trying to create a formula field to check the length of a text if it is the right length for a tweet. But I want to exclude links in the text, as Twitter always takes 23 characters for a link. So the hyperlinks in my text don’t need to be counted. Can I make a formula to count only the text which is not part of the url?

I have a field that lists jobs as “open,” “closed complete” and “closed incomplete”. I would like to calculate the number of days for open jobs since they’ve been submitted. Without conditional formatting, datetime_diff (today(), {date requested}, ‘days’ works. When I try to add in a condition it doesn’t. I’ve tried multiple variations of this, but nothing seems to work. =if({date requested} = “open”, ) (datetime_diff (today(), {date requested}, ‘days’ I’m sure it’s an additional/missing character issue, but if you can help, I’ll forever be in your debt.

I want to find out if I can convert this Community Profit Calculator Google Sheet into an AirTable. is that possible? In the basic version simply input Membership rate Current Membership Size Membership Growth (monthly goal) Membership Churn Expenses You will then get a breakout of how much profit you will make per month. The Plus version I want to be able to allow multiple Membership based revenue options (I have counted 21!) I have other ideas on how to expand including being able to sell that individual Airtable via https://paytable.io/ A future idea includes being able to indicate how many members I want in 12 or X months in the future, then the output will divide the total by the number of months to reach goal and

Thank you Airtable team! I love the new Formular formatting you have implemented. It really helps, when writing longer and complex formulas!

Hello! We use this formula: =REGEXEXTRACT(IMPORTXML(A1,"/html/head/meta[@property=‘article:modified_time’]/@content"),"^(.+?)T") on Google Sheets to fetch publish date information from a URL (A1, in the formula). We would like to do something similar on AT but I can’t for the life of me figure out how AT would write this out as a formula. Any tips?

I can’t open my formula field. Every time I double-click on the field name, it just pops up a banner saying “An error has occurred” and forces me to refresh the page. This happened to my base after the formula formatting update today. Strangely this only happens to one table in my base. Other tables are working fine. I attached a video recording of my screen below. Any help will be much appreciated! Thanks guys. https://cln.sh/PpmNkX

Any way to use spintax in formulas to create concatenation texts that are a bit more dynamic?

Hi all, i am trying to reproduce an exel formula that calculates the Minimum Sample Size based on some values in Airtable. the formula in excel is like this: =IF(AB2;ROUNDUP((1,644853625*(2I2(1-I2))^0,5+0,8416212327*(I2*(1-I2)+(I2+(I2AC2))(1-I2-(I2AC2)))^0,5)^2/((I2AC2)^2);0)*K2;0,1) Reference: AB2 = value from call (TRUE/FALSE) I2 = 10% (value from cell) AC2 = 20% (value from cell) K2 = 2 (value from sell) I am stuck with the use of “^” (power) any suggestions? Thanks in advance. Regards Maichel

Hi there! I’m looking to calculate the growth rate of attendees for events that I run. Specifically, I’d like to understand how many attendees are first timers. To spell out how this formula could work… I see x variables Attendee Attendee attended event date list Event Event dates I’d like a sum of how many attendees attended this event if it was their first time (had not attended events prior. if ( {Attendee} has NOT(BEFORE({attended event date list}, {Event dates})) This is not really working… One of the problems is that the rollup of events attended is just a list of dates, it’s not running through the formula one by one with the attendees. Any advice on how this could work? I have two linked databases with Attendees and events.

How can I count the number of occurrences of a character in a paragraph? Example: I want to check the number of occurrences of commas in a paragraph: “182-DISPLU6-Profile 3-disneytest04@gmail.com, 161-DISPLU6-Profile 1-disneytest01@gmail.com, 162-DISPLU6-Profile 2-disneytest02@gmail.com” I want the return result is 2

I had a formula that was working until this week and has now broken for some reason. I looked into the basics (did the field names for the formula change, synced fields change, anything like that) but literally nothing about the components of the formula broke, so I’m unsure why it’s now resulting in #ERROR rather than the result. Formula below, it’s honestly incredibly basic. Any ideas for simple things I could be missing? DATETIME_PARSE(ARRAYJOIN({Release Date_static}), ‘MM-DD-YYYY’) Where {Release Date_static} is a lookup of a single line text field with a date in ‘YYYY-MM-DD’ format.

Hi! Anyone have any solutions on how to create a formula that would allow me to input a list of say 10 people and then create all possible pair combinations without creating duplicates? Example: Name 1 Name 2 Name 3 Name 4 Name 5 Then I would want to create 10 groupings where each name is paired with each other at least once without duplicating. I’ve tried using autonumber, but can’t seem to figure out the formula to create this.

Hello everyone, I receive in a single text field this kind of data: +1 * 1+0 * 2+0 * 3+0 * 4+0 * 5+0 * 6+0 * 7+0 * 8+0 * 9+0 * 10 or +1 * 4+1 * 5+0 * 6+0 * 7 This is the number of kilos that needs to be sent in the appropriate package. I wonder how I could retrieve the 1*1 from the first line, or, for the second line, 1 * 4 and 1 * 5 I was thinking about some regex expression but my knowledge is poor in that field. Anyone would have an advice? Thanks in advance

How can I replace decimal number with two decimal places to hourly like number. 2.42 > 2:42h

Hi Trying to get a prepopulated airtable form URL to prefill a certain field, per this link. The problem is that the form is generating a new record, not updating the old one. Do I need to use a separate sheet for the submissions, is this causing the issue? The formula used is CONCATENATE('https://airtable.com/shrxxxxxxxx?’, ‘prefill_StatusC=’,{Record ID}) Record ID = formula field = RECORD_ID() StatusC = Single select field which is also included in the form

Hey guys, So I have a freight-orientated base, where I record all loads driven by particular collaborators (drivers). So that means I have a list of facilities and warehouses where those drivers are delivering particular loads to. So, I would like to give drivers the opportunity to rate those facilities, to write comments for that particular facility/warehouse and choose some predefined options from “Multiple select” fields. Also, I would like drivers to have the option to fill their loading/unloading time for that particular facility/warehouse. And all that I mentioned above I want to show as average numbers in my base, so later on drivers can see average rating for that facility, and their previous ratings. Is that even doable? Thank you

Let’s say I have a multi-select column called “Colors.” And I have a record with the Colors value: Red, Yellow, Blue I’d like to create a new column called “Tags” with the string: “Color: Red, Color: Yellow, Color: Blue” Is it possible to access the column name in a formula?

Getting into formula’s a bit more as a novice lately, but struggling to get this one right. Anyone able to help me out on this one? What I want is to change the Multiple Select Field as per below. small instead of s average instead of a tall instead of t Was thinking in the direction of wrapping the exiting formula in a Switch( ) to change the select options small, average and tall to s, a and t for the the output.

Anyone can help me make a formula to write out the below numbers in a Single Line text field (A) that are styled like below, to full digits in a Formula Field (B)? Field A 1 10 100 1k 10k 100k 1.1k 10.1k 100.1k 1M 1.1M Etc. So far I’ve come up to this formula. But it’s very lacking. SUBSTITUTE( Views, REGEX_EXTRACT({Views}, "\.\d+k"), "00") It outputs 100 when its 1.7k and doesn’t work at all for entries like 1 to 999 and 1M upwards.

I am looking to take an integer and add the number to a start date. I need to have weekends excluded from the date add. Please see the formula I am currently using that isn’t generating the result I need: IF({Workflow}=“Manufacturing”, DATEADD( DATEADD({Start Date}, {Estimated Production Days}, ‘days’), SWITCH( WEEKDAY( DATEADD({Start Date}, {Estimated Production Days}, ‘days’) ), 0, -1, 1, -2, 0 ), ‘days’ ))

I have a formula that groups together transactions within each month. Formula is: DATETIME_FORMAT({Pickup Date}, ‘MMMM’) I need to sort the months in chronological order instead of alphabetical. Is there a concatenation or formula that will return this result?