Formulas

I have a formula that works when a set decimal is entered but not when the decimal is inputted from a field reference. The formula that works (with manual decimal at the end): IF({Custom MSH Fee} > 0, {Custom MSH Fee}, ({Total Booking Price}+{Channel Booking Fee Amount (inc manual fees)})*0.1)The formula that doesn't work (with decimal from Field Reference) IF({Custom MSH Fee} > 0,{Custom MSH Fee},({Total Booking Price}+{Channel Booking Fee Amount (inc manual fees)})*{MSH Commission Decimal})What am I doing wrong her

Hello,I have a web scraper that is pulling in assorted data from a list of projects. The issue being that the date/time data that it scrapes into my base doesn't convert very well into an actual date field type.Here is what I am working with:Any ideas on how i can remedy this? I need the date field type so i can better filter the projects.Also welcome to any suggestions on how to best handle the "Bids In" column where it gives me varied answers that don't filter very well 😕😕 

Hi there, I know how to get things off the end of a string but not the start!!! I have recipes that look like this  Easy veggie, lentil and quinoa dahl with pita toasts (KIDS)Greek salad with crispy chickpeas and potatoesMiso eggplant with broccolini and edamameTM Pan-fried salmon with creamy pumpkin milletTM Moroccan spiced beef and lentils with carrot couscous I want to create a field that has all the recipe names - but without the TM at the start.  TIA

Hi,Am using a formula I've used in bases before, but it's behaving strangely.  It's subtracting a day off of the returned value.  I have two fields with dates.  Only one of the two will ever have a date in it.  I want the formula to grab the date from whichever field has a date already populated in it (from another table)Here is the formula.  IF({Shoot Date Rollup (From Scenes)}="", {Shoot Date Rollup (from Set List)}, {Shoot Date Rollup (From Scenes)})Example:Shoot Date Rollup (From Scenes) = 7/25/23The formula Returns 7/24/23Same if it's the other field that has data Shoot Date Rollup (from Set List) = 8/10/23Formula field returns 8/9/23Thanks for any help.

I am trying to follow this video tutorial: https://www.youtube.com/watch?v=BkAY7OHEgdcThe formula: 'https://airtable.com/shr12G6XKocbeGi0H'&'?prefill_Doll%20ID='&RECORD_ID()&'&prefill_Doll%20Name='&ENCODE_URL_COMPONENT({Doll Sculpt})&'hide_Doll%20ID=true' However, the part to hide the record ID is not working. This is what happened to the form:As you can see, the "Doll Name" area has the formula to hide the record ID in it. 🤔 🤔 I tried doing it exactly like the video and only having "(Doll Sculpt)", but it said there was an error with the formula and I was forced to use "({Doll Sculpt})". Could this be the issue?I haven't even tested the functionality (I haven

Hi there smart people!I'm looking to show the percentage of improvement since the implementation of our new Figma redlining process from our team survey. Right now, 5/8 people say the process has either improved or greatly improved! Is it possible to show this in Airtable's Interface? Either as 5/8 or 63%? Participants will continue to add to this survey in the coming weeks. So, I need a create a formula that will update the Interface accordingly. Please, let me know if anyone has an answer!

Hello, I am working with Airtable and Softr. Sometimes it would be helpful to show or hide single rows of a table. So I am looking for a way/formula to easily switch on or switch off (mute) each row in a table with "single select". Sort of like enabling or disabling the contents of the row (all columns). -- Of course, the content of the row will always remain and will only be shown or hidden. Is this possible? Many thanks in advance for your answers and suggestions! 🙏

I'm building a table with many due date columns and they all need to be automatically calculated based off of one "publication date." For example one due date might be 21 days before publication. BUT, I always want the due date to fall on a work day. How do I make that happen? And a second, related question: I'd like to have the same sort of setup for one of the tasks but instead of an auto-populated single date, I'd like the formula to return an auto populated 3 week time range. (The weekdays are not important for that one.) 

