Formulas

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",{🗃 Chapter 03}),IF(FIND("A1", {Ch

: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 #2: Items A & B Invoice #3: Items A & B Does anyone have an idea how to write a formula that can check if the date period of each item overlaps with the date period of each invoice? The rule is as soon as they have

**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 https://www.usnews.com/360-reviews/credit-card-processing/what-is-credit-card-processing https://www.usnews.com/360-reviews/credit-card-processing/paypal https://www.usnews.com/360-reviews/credit-card-processing/shopify https://www.us

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 either be 1 or 2 digits (G or G1), or 7-8 digits (C SHARP or C1 SHARP), or 6-7 digits (B FLAT or B1 FLAT). The CATALOG field determines tells us if a filename contains musical key information or not, but I cannot figure out how to work that condition into the formula. Thank you in

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 test, although the first 2 fields are updating

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

I want to show a message if a number field is blank. 0 is a valid, non-blank entry. I’m using the formula IF({Number}=BLANK(),"Blank","Not Blank") where {Number} is an integer. What I’m seeing is that blank values and 0 values are treated the same in this formula. That is, the formula field shows “Blank” for both empty values and 0 values. I would expect this to show “Not Blank” for 0 values. Conditional formatting will highlight only the empty values, which is what I would expect. So the concept exists. Is there something missing or is this a bug? Any workarounds?

