Formulas

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

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.

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!

Hi! I have a base for fanfic that imports data and chapters via Airtables Webclipper. Some fanfics have 100+ chapters, so my base is set up to accomodate up to 125 chapters, one per field. After a fanfic is imported I want to tagg it for the characters in it that have not yet been tagged and disregard the ones that are mentioned in passing, but aren’t “physically present” in the fic. As is the code to find A (as in one) character looks like this: IF( FIND("A1",{🚫 Disregard characters (mentioned, but not present)}), '', IF( AND( FIND("A1", {🗃 Characters to Log}), FIND("A1", {Characters}) ), '', IF( FIND("A2", {🗃 Characters to Log}), "A1, ", IF(FIND("A2",{🗃 Chapter 00 - Oneshot}),IF(FIND("A1", {Characters}),'','A1 [00]\n'))&''& IF(FIND("A2",{🗃 Chapter 01}),IF(FIND("A1", {Characters}),'','A1 [01]\n'))&''& IF(FIND("A2",{🗃 Chapter 02}),IF(FIND("A1", {Characters}),'','A1 [02]\n'))&''& IF(FIND("A2",{🗃 Chap

:wave: Hello Airtable community! Here’s my use case: I have a table Items where each items has a price and a date period. Given their period of validity, I can or cannot add the item to an invoice. Example: I can add item A anytime in the year as its validity starts 01/01 and ends on 12/31. Item B however is only available from 03/01 to 03/31. I have a second table Invoices where each invoice covers a period of time, from a start date to an end date. Example: For customer A, I issue invoice #1 starting at 01/01 and ending at 02/15 and invoice B starting at 02/16 and ending at 04/30. For customer B, I only issue invoice #3 which starts at 02/15 and ends at 03/15. So given these parameters, I should have these items in my invoices: Invoice #1: Item A Invoice

**Solution ** REGEX_EXTRACT({url}, ‘[^/]+$’) Note: replace “URL” with column’s NAME and make sure the single quotation marks are STRAIGHT. Hi Airtable Community, TL;DR I’m trying to write a formula that’ll turn part of a URL into a Primary Field Specifically, I have a list of URL’s like below. I need to separate the last bit of each URL (e.g. shopify, how to use, square vs paypal, what is credit card processing) and automate it into the Primary Field name - leaving out everything before it. The problem I’m unable to solve is that the part after the main URL is variable. For example, in https://www.usnews.com/360-reviews/credit-card-processing/what-is-credit-card-processing the emboldened part varies in each URL, making it hard to remove it with a formula

Hi. We have been using Airtable for 3 years now. The person who set up our workflows is no longer with us. I needed to add a new person to the workflow and create their own ‘to do’ view for project work and I totally messed something up. We have a field for ‘status’ to help with project management and I think it was based on a formula so we could change status as projects moved along. I can’t get our status view to work properly and am desperate for some help.

I am trying to create a formula to return the difference in time between two dates in terms of the number of months and number of days. For example, being age “8 months 3 days.” This is the formula I came up with so far, but this direction doesn’t get me an accurate return since not every month has exactly 30 days in it: (DATETIME_DIFF({DATE},‘2 Feb 2020’,‘M’)) & " mos " & ((DATETIME_DIFF({DATE},‘2 Feb 2020’, ‘d’))-(DATETIME_DIFF({DATE},‘2 Feb 2020’, ‘M’))*30) & " days " Any ideas? Maybe I need to start over using different functions?

I am trying to add conditions to my nested IF statement, but somehow it’s not working. I want to extract musical key information from the end of file names. The information I am trying to extract is marked bold. BRASS CHORDS DARK G BRASS CHORDS DARK D1 FLAT BRASS CHORDS DARK C SHARP BRASS CHORDS FORCEFUL A FLAT BRASS CHORDS FORCEFUL B The extraction should only happen if the file name belongs to the category “Musical Sound Design”, but I can’t figure out how to get that condition into my formula. I am using this formula: TRIM( IF(REGEX_MATCH(NAME,'SHARP'),RIGHT(NAME,8), IF(REGEX_MATCH(NAME,'FLAT'),RIGHT(NAME,7), RIGHT(NAME,2))) ) Am I trying to get too much into this formula? The musical key information can eith

Hello, I have a problem with a SUBSTITUTE formula. I have a text field that contains two consecutive spaces between two words. I want to SUBSTITUTE these 2 spaces by nothing ("") but the formula doesn’t work because airtable consider these 2 spaces as infinite number of spaces. I know it because I tried to apply on this field a substitute 3 spaces by “X” formula and the output was first_word+space+X+second_word Something weirder: if I copy the output of my formula and paste it somewhere else, the space is not present… But it is problematic to me because I want to compare the output of my formula with another field in airtable …

Hi all, I have set up a formula to reformat a field that contains adjusted “date modified” info but the time (minutes) part of the date-time modified like field are different. Eg I have one field for Date modified, then another field that uses the formula “DATEADD({Date Modified UTC+12/GMT},3,‘hours’)” to add 3 hours to the date modified. Then another field using the formula “DATETIME_FORMAT({Date Modified +3 HOURS}, ‘YYYY-MM-DD HH:MM:SS’)” to get the adjusted date modified data into the right format. The adding 3 hours part is working fine, however the time is changing slightly at the date reformat stage. Eg field A has date modified showing as “2021-03-20 6:26pm” Field B is successfully adding 3 hours and showing “3/20/21 9:26pm” But Field C is changing the time slightly when reformatting and showing “2021-03-20 21:03:00” (rather than 21:26:00 for HH:MM:SS). Interestingly, when I add a comment to a record to change the date modified and te

Hi, I want to pull the image (and the image’s URL) from a given website URL (always from opensea.io). On the website I found the <img class but don’t know how to automate this process. Process example: enter website’s URL: Thicc Blastoise - Rarible | OpenSea script for pulling image URL: https://lh3.googleusercontent.com/rvHM92zMSm1AKR0tuoTu_1RmWGYeeKMwJx4xhQtXFjNeP3ZdbqtHK5E20_-JHrw4-BTsmH9evJNwYOzpIFBwLtOOXIt_Q1cGbZw1GA=s992 script for pulling image (jpg, gif): the image itself