Formulas

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

I’d like to use a rollup field with an array as an input for a formula in a different field, but it gives me an error. It seems to not be treating the rollup as a text string.

Hello, I need help on an airtable formula. I have a date conversion formula: but I would like if the table is empty it says "Enter your birthday date" To avoid the error message please my formula : DATETIME_FORMAT(Dateofbirth, 'DD MMMM YYYY')thank you in advance

Hi all,I recently switched my team over to Airtable for project management. There were a few things holding me back, including that we'd have to build the automation for recurring tasks manually. Now that I have that built, I'm curious if I can get a formula into the automation which adds the proper deadline to the NEW task.Issue is - there are different kinds of recurring tasks (Daily, weekly, monthly). Do I need to parse these out to create a separate automation for each of these types, and then enter a formula into the dynamic field type of the automation? (I'm also looking for the actual formula!) The other thing is if it is a daily task, we skip weekends, so we wouldn't want a new task to show a deadline of Saturday or Sunday. I'm not sure if this is possible 🙂 Here's what the automation currently is: I can add the