I have used a formula to create a new date value based on another date cell: The Next Due Date uses the following formula: SWITCH(Frequency,'Weekly', DATETIME_FORMAT(DATEADD({Task Due}, 1, 'week'), 'DD/MM/YYYY'),'Monthly', DATETIME_FORMAT(DATEADD({Task Due}, 1, 'month'), 'DD/MM/YYYY'),'Quarterly', DATETIME_FORMAT(DATEADD({Task Due}, 3, 'month'), 'DD/MM/YYYY')) However,  to set up views and automations I need to be able to use the conditional properties of a date value, and I can not seem to filter on any of the normal date values. Is there a way to filter Next Due Date to be 'with

Hi all, I’m trying to work out a simple countif alternative which adds numbers in a column only based on a dropdown selection in another column. For example, if I have dropdown options based on project status (ie. “Not Started”, “In Progress” and “Complete”), and a simple number column tracking how many apartments are in each project, can I have a third column with a formula counting the number of apartments only in Complete projects, which I can then use to create a dashboard display? Or do I have to create a separate view and sort/filter the data this way. Keen to avoid as I already have quite a few different views set up. Thanks in advance! AC

Hello, I am pretty new to airtable and have a slight issue with fetching info from a record within a record. With an automation i create a number of records within a record to be able to choose multiple products for the same order number. (Not a lot of context here, but it would be a way to long post if I provide all the context)All I want to do is separate the info, in the following case, for the two elements.Looks kind of like this:Amount & product (linked records)Weight (lookup)Weight, first element(help here)Weight, second element(help here)120 Coarse,100 Fine4.5, 3.04.53.0

Hi, I'm building a formula that says if the first payment date is equal to the month today then show the first payment date otherwise show other payments with the month today.   

Hi All,I need some help with extracting specific text. I've looked through the community and tried to apply the examples offered...but I am not getting it quite right.I need to extract the suburb, state and post code from a (text) address field. Some suburbs are made up of two names. Below is an example of how the addresses are formatted/written.  Whilst the addresses are made up, these are real suburbs in Australia 😀.Address Examples:305 Smith St, COCKBURN SA 544012 Lincoln Cres, WOOLLOOMOOLOO NSW 201112/65 Johnston Lane, DOG SWAMP WA 6060Wanted Outcome:COCKBURN SA 5440WOOLLOOMOOLOO NSW 2011DOG SWAMP WA 6060I appreciate the help. I don't expect an explanation but I am trying to learn so any info alongside the solution will be helpful.Thank You!!

I am using the field, "Total Days Open" to count the days between the date something is submitted and the date that work is completed. The formula is: IF(AND({Work Completed} = BLANK(), {Submission Date} = BLANK()), "",IF({Work Completed} = BLANK(), DATETIME_DIFF(TODAY(), {Submission Date}, "d"), DATETIME_DIFF({Work Completed}, {Submission Date}, "d")))On a separate tab, I want to calculate the average total of days based on the Health Plan link to the records I want to average. I have been trying to use the Rollup field but it is returning a value of "NaN"

Hi Everyone!So I would like a formula field where it shows the month and year of the End Date match the month and year today, say "This Month". I only know month and year separately as shown below however, I want them together in a single-sentence formula. IF( MONTH({END Date}) = MONTH(TODAY()),"This Month")IF( YEAR({1st Payment Date}) = YEAR(TODAY()),"This Year")

Is it possible to use a formula or some other work around to find the most recent date across 4 rollup columns? See photo. I'd like the left column to then show the most recent date, the 4 columns are different donation platforms and it'd be helpful to summarize that info to one single column showing most recent date regardless of the donation platform. 

Is there any way within airtable to return multiple row headers based on a column match? I have an example below. Basically my master base is company-focused but I have two fields "Investors" and "Investments" which would both be matching items in the "Name" field. Ideally I want to be able to tag "Investor(s)" in a column but have the "Investment(s)" column auto-populate pulling in "Name" based on a match of "Investor(s)" to "Name" in that particular row. NameInvestor(s)Investment(s)Startup AInvestor A, Investor B Startup BInvestor A 

