Formulas

I am trying to have my airtable automatically assign someone a category based on the date range that their entry falls in. Specifically, if someone’s birthday is within their zodiac sign dates, I want it to assign them that single select in the zodiac field. For example, Aries is March 21- April 19th. If someone enters the form with the Birthday as April 1, I need it to add the single select of “Aries” to that record. I know that this needs to be an IF() formula, but I am having troubling getting adding in the date without a year. If I have to make it with a year, then this field will have to be updated yearly, which isn’t the end of the world, I just need to know if there is a way to do a date range only using Days & Month. Thanks in Advance!

Hi! How do I make it possible for the return of my formula to be not more than a specific number? Thanks!

OK… straight to the point… I’m importing order data using Zapier and the data source from which I am importing only provides comma-separated order data. For example, if a customer buys a qty of 3 of item 1, and a qty of 2 of item 2, I get a record like this: item1, item1, item1, item 2 item2. I cannot figure out how to get that string into “useable” fields, like an item field and a qty field. In addition, I also have a price field that acts the same way. For example, in the same order as the above example, I would get: 2, 2, 2, 4, 4. (assuming the price of item 1 is $2 and the price of item 2 is $4). I’ve worked with, and successfully setup tables with comma-separated order data, but those data sources had a qty field that matched the item and price. This one does not have a qty field. In a perfect world, the result would be a record that showed each unique item in a field with the associated qty and price in corresponding fields. Thank you!

Hello everyone, I have a table that has a few columns. One of the column is ‘amount(price)’ and also ‘status’ which I uses ‘Single option’ field, it consists of 3 options → paid, unpaid, paid half. So, I want to add another column which calculates all rows with status that are selected as ‘unpaid’. And can those rows that have a status of ‘paid’ and ‘unpaid’ change it’s color based on the chosen option? Ex: I want ‘Unpaid’ rows to be in red color, ‘paid’ to be in green color. Appreciate if someone can help. Thank you.

Hi I’m creating a booking system. need help in creating a formula in creating Start Date/Time and End Date/Time. in my form, I’ve an option for the customer to select multiple slots. I will need to add date add funtion to select start date/time and end date/time for calendar view.

Hello Airtable community friends, It’s been a while! Thanks to some of you, my online book business is running smoothly on Airtable. But I am far from Airtable proficient yet… How can I get the Date and Time formula fields to check and see if there’s something in Manual Date & Time first, and then, if it’s empty, turn to Unix Date & Time? I don’t know how to weave the necessary IF statement into the DATETIME_FORMAT I’ve already got set up in the Date and Time fields. I labeled the screenshots 1 and 2 'cause they’re the same, only the second shows the formula I’m using. Many TIAs for ideas!

My records have a lot of cells (columns). Now i want to write a search formula to horizontally search and scan all cells of a record to find a specific value. How can I solve this? About search formula’s: Airtable Support Using Zapier's Multi-Step Zaps to find and update records Zapier is a third-party service that connects Airtable to many other apps and services. This article is specifically designed to teach you how to design Multi-Step Zaps for the purpose of creating ... Use case: I want to use this search formula for Zapier, in order to automatically find and replace the particular cell based on some other triggers.

I cannot get this formula to work. Can someone help? I have a type field where you can select print or PDF. I need the start date to calculate a date either 4 weeks for print or 10 days for PDF. This is what I have: SWITCH( {Type}, ‘print’, DATEADD({Start Date}}, 4, ‘week’), ‘PDF’, DATEADD({Start Date}}, 1o, ‘days’) )

Regarding property bookings. We have a check-in & check-out field, both with time fields included. Typical check-in starts at 3:00pm and check-out is 11:00am. DATETIME_DIFF to see booking length in ‘Days’ shows 1 less that, in these cases (clearly it is counting days in 24 hour blocks, and 3:00pm-11:00am is < 24 hours). Is there an easy workaround I’m missing, to use date fields w/ time fields included, yet calculate booking length in ONLY # days? Let me know if this makes sense.

