Formulas

I have a formula field that calculates the difference between {End date} and {Start date}. Works fine If there is no Start Date or no End date I want the cell to be blank instead of NaN Been trying everything. HELP!!

I have scoured the community and keep bumping up against an error when trying to create my formula for a SKU number. I have multiple product categories: Mugs, Berry Bowls, Plates, etc. I would like to have a SKU number that displays the current year, current month, a truncated category, and the autonumber field. The categories will change to a two or three letter combo, like Mugs becomes MUG, Berry Bowls becomes BB and so forth. The SKU should end up like this: 2022-03-BB-00001 I’ve tried the SWITCH command, but kept getting an error. I don’t currently have a date column, but do I have to have one to include the year and the month? Also, the autonumber column doesn’t add the zeros. How can I get the autonumber field to display a 5 digit number? Thanks in advance. Lisa

Hi, Does anyone have a formula to format a date to include the “st” “nd” “rd” “th” ? For example, I would like 3/21/2022 to read as Monday, March 22nd I can only figure out how to do Monday, March 22 Thanks! Tom

How do I convert the function “=CONCATENATE(REPT(0, 3 - LEN(A1)), A1)” used in Excel into an airtable formulas?

We have a farm and grain processing facility. We installed an on farm truck scale a year ago and have been manually filling out scale tickets, bills of lading, and a scale log. I want to set up a system in airtable as a scale log with the following data: Unique ID Date Origin (Name, Address, City/State/Zip, Phone, Email) Destination (Name, Address, City/State/Zip, Phone, Email) Product Status (Organic/Transitional/Conventional) Lot Number ******* (more later) Gross Weight Tare Weight Net Weight Incoming Moisture Product Target Moisture Moisture adjusted weight (this is a formula we are already using) Scale Operator Notes From that I would need to be able to generate: Scale Ticket Bill of Lading these 2 items I’m guessing will something like “Page Designer” Most of this is straightforward, what I’m having trouble with is trying to set up a formula to automatically generate a lot code base

I have up to 3 fields that I want to merge together and separate each field with a comma, but only if the field is not empty. If any of the 3 fields are empty, I don’t want them included so that I don’t have extra commas in the resulting formula field. Any ideas how I achieve this?

Hi, I am trying to create a sum up view form my services sales. I provide only 7 services but I sell them always with different price and expense. This is what I have: Table 1: Service - Service (Service name + reference number of each sale), reference number of each sale, Sale date, Sale month (from Sale date), Price (different for each sale!) Table 2: Expenses - Name of expense (Service Name+date of expense), date of expense, amount paid Table 3: - Service name - linked to Expenses and Service table, where I rollup all sales and expenses for each month (using conditions) then I can get net profit. The problem is that every new month I have to create new conditional roll up to get monthly sale, expense and net profit. Is there any way I can create this reports automatic? Creating views in ech table won’t be enough for me as I can’t get the net profit values using only views :confused: Thank you all in advance! Kamil

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