Formulas

I’m trying to build in the ability to automatically flag order data in my Airtable CRM as one of three categories: “new” - first time ever order for this customer “retained” - the same customer placed an order last fiscal year too “recaptured” - same customer placed an order at some point in the past, but not in the previous year. I felt like I was pretty close to solving this when I used an uniquearray formula to create a field that combines all fiscal years in which an account has orders into one array, so that I can compare each individual order fiscal year to that array. However, since there’s no apparent way to intentionally sort that array from, say, earliest to latest or least to greatest, I have no way to reliably compare a given order’s fiscal year to the array. I feel like I’m doing a bad job explaining this, but maybe someone will know what I’m talking about.

Hey everyone! I have a database of customer feedback that I’ve compiled and categorized by theme. I’d like to extract the top 5 themes and show them as a bar graph. Additionally, I’d like to have an option to change the week # and the bar graph will adjust the top 5 themes accordingly. Is this possible on Airtable?

Hi Everyone, I’m newbie on Airtable. I want to make my first formula. I want to know the latest date. I try to use Max, but the result is zero… I used this formula : MAX({Starting date (from Activity journal)}) (first colum is Starting date, last colum is used for the formula) Data in “starting” column look OK, since is able to calculate the MinDate in the summary… Any idea ? Thank you!!

Hello! I’m trying to use the nested IF function to return a specific piece of text per code that I enter (7 codes in total). I have tried to use both IF and SWITCH, and they seam to work for the first one or two and then I can’t get any further. For example, these are the conditions I would like to have met: IF{Indicator}=1, “A” IF{Indicator}=2, “B” IF{Indicator}=3, “C” IF{Indicator}=4, “D” IF{Indicator}=5, “E” IF{Indicator}=6, “F” IF{Indicator}=7, “G” The letters (A-G) are just substitutes for text I would like to include. It’s one sentence per IF function - could it be a length issue?

I run a photo studio and need help with a formula that I use to keep track of the status of documents clients are to fill out and return to me as part of their booking. In my table I have… 4 single select columns: Contract Credit Card COI COVID Each has the following options: Not Sent Sent Received Not Needed I want to create a “Document Status” formula column that returns 0 if any combination of the single selects are set to “Not Sent” or “Sent”. If none of those options are selected, the formula should return 1. From there I’ll create a filtered view of my table to only show me bookings with a document status of 0. In other words, only show me bookings who have not been sent or have not returned their documents. I had all of this working, but recently added the “not needed” option and it’s screwed everything up. Hope that makes sense. Thanks!!

Hello Everyone, I am submitting our Content for an award competetion, so submissions fees differ. 1st Submission is : 400 2nd Submission and up are: 210 Each another competition 1st submission is: 400$ 2nd Submission is: 120$ 3rd and up is: 60$ each How can I create a formula read the 1st, 2nd, 3rd, and so on and add the right fees amount through a formula? or a rollup? Some help is needed here Guys, appreciate your time. Thanks, A lot

Good afternoon, Hoping for some help please. I have a status filed (single select). New / requested / quoted / complete etc Is it possible to have the status change automatically depending on information in other fields. i.e. The status selects ‘New’ if another field B is empty The status selects ‘Requested’ if field C is not empty etc etc Thanks Mark

Hi, I am trying to make a Button that will open a Gmail compose message screen that has the To, CC, BCC, and Subject fields pre-filled. The CC and BCC are static; To and Subject change depending on the record. This one works but doesn’t have the cc field, and I want to add a BCC too… “https://mail.google.com/mail/u/0/?authuser=example@gmail.com&view=cm&to=”&{send to email} &"&su="&({load}&" - confirmation")&"&body="&{load numbers} now trying to add a CC field and it won’t save the formula, get an ‘Invalid Formula’ error “https://mail.google.com/mail/u/0/?authuser=example@gmail.com&view=cm&to=”&{send to email}?cc=traci@example.net&"&su="&({load}&" - confirmation")&"&body="&{load numbers} What am I doing wrong?

I would like to have a column for the % increase each week. For example: On February 1 there were 50 people registered. On February 8 there were 100 people registered. How would I do this?

Hello! Does anyone know a way that I can use a formula to convert a year to a decade? Forgive me if this is simple, I am new to formulas… Thanks!