Hello, this is more like a documentation rather than a question, for those who might find it helpful and have similar use cases. If there are errors in the formula, please point them out. I have an attachment field called Picture. The number of pictures I’ll have in the attachment field can vary from 1 to 20. I want to grab the URL of each image, and put them into multiple columns. To do this, I’ll have to create 20 additional columns, one for each field. Note that this works as long as the image names don’t have any brackets in them. Hopefully, this won’t be a problem for many. The logic behind the formula is as follows: I have an attachment field called Picture I create a formula field called URL beside it, and the formula in it is {Picture}. This returns the URL of all the images in the Picture field, along with the image names. This is the field I’ll try to clean up and extract the individual image urls from First, I’ll use this formula to find the first “(” in the URL field: FI

Hello y’all, I’m writing out a formula that will generate dates. The dates will be based off a different calendar field in my table, and will be adjusted based on a dropdown menu. There are two formulas I’m attempting to use. The first is as follows: IF({Recording Date},DATEADD({Recording Date},SWITCH({Status},‘Outlined’,-3,‘Script Writing (including editing)’,0,’Review Outline’,-2,’Review Story Draft’,-2,’Ready to be Recorded’,0),‘days’)) With the goal being to adjust the date off of the “Recording Date” based off the “Satus” (the drop down menu). This has worked well. However, when I tried a similar approach for a column called “Upload Date,” the formula doesn’t work. Here it is: IF({Upload Date},DATEADD({Upload Date},SWITCH({Status},‘Recorded and Ready to be Edited’,-5,’Requests for Re-records’,-3,’Sound Edited and Ready for Review’,-2,’Complete and Ready to be Uploaded’,0),’days’)) Do you by any chance see an error in how I’ve written out this second formula?

I have a field that captures the URL for a webpage, but some URLs contain extra parameters after the .html part of the string. How can I remove anything after the .html for those records but not have a blank cell if the URL does not contain parameters after .html? I’d like a formula so that new field displays the URLs, just trimming the ?utm… part where needed: https://www.test.com/page3.html?utm_campaign=hc-sc https://www.test.com/page2.html I figured out the trimming part using this formula, but not how to display URLs where there is no ? LEFT({URL}, FIND("?", {URL}) -1) Thanks!

Right now we have a formula that uses percentages based on timeliness (Auto Calculated Percentage). This value is determined through a set of time between two dates. However I’d like to be able to override that for certain employees using another field like a checkbox or maybe a number field. So maybe an if statement would go in the auto calculated percentage? Like if override field is blank then use auto calculated percentage field? I can’t figure out how to do it ({Payment from Customer}*({Auto Calculated Percentage}/100))-{Admin Fee}-{Insurance Fee}-{Amount Paid to Employee}

Hey everyone! I’d like to create a tab in my pipeline in Airtable that shows me the number of closed deals each month and the total amount of money made during that month (ideally it should be a tab made of 24ish fields, so for example january (3 deals) - january value (25k) - february (4 deals) - february value (38k) and so on). In our pipeline we have a created time field which is the date the new lead enters the pipeline, and also an “acquired” field which is the date that that deal is moved from Negotiation to Contract. I’d like to track the number of deals that enter the Contract stage each month (if a deal enters Contract in january and it is still there the next month, it should only be counted in january). Maybe I’m overcomplicating things, or maybe it is only my poor english when it comes to explainings things that I have in my head :grinning_face_with_sweat: Hope you can help me, thanks in advance! Lorenzo :it:

Something strange with my formula. It was working fine earlier, but not now (something changed?) IF({Date} >= 365, “ :x: ”, IF({Date} <= 0, “ :x: ”, IF({Date} < 334, “ :white_check_mark: ”, IF({Date} >= 335, “ :warning: ”)))) When field is empty or 0 (zero) it still show :white_check_mark: Why?

Hi, I have product names with sizes inside, I would like to extract just the sizes, example : Converse Black - size : 40 Adidas Amerocaban - size : 44.5 Nike black T-shirt - size : XL Extraction in the column just to the right: 40 44.5 XL In summary, this would be the value extraction just behind “:” Do you have a solution? Thank you in advance and have a nice day,

Is there some way to use only Airtable instruments to calculate the great circle distance between two points? I have those points with lat and long coordinates and need a formula to calculate the great circle distance (or something close to it, no need in high precision). I know the haversine formula, but lack of trigonometry functions in Airtable makes the whole thing impossible. I am not into scripts, zapiers and web-hooks unfortunately. I have a Google API key which is used to create maps from Airtable data, but I do not need to calculate road distance or time, only great circle (use case is aviation). Just a formula of any complexity would be great. Thanks to everyone concerned.

I feel like this is simple but I’m missing something. I want 2 text fields joined with 4 blank spaces in between. Field1 & " " & Field2. It is trimming out 3 of the blank spaces on me. Is this possible?

I’m trying to create a field reference using a formula field. As an example: if I have a table with FieldA and FieldB and FieldC I want to create a formula field that will link to “FieldX” where X is a variable A, B, or C. It seems possible to build this if I know all the possible permutations of field names (using SWITCH statements) but what if it’s an open-ended variable?

I have an interesting problem. I need to create database-sort-friendly versions of names, where all definite and indefinite articles are dropped from the beginning of each name. I came up with this formula: IF(REGEX_MATCH(Name,'^THE'), SUBSTITUTE(Name, LEFT(Name, 4), ''), IF(REGEX_MATCH(Name, '^A'), SUBSTITUTE(Name, LEFT(Name, 2), ''), IF(REGEX_MATCH(Name, '^AN'), SUBSTITUTE(Name, LEFT(Name, 3), ''), Name ))) But it’s not working for names that start with “AN”. The formula is deleting all "AN"s. Why is it working with “THE” and “A”, but not with “AN”? What am I missing? I am at a loss. Thank you in advance!

How can I write a formula where I could say "return true if value is 95000, or starts with 923, or …) I’d probably use a regex for this but airtable doesn’t allow that. What’s the next best thing?

Hi, I am writing a large formula that looks for answers in another field and outputs their related categories. I have done this with multiple IF(OR(SEARCH( formulae, and it has worked great. However, this returns multiple categories as a single string running into each other, eg. “SensoryEscapeTangible”. I need a way to insert a separator between these values without having to create another field. Any suggestions? PS- ARRAYJOIN( doesn’t work as the use of ‘&’ between the formulae has concatenated the output into a single string already! Thanks!

Hi everyone, So glad to join the community. I’m new to Airtable and been trying to get this work for the last 3 days, but I’m hopeless right now. :pensive: So I have this “Overdue” column which shows it when the date is past 1.5 days from “Last Catchup”. Formula: IF(DATETIME_DIFF(TODAY(),{Last Catchup},‘days’)>1.5,“Overdue”) I have 2 more fields that need to be added to this: Engagement (Single Select Field) Tag (Single Select Field) What it needs to do: I need to show the overdue after 2 days when “Engagement” field is set to “Couldn’t Connect” Overdue after 3 days when the “Engagement” field is set to “Guest Unresponsive” AND Need to hide the Overdue when: The “Tag” field is set to “Confirmed” or “Lead Dropped” Any help would be really appreciated. :slightly_smiling_face: I tried a few things, but in the end, it shows an error in the formula.

Hello there. I’m struggling with a formula on base I use for production planning - wondered if anyone has time to give me some pointers… I have a table we use to schedule production - in essence the table has rows of tasks, with amongst other things, a start date, end date, total number of hours planned for the task, calculations for number of days between start and end, and then calculations for the average hours per day and per week. All working great and visualising on a Gantt. I now need to see an indication of load on production based on a sum of the average weekly hours for any given week. I’ve created a separate table with rows for each week of the year, with a week commencing date as the primary field, and linked through to my schedule. I’m trying to write a formula that returns a sum of the average weekly hours for tasks in my schedule where the week commencing date in the production load table falls between the start date and end date in the schedule table. I initially had a

Hello, New to AT community and hope to find help. This could be extremely simple but not sure how this field needs to be coded. Column needing help = “Monthly Fee” As shown in the fee section, is there a way to assign a value to each selected fee? For example, Exempt = $0, Forewarn =$5, Admin =$10? Therefore once any combination of those fees are selected it populates a total in the Monthly Fee section? Assume the Monthly Fee section needs to be a formula but not sure which formula it needs to be or how to do it? Thanks so much!