I need help in writing a formula to remove spaces from a URL. This specific line of text is already pieced together using a formula: CONCATENATE(“Potential Partner ApplicationStage 2”,{Auto #},"&organization=",{Organization},"&website=",{Website},"&name=",{Name},"&email=",{Email}) which outputs → Potential Partner ApplicationStage 2 Bottle (75)&website=http://www.lovebottle.com/&name=Kelly Boslow &email= However there are spaces within this that need to be removed in order to properly use the link. Can I get someone to help with this?

AirTable Communityc, I am trying to find a simple way to score a “yes/no” question. I need to say that a yes answer is equal to a “1” and a “no” answer is equal to a “0” I was using the switch formula but I cannot formulate it correctly. I also tried an “IF” formula without success. Thoughts? Here is my formula: SWITCH({Aircraft - Is all equipment secured appropriately in the main cabin and the aft compartment?}, yes, ‘1’, no, ‘0’, ‘0’ ) Thanks in advance!

Hi, first of all new here :grinning: I’ve searched in the comunity and nothing really answers my question I have a formula DATETIME_FORMAT(DATEADD({Data Pagamento 1},{Produção Dias},‘days’),‘DD/MM/YYYY’) When I go to calendar I cannot select that field. Am I doing something wrong? I also thought of the possibility that the fact of being a “formula field” does not let the app recognize it as date, even though it is. So I thought a work around that I don’t know how to apply Create a second “date field” which value is equal to the formula. I thought maybe with … automation? :roll_eyes: I tried but I wasn’t able to find such function. “Date Field” = “Formula Field” Thanks Juan

I have a live integration set up from Airtable to Power BI to display sales data. The reporting is shown at a cadence of every two weeks. Certain records may change in status in the gap of the two weeks - and I want to be able to show what has changed in status and $ associated with that record. In order to determine what has changed from initial creation I have created the following fields: Created on (Returns date of creation of record) Modified Time ( Returns date of modification of specific field of status) Changed/No Changed formula field that returns “Changed” or Not Changed based on whether modified time date is greater then created on I am trying to create a activity field and want to write a formula that returns what that status actually changed from. Any help would be greatly appreciated!!

Hi there! Is there a formula for isolating the last available single-select from each group? I’m looking to only show the Shows that are marked as On-Air but also only want to see their currently airing season. I have a single-select for On-Air or Off-Air, and a single-select field for Seasons, but let’s say Jersey Shore has 6, and Teen Mom has 10 seasons, I only want to see Season 6 of Jersey Shore and Season 10 of Teen Mom. Is that possible? Thank you in advance!

I’m trying to figure out how to use an IF statement with DATETIME_FORMAT({Birth Date}, ‘MMMM Do’). Basically, I’m formatting the Birthday Field from the raw Birth Date field to remove the year and to put the date in a more reader-friendly format. However, when a birth date is missing, I get #ERROR. Is there a way to do an IF statement with an IF({Birth Date}=BLANK(),“Birthday Missing” with the DATETIME_FORMAT({Birth Date}, ‘MMMM Do’) as well? I tried just combining the two, but it won’t accept it as a valid format. Here’s screenshots of the fields: Thanks for your help! I’m new to Airtable so not so great yet.

Good day to all! I love how the Airtable works, but restricted by a total lack of understanding to some of the formulas. Would any one be able to help me how to figure this problem of mine please? Apologies if this is a stupid question, but: I’ve made a table of content for tracking of bug reports. What I would like to do is when the Status is marked “Completed”. The Completion Date should show the Now() or Today() formula. Else, it should be black. Here’s the formula I have used: IF( (Status = “Completed”), DATETIME_FORMAT(NOW(),‘MM/DD/YYYY hh:mm’), ‘’ ) However, when I tried doing it. Every time I mark a row with status “Completed”, all the dates in are also being changed. Thank you!

Hi all, I know that you can reorder linked field values by the Batch Update app, but I was wondering if there was a way to reorder linked values by another field related to that field. Example: We are linking people’s first & last name between two tables (“Jane Doe”, “Amy Smith”, “Harry Barrel.”) Instead of chronologically sorting them, is there a way to sort by their last name ("Jane Doe, Harry Barrel, Amy Smith)? We have a field that has only their last names, if needed, that is related to the linked field value. Thanks so much for your help!

Hi Everyone, I’m trying to set up record emails to send at certain times of the day, however after testing using the NOW() function, I’ve found that this is not entirely accurate, and on the help page it does mention that when the base is closed the formula will only update roughly every hour. Does anyone have a solution I can use to give a more accurate time in my formula? Thanks in advance!

I’ve built a formula that shows a Status in my Task field based on how many Sub-Tasks are complete: IF({Sub-Tasks Complete} = 0, “ :radio_button: Not Yet Started”, IF({Sub-Tasks Complete} = {# of Sub-Tasks}, “ :white_check_mark: Complete”, “ :orange_circle: Working On It”)) For some of my Tasks there is only one Sub-Task. When one of my teammates moves the Sub-Task into the “Working On It” status, I would like the status in the Task field to say, “Working On It.” Not sure how to change my formula to do that. Any suggestions? Thanks, Kevin

Not sure if this is a formula issue but here goes. I have a table with contract info that is linked to a table with contract billing info (i.e.billing period start date, end date and due date and payment status (invoiced, paid, overdue). In the Contract table I have a rollup field that returns for each contract - the start date of the most recent billing period. I would like to now display the payment status of that most recent billing period. Is there a way to do this? I’ve tried the lookup value but I cannot set it to lookup status only for the max billing start date.

Hi guys, I’ve been playing with the prefill form function that I’ve just discovered and am loving it! But I’ve hit a snag. Im trying to prefill a form to link with multiple linked fields. For example customer A has requested products A and B via a form. (products A and B are linked fields). I then create a new prefilled form to send to purchasing with Prefilled sections on customer A, however I cannot figure out how to link to multiple products. I can only get Product A to prefill. Heres the urls I’ve tried: prefill_Equipment=Product+A,+Product+B prefill_Equipment=Product+A++Product+B prefill_Equipment=Product+A&+Product+B prefill_Equipment=Product+A%20+Product+B prefill_Equipment=Product+A/n+Product+B prefill_Equipment=Product+A%0A+Product+B Any ideas on how I can do this?

I can’t seem to figure this one out. I have 3 Writer fields with 3 corresponding Share fields. So far I am only able to combine the WRITER fields with the following formula: {WRITER 1} & IF( AND( {WRITER 1}, OR({WRITER 2}, {WRITER 3} ) ), ', ’ ) & {WRITER 2} & IF( AND( {WRITER 2}, {WRITER 3}), ', ') & {WRITER 3} But I’d like to combine WRITER and corresponding SHARE fields and then combine everything in the ALL NAMES COMBINED formula field. The final combinations should read: John (BMI: 20457, 50%), Michael (BMI: 20691, 50%) John (BMI: 20457, 50%), Michael (BMI: 20691, 25%), Charles (PRS: 30918, 25%) Charles (PRS: 30918, 100%) Yes, the extra “)”, commas, and percentage signs have to be added in the formula, but my question is, can all this be done in one swoop in the ALL NAMES COMBINED formula field, or do I have to create 3 “PRE-COMBINED” fields for the 3 Writers and their percentages and then combine these fields with my original formula? It would be great if it co

I have been trying to figure out how to write this formula: Where Category is any of “macOS” AND checkbox Universal is checked OR checkbox Intel is checked. Here are two of my approaches: IF(Category = "macOS", AND(Universal = 1), OR(Intel = 1), "true") IF(AND(Category = "macOS", OR(Universal = 1, Intel = 1)), "true") Can anyone tell me what I’m doing wrong?