First time posting, so sorry if this is categorized wrong. I’ve spent a long time digging through archives here and don’t think I’ve found a solution yet. I have an online store selling national park tokens that has a membership. When you become a member, you get access to all 63 np tokens, and you can order however many of them at anytime you want (they become $0). But you only get 1 of each. Technically in my store, there’s no way for me to control if someone accidentally (or on purpose) orders more than 1 of a certain kind, whether that’s in the same order or a month later. There’s no, “hey you already ordered this one.” What will happen is, we just won’t send that duplicate token with their order. So basically I only need to see what duplicates there are, and I thought Airtable could help. I have Zapier setup to import each order into Orders and it will list out each token they purchased, and the buyer becomes a Member. For the time being I have a dumb quick fix, where you can se

Hi Trying to create a formula to show all records before an actual date but not say today, last week etc. Can see how to do if I want today etc but not an actual date Originally, wanted to created formula to show records that fall in my accounts year end so within date range of 1 April 2020 and 31 March 2021 = 20/21. And to use for every year. Going around in circles to find! Can anyone help please?

I run a fleet, and we log trips data with relevant info (date, driver and vehicle#). I’d like a way to count ‘how many days a person/vehicle is active’, in a given date range. Generally, we would know this with the count function. But in this case, we only get the trips count, and not the active days count. Within a given date range, I need a formula to calculate how many days a driver was active (has atleast 1 trip/day). how many days a vehicle was active (has atleast 1 trip/day). Just couldn’t find a solution to this simple requirement, however hard I try to find. Is there a way to do this? Any ideas are welcome. Cheers

I am trying to set the Due Date of 4 To dos based on a field date (Checklist Returned(from Elite Details). I can make one work but when I nest the IFs it wont work. I know I am missing something but I have done it so many times I am lost! IF(Name=“Funnel and Ad Copy”,(DATEADD({Checklist Returned (from Elite Details)},5,‘days’), IF(Name="Funnel Build”,(DATEADD({Checklist Returned (from Elite Details)},10,‘days’), IF(Name="Creative”,(DATEADD({Checklist Returned (from Elite Details)},10,‘days’), IF(Name="Integrations”,(DATEADD({Checklist Returned (from Elite Details)},14,‘days’) ) ) ) )

I would like to extract the FOURTH word from the data that is populated in my Airtable. The first three words are always the same. The fourth word could have three characters or four characters or it could have five. How can I extract the 4th word and create a new field with it? Use case: The extracted word becomes a tag. Sample text: Next Level Show Dog Event Next Level Show Dogs Event Next Level Show Birds Event Extracted to a new field: Dog Dogs Birds Thank you for your assistance.

I have a base where I am tracking instruments loaned to students in a music department. Three of my data points are date checked out, date checked in, and a single select field for which two of the options are ‘unavailable for loan’ and ‘available for loan.’ At this point, I change the single select field by hand to indicate whether an instrument is available for loan or not. I’d like to create a formula that looks at the ‘date checked in’ field and if the field is empty, it changes the single select field to ‘available for loan.’ If the ‘date checked in’ is not empty, then the single select field would be changed to 'available for loan." Another layer would be if the ‘date checked in’ field is not empty, to look at the date and if it falls within the current school year (or another range of dates I choose), then the single select field would change to ‘available for loan.’ I think this can be done, I’m just not sure how to do it

Hi, I’m importing data that have “Publish Dates” in a text field. Most of them are just Month Year (April 2021) some of them are complete (April 30, 2021). I would like to use the formula field to convert them all to YYYY-MM-DD and use them in calendar/gantt view. It should always default to the 1st of the month for a missing day (i.e. April 2021 will be converted to 2021-04-01) I tried to search for a way how to use IFs and DATETIME_PARSE but I couldn’t make it work :man_shrugging:

I have created a formula to alert me to a birthday anniversary as well as upcoming anniversary (within 7 days), however I’ve noticed the date is GMT despite setting DATETIME_FORMAT as Los Angeles in the formula. At 17:00, the results change as it becomes 00:00 in GMT. This is causing a little confusion. The date field the formula is pulling from doesn’t seem to be listed as GMT, so I don’t know why this might be happening. Have I done something wrong? IF( IS_SAME( DATETIME_FORMAT( SET_TIMEZONE( DATEADD( Birthday, DATETIME_DIFF( TODAY(), Birthday, ‘years’ ) + 1, ‘year’ ), ‘America/Los_Angeles’ ), ‘L’ ), TODAY() ), “Anniversary”, IF( DATETIME_DIFF( DATETIME_FORMAT( SET_TIMEZONE( DATEADD( Birthday, DATETIME_DIFF( TODAY(), Birthday, ‘years’ ) + 1, ‘year’ ), ‘America/Los_Angeles’ ), ‘L’ ), TODAY(), ‘days’ ) < 8, “Almost Anniversary” ) ) Thank you!

I have created this formula that will set the duration when something is selected: SWITCH( {Select}, ‘Code Review’, 1200, ‘Design Review’, 1200, ‘Resume Review’, 1200, ‘Career Coaching’, 1800, ‘Team Coaching - Design’, 3600, ‘Team Coaching - Code’, 3600, 1200 ) It is then connected to another formula field to create and End Time: DATEADD({Time}, {Duration}, ‘seconds’) However, the time I get returned is 5 hours later not the minutes I have added. Anyone have a suggestions.

Hi Everyone, I’m currently working on what feels like it should be a relatively simple formula, but I’m consistently getting a #ERROR! result when I try to implement it. Context is: I have a table that lists students’ work experiences, their start dates and end dates. I want a filtered list of records where either the start date or end date was in the month before the previous month. e.g. if I was looking at it at the end of May, I’d want to see all work experience that started or ended in the month of April. This is the formula I’ve written: IF( OR( AND( {Start Date}>(DATEADD(TODAY(),-60,’days’)), {Start Date}<(DATEADD(TODAY(),-30,’days’)) )=1 , AND( {End Date}>(DATEADD(TODAY(),-60,’days’)), {End Date}<(DATEADD(TODAY(),-30,’days’)) )=1 ) =1,TRUE(),FALSE()) As I said, I’m getting a consistent error message on this formula. Apologies if I’ve missed something incredibly simple, but would anyone be able to cast a fresh pair of eyes over this and point out any mistakes I’ve mad

I am trying to create a filter in Adalo related to data in Airtable. I work on a category page. When the user click on one category (I.e. environment), he will see the content related to “environment”. Adalo does not integrate multiple value cell (if there is more than one category per cell), whereas most of my content can have 2 categories. In Airtable, I have created a second column with a sub category. Each content should be able to appear in both category and subcategory in Adalo. Also, there is an additional filter in the formula which is “Status = live” (to only display the content that are Live on Airtable, and not the draft content). So far my formula looks like this: AND(({Categories}=‘user cat’),({Status}=‘Live’)). It works for one category but I don’t know how to integrate the subcategory in the formula. It looks like AND and OR don’t properly work when combined but maybe there is another way? Many thanks.

Hello, Currently, we have payment data that comes in from Wordpress via a .csv through Zapier. The problem is it comes into one column like this: s:212:“a:6:{s:17:“bankaccountname”;s:8:“Checking”;s:19:“bankaccountnumber”;s:9:“111111111”;s:9:“bankname”;s:5:“BigBank”;s:19:“bankroutingnumber”;s:9:“000000000”;s:9:“bankiban”;s:0:”";s:14:“bankbicswift”;s:0:"";}"; We need to pull that data into 4 different columns - Account Name, Bank Name, Routing, Account Number. Givens: This data is presented as a popup inside wordpress. The end goal is to automate payment requests. Currently, we would have to manually enter in these details inside our payment gateway to issue payment. With a bit of automation it would streamline the process. Any help would be greatly appreciated!

Can I write a formula to do multiple things? For example I want to write a formula that when an items sales it calculates how much the fee would be based on the price. So if I sold something and it was $15 or less the fee would be $2.95 but if something sold and it was more than $15 it would be 20%.

Hello there, I have been using Airtable for a few years now and I absolutely love it. I am creating a basic HR system for a startup and I want to create a formula for the employees’ paid leaves. Basically, each employee should receive 2 days off for every month they work for. it should be added by the end of each month. How do I automatically make it add up in Airtable, using the joining date only? Is there a formula for that? Thanks in advance.

Some help with lookups appreciated. We deliver courses and have a table (A) where participants info is collected, like the name of the course they will follow. (f.e. ‘Photoshop Basis’) We also have a table (B) where the info of the course is seated, like the price of the particular course. Now we want to show in table (A) the price of the particular course so the participant can get an invoice with the right price. How to do that? We need some sort of lookup function for the course name and then return the price-field. Thanks for helping.