Formulas

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.

Hi There, Brand new to Airtable, so apologies for this potentially stupid question. Am I able to sort a series of dates by month and date only (year isn’t important for the sort)? My current workaround is pulling the month and day into their own columns, then having a third column using this formula (MONTH&" "&DAY) and then sorting the results of that formula. I’d rather not have 3 columns to do a basic sort if I can help it. Thank you!

I would like to sum the number. Here is an example. (value) (accumulate) 10 10 20 30 8 38 11 49 3 52 If Excel, it would be calculated as below (B line) sum($A$1:A*) * means same row number sum(B1,A2) The summary bar will not be fine in my case. I want to understand the accumulated total in each row. Thanks for your support!

I am trying to create a formula to name records by concatenating specific fields but using different groups of fields depending on the item type. In other words: IF {Item Type} is Watch, THEN CONCANTENATE {Field 1} {Field 2} {Field 3} ELSE IF {Item Type} is Necklace CONCANTENATE {Field 4} {Field 5} {Field 6} … and so on for 8 or so item types. This is how the formula looks currently: Is it possible to do this? Help appreciated! I love Airtable but I have no formula skillz.

Hi there, I’ve got a CRM base that has a series of stages that an opportunity (aka the main record) goes through. I’m trying to track the conversion rate for each stage, basically if my stages are: [Prospecting Qualifying Proposal Won / Lost] Then I’m trying to pull a number for: What percentage of prospecting moves to Qualifying? What percentage of qualifying moves to Proposal? What percentage of proposal moves to Won? Assumptions: A key assumption here is that every opportunity goes through a linear process from one stage to another - so nothing just drops into the Proposal stage without having been prospected first. Any ideas or introductions to people who could help would be greatly appreciated :pray:

I’m trying to set up a customer having multiple addresses. So Table 1 is customer that contains the profile, then table 2 for addresses and I link the customer to it. I want the primary record of Addresses to have a count based on how many same customers are linked to it. Any leads?

Hi there, I have a question that I can’t seem to wrap my head around, so I would love to have your input. Let’s take a look at the following screenshot: As you can see, I group records based on a name. In this case, we have “K” and we have “T”. I would love to create a sum of the points for each person. So K would have 1 point, where T would have 5 points. There is indeed already a SUM, but I’m not able to sort on the SUM of each group (if I can, please let me know how, meaning that T would be on top of K). So, I would like to create a field that is able to calculate the SUM from all the records for each name separately. I would not mind if I would see that same result in each record, but the idea is to create a scoreboard, where the highest sum of points for each person is on top (currently, when I sort, it just sorts on the individual point). Is this possible, and if so, how would I go about this? (Basically, I want to do some kind of count if on each name separately and on each mon

Hi community! I have a very strange issue here… I have a lot of duplicates within the same field, a lookup field (I’m not so sure why, but that’s for a different question) In order to try to create a field with no duplicates, I created another field with the formula: (ARRAYJOIN({Most recent First Name :zap: },", ")) It works fine for most of the records, but in some it creates a a comma in front of the name, which I couldn’t get rid of. It happens only in some of the records, and only those that are not duplicated to begin with For the full picture, the next step is a field that takes the string that I created and find the name to the left of the comma which gives me just the name. Any idea what is it that I’m doing wrong? Thanks!

In order to track the workload we’ve had in the past, we want to create a formula that displays which specific weeks we worked on different projects. That way, we’ll be able to see how many projects we had active during specific weeks. We know which week we started a project, and which week we finished it, but I can’t figure out how to display the weeks that passed in between those dates. The number of weeks in between isn’t relevant, we want to see “We worked on 45 different projects during week 14”. Any ideas?

I’m running a datediff formula but it’s not displaying the result I’m expecting. I would like the output to include the day itself. In other words if I’ve got a date range from 28 April to 30 April, for our purposes this is a 3 day event. DateDiff gives 2 days though. DATETIME_DIFF ({Date ended},{Date started}, ‘days’)&" days" Any thoughts?

I understand this is a known issue, but I’ve read several threads on the topic and tried a few solutions but still can’t figure out how to fix this. I have two fields of scores, one from a 2020 publication and one from an upcoming 2022 publication. I am trying to be able to show if the score is an improvement, worsening, or the same. The issue being that the score “0” is an acceptable input and the formula is recognizing 0 as blank instead. The formula is currently as follows: IF( {KI-1 2022} = BLANK(), "", IF( {KI-1 2020} = BLANK(0), "⬛️", IF({KI-1 2020} - {KI-1 2022} = 0, "⬜️", IF({KI-1 2020} - {KI-1 2022} < 0, "🟥", IF({KI-1 2020} - {KI-1 2022} > 0, "🟩", "") ) ) ) ) I have tried COUNTA as well, but nothing changed: *I tried both COUNTA()=COUNTA() and COUNTA()-COUNTA()=0 but neither scenario worked… IF( {KI-2 2022} = BLANK(), "", IF( {KI-2 202