I want to use formula TODAY() BUT I Don't want that it will automatically change with each passing day. I need that the original date that was created, will stay the same. Is there another formula that can do it? 

Over time our long text field with bullet points has evolved into 100+ lines and we're looking for a way to no longer add new bullets at the bottom of the field, but rather at the top. Hence the very logical question: on existing long text fields how could one "invert" the bullet points. As an example:Item 1Item 2Item 3Should become:Item 3Item 2Item 1I've tried to use the "LEN" formula but haven't been able to produce anything working. Many thanks,

Hi, I am trying to create a formula to work out meal allocations for works at a festival site.Currently I have date fields for 'ARRIVAL DATE' & 'DEPARTURE DATE' and a field for 'DAYS ON SITE' which uses the formula DATETIME_DIFF({DEPARTURE DATE},{ARRIVAL DATE}, 'DAYS') to calculate the days that any given crew member will be on site. I use another field 'Crew Meals' with the formula SUM({DAYS ON SITE}*3)Through the actual show component of the event, we only feed crew members that are actively working ie audo / lighting operators. I have included a check box field 'SHOW CREW' for crew members to check if they work on the show. I would like to create a formula (potentially an extension of the 'CREW MEALS' formula) which takes into consideration the 'SHOW CREW' check box s

Howdy yall, I am trying to do a simple math setup for a client. calls per hour, demos per hour, calls per demo... The issue I am running into is the math seems to be coming back wrong. I have tried a few different ways and still having issues. Here you can see the number not adding up. In this case, we did 30 demos and had 19 families sign up. According to my calculator, I get 1.58 demos per family (30/19). However, I get 1.82 in the formula field. I have eliminated the infinity/NaN issues with the code in the pic that I found and edited. I will add that the rows the data is being pulled from are just normal number rows. I have tried different decimal sizes to see if it was a rounding issue, but it wasn't. Any advice or ideas would be awesome. 

Hi everyone, I am trying to create a formula that will output what funding year a record falls within. I have a field with the dateStart of the funding and another with the dateEnd of the funding. I also have a program start date which is a static field. I am calculating the funding year manually and am sure there is a formula that could do this for me, but I haven't been able to figure it out. In the example below here is how I get the funding year: I write out the date ranges and manually tag the funding year depending on what year it falls within. The program start date is what the funding years depend on.9/1/2019-8/31/2020 = Y19/1/2020-8/31/2021 = Y29/1/2021-8/31/2022 = Y39/1/2022-8/31/2023 = Y4Any suggestions would be MUCH appreciated!

Hi all, I am looking to create an interactive leaderboard, where my students fill out a form which relays the results back into a board. I have a rough idea of how to do this, but was wondering if anyone has attempted this before or any leaderboard type board? Thanks! 

I’ve seen some complain that there is no SPLIT function in Airtable. Here’s my workaround: I’m dumping data into an existing table and I have formula fields that “pull data out” into other cells based on keywords. The formulas are just combinations of FIND, MID, and LEN. I’m doing this work once and the table will do the work for me when I import new data. Yes, a “split” would be nice, but it’s definitely possible without it. P.S. Don’t forget to escape characters with “\” in formulas.

I can't for the life of me figure out what's happening with this one transaction in the personal finances base I've been building.  Would you guys take a look?These are two of the same type of transaction for comparison.  The top transaction calculates exactly as expected.  The bottom transaction calculates out to -$0.00?!?!  I have traced and re-traced the formulas and can't find the issue, haha!  Here's the flow:The Amount field calculates by choosing the non-empty entry amount field from the following options:  an Enter Amount field that allows me to type the amount indirectly, and the Import Debit and Import Credit fields you see in this screenshot which come from my bank import files.Transactions are designated into one or more categories.The amounts from those designation records are rolled up into the Total Split