Formulas

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 determine the monthly profits from there. Thoughts? Can it be done? If so, how? Thanks let me know if you need more info to answer question.

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?

I have a “Next Year End” date field for each of our clients and then I have fields for each of the 4 quarters to populate dates in various other tables. I am trying to create a formula that will take automatically choose the last day of the month when I use this formula: DATEADD({Next Year End}, 6, “month”) - it basically brings me to October 30th, not October 31st. Does anyone know what I’m missing? I’ve been looking everywhere in here…It doesn’t seem like this should be so difficult unless I’m totally not understanding :slightly_smiling_face: Thanks in advance!

I am reconfiguring my budget spreadsheet and am having trouble with an IF formula. I want to set up the base so that I can select if something is revenue or an expense, enter the dollar amount, and then in the next column over (Debits and Credits), it will automatically add a (-) negative to any item that is marked as an expense. Here is the formula I was using: IF({Trans Type}=“Expense”,{Transaction Amount}*-1)&IF({Trans Type}=“Revenue”,{Transaction Amount}*1) The formula is messing things up somehow because it won’t recognize the output as a number, and therefore will not let me format it as currency. Instead, it says: Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date. I want this column to be formatted as currency and show a running total. Can someone help?

Hi, I am creating a client job tracker and would like to autogenerate 4 digit numbers, eg. 0001, 0002. Can someome please help as I can only get a single digit or decimals so far. Thanks Daz

Hi, I have a list of cities and states, and I want to extract only first state initials. How can I do that? Thanks

Hi there, I’m trying to figure out a way to take 2 date inputs (shift start and shift end) to output a duration and I’ve done this using “DATETIME_DIFF({Shift End}, {Shift Start})”. However the system we use for our company relies on daytime hours and night hours being paid differently, so ideally we want the end duration to be split between day and night where the crossover is 7AM/7PM. For example if someone started their shift at 0500 and finished at 2300 they’d work 12 day hours and 6 night hours. Would this be possible to configure within Airtable’s capabilities? Thank you in advance.