Formulas

I am trying to incorporate a formula column which looks for entries which may include swear words, but I cant seem to get this formula to work. Here is a snippet (I've taken out a lot of the middle which is just 3230 different swear words) but the beginning and end are there. Any ideas? IF((OR(SEARCH(“2 girls 1 cup”, ARRAYJOIN({Lower})) > 0, SEARCH(“2g1c”, ARRAYJOIN({Lower})) > 0, SEARCH(“4r5e”, ARRAYJOIN({Lower})) > 0, SEARCH(“5h1t”, ARRAYJOIN({Lower})) > 0, SEARCH(“5hit”, ARRAYJOIN({Lower})) > 0, SEARCH(“a fanabla”, ARRAYJOIN({Lower})) > 0, SEARCH(“a pecorina”, ARRAYJOIN({Lower})) > 0, SEARCH(“a tomar por culo”, ARRAYJOIN({Lower})) > 0, SEARCH(“a tomar por saco”, ARRAYJOIN({Lower})) > 0, SEARCH(“a$$”, ARRAYJOIN({Lowe

Hey guys,Just trying to work out a simple Formula to create a day count until a certain date (based on one of our fields) without including weekends. For example. We have a customer due date for an order which is put into a field, just wanting to have a field that calulates how many days we have for production until that date. So far I have: DATETIME_DIFF(  {Due Date},  TODAY(),  'days')Which works fine but is including weekends. To combat this, I did try:WORKDAY_DIFF(  {Due Date},  TODAY(),  'days')But this is throwing an #ERROR. I know I am definetly missing something. Any help is appreciated. 

hi, when i use the field #number, the number always look like this 1.00but i i want to put my numbers as 0.01 how can i do that? because everytime i put 0.01 they change it to 10 

Hello,I program a film festival and we use Airtable to organize the submission we receive. This year we have two different programming teams that should watch half of the submitted projects. I wanted to create a formula that would calculate the total of duration of the films (we have a field for the duration in HH:MM:SS), divide it in half and then assign each half for team A or team B. Is that possible? I have tried to add some simple SUM formulas but I'm still a beginner in all of this. Thank you!

I am looking to remove the comma separating the two lists. Here is a picture to show an example.    Is there a way to do this?Thanks!

Hello everyone,Trying to come up with a formula using Chat GPT (and some tweaking) to create a formula field type that displays a subscription's (record) amount in a cell based on budget status and the payment schedule.I want to create a formula that checks the following conditions:The Payment Schedule is "Annually."And one of the following is met:a. Budget Status is "Projected" and Renewal Date is within the specified range.b. Budget Status is "Actual" and Purchase Date is within the specified range.If these 2 conditions are met, it displays the {Subscription Amount}. If none of the conditions are met, it will display "Conditions not met".Here's the code:  IF( AND( {Payment Schedule} = "Annually", OR( AND( {Budget Status} = "Projected", {Renewal Date} > DATETIME_PARSE("2023-01-01"), {Renewal Date} < DATETIME_PARSE("2023-02-01") ), AND( {Budget Status}

I have a base in Japanese and want to use Japanese field names but when I tried to put together a formula, it does not work.  Is there a work around for this issue?

In my "CRM" database, I am building out a reporting table that gets rollups of IDs from other tables (i.e. interactions, etc) and am trying to get one array of IDs that I can count to determine the number of unique IDsInteractions TableOne to Many Contacts, lookup field of Contact Record IDs from "Contacts" TableProjects TableOne to Many Contacts, Lookup Field of Contact Record IDs from "Contacts" TableReports TableRollup of Contact IDs from Projects, Rollup of Contact IDs from InteractionsMy formula below doesn't get all the IDs. I don't know if maybe there's some nesting going on. Any suggestions for combining these arrays? properly? COUNTA(ARRAYUNIQUE(ARRAYFLATTEN({Interaction Contact IDs}, {Project Contact IDs}))) Each of my rollups in Reports has this formula ARRAYUNIQUE(ARRAYFLATTEN(values))   

Hello all,I need a formula that will delete every instance of a string of characters between [] including the brackets. Basically I'm pulling a products from a database which is SKU code then the product name. Every SKU code is like the following [SHU-BATT-22-EU] SHURE BATTERY 22. Is there a way to remove any code between [] including the brackets so I am just left with the product name? Thanks in advance!

Howdy, Recently encountered a twitter account that I like which posts a progress bar for the year. I would like to make an extension that simulates that, so made a field that displays the day number of the year; specifically: DATETIME_FORMAT(NOW(),"DDD")However, when I am trying to then convert that output into a percentage, I only get an error.DATETIME_FORMAT(NOW(),"DDDD")/365I'm very confident there is a simple detail that I am overlooking. Would any of you happen to know why? 

Hello,This one has me frustrated because I know it can not that hard. 😁I need to create an Expected Ship Date field.  I have a Start Date field (formatted as Date) and a Ship Days (lookup field from another table).  I want to find out how many days prior to the start date I need to send out the package. First My thought was Start Date - Ship Days, that just gives me NaN message.Then I thought I needed DateTime_Diff(Start Date,Ship Days, 'days').  That gives me a 4 digit number in some cases and NaN in other cases.  i.e. 1/3/2024 (Start Date) - 7 (Ship Days), I get 8221.  I am thinking that is good and just need to DateTime_Parse it.  So I enter,  DATETIME_PARSE((DATETIME_DIFF({Start Date},{Ship Days},'days')),'MM/DD/YYYY'), and I get #ERROR!What am I missing?

Hi people! I’m building a “Birthdays” table in my “Calendar” base and I’m looking for a formula that says: if “Birthday” is within the next 2 months then display “soon” What would this formula look like?

I want to combine multiple IF statements from data in two columns and return a TRUE or FALSE result.The two columns/data I am using are:User Subscription Level (Basic, Pro, Premium)Total Requests (Sum of values from multiple fields)What I've tried to do so far seems to not be working very well and I am a complete novice when it comes to Airtable formulas. IF( AND({Subscription} = "price_1NZdQ5C8Jx0D8bOIPFa8Nlto", {Total Requests}<"8"), "Limit Not Reached", IF( AND( {Subscription} ="price_1NkT9EC8Jx0D8bOIMXi0g37m", {Total Requests}>="25" ), "Limit Not Reached 2", IF( {Subscription} ="price_1NkT9EC8Jx0D8bOIMXi0g37m" ), "Limit Not Reached 3", "Limit Reached" ) ) Ultimately what I'm trying to do is check the user's subscription level and the number of requests they've submitted and evaluate if they are at their limit or under their limit.I'm not sure if I'm fully grasping how the logic is supposed to flow and I

Hi, hoping this is easy...Can you help me adjust the formula below to return just the number of work days?formula:  IF( AND({Squad Start Date}, {Squad End Date}), IF( AND( {Squad Start Date} < DATETIME_PARSE("10/28/2023"), {Squad End Date} >= DATETIME_PARSE("10/01/2023") ), DATETIME_DIFF( DATETIME_PARSE(MIN(VALUE(DATETIME_FORMAT({Squad End Date}, "X")), VALUE(DATETIME_FORMAT(DATETIME_PARSE("10/28/2023"), "X"))), "X"), DATETIME_PARSE(MAX(VALUE(DATETIME_FORMAT({Squad Start Date}, "X")), VALUE(DATETIME_FORMAT(DATETIME_PARSE("10/01/2023"), "X"))), "X"), "days" ) + (MONTH({Squad End Date}) = 10), 0 ) )  Thank you so much! Ann

Hello, I am looking for a way to calculate a date minus x days based on a date field. I.e. the result should be 31.12.2023 (DD.MM.YYYY) minus 10 days = 21.12.2023. How can I do this? I tried to use this formula, but it returned an error:  DATETIME_FORMAT(DATEADD({Date Field}, -10, 'days'), 'DD.MM.YYYY') Thanks for help, M.

I’m probably missing something, but how do I get the current day of the year? For instance, we have WEEKNUM(TODAY()), but not DAYNUM(TODAY())? I can do year(today()), and week(today() but when I do day(today()) it shows the day of the month and not the year. Thanks! Example: Jan 1st 2021 is day 1, etc.

I already have a formulas that work fine although i need to add “( )” around my Base field called MODELEIF(AND({PRODUIT},{MODELE}),{PRODUIT} & " - " & {MODELE} & "\n",IF({PRODUIT}, " " & {PRODUIT} & "\n") &IF({MODELE}, " " & {MODELE} & "\n"))& Right now when I got the formulas it shows ANNEAU DE FOULARD - ALEXANDERI would like to add ", so it will show this.ANNEAU DE FOULARD - "ALEXANDER"   

Hello again,I am trying to puzzle out the best way to know if a member of an organization has lapsed in their membership dues and when they were due.I have a payment table with records of each payment by members with the date of the payments. I want to list members who have not paid in 12 months since last payment and, list them as lapsed in the renew column.Added problem. The table lists all payments over time. So each member could have many entries of payments in the table. I only want to test on the latest payment per member. Yikes!Anyone have any thoughts on this?Thanks in advance, Scott

I have some numbers i need to include in a string.These numbers needs to be rounded down to two decimals, which I use ROUND() to do, my problem comes if the number has trailing zero's fx:233.50 becomes 233.5233.00 becomes 233Does anyone know how can keep the trailing zeros? 

I have this formula as 1 of 3 parts that's extracting certain bits of data from a reference cell into 3 adjacent cells. What I'm wondering is if I can add something to get a specific character insert BEFORE the info...Examplereference cell contains : flavor1, flavor2, flavor3 This part below pulls out "flavor1" into a 2nd cellI'd like it to do that but add a " / " before the flavorSo the result would be " / flavor1"IF( FIND(",", FlavorsLookup1_1),LEFT({FlavorsLookup1_1},FIND(",", {FlavorsLookup1_1})-1),{FlavorsLookup1_1})  Thoughts/suggestions are appreciated in advance.

Hello-I am trying to create a formula/automation/script that looks at a field of images (All PDF Files), and takes the field that is named in the file "RULES", and add this file only to another field called RULES.Any ideas?Thank you!

Why wouldIF(NOT( IS_AFTER( Date,'10/4/2023)function correctly andIF(IS_BEFORE(Date,'10/4/2023)not function the same in a formulais not after = is before 

Hi All! I am still new to Airtable and writing formulas and (hopefully) this is an easy question.I am trying to write a formula that will display text that matches the "checked" state of 3 different checkboxes, and also will display "New" if none of those checkboxes are checked.This is what I have written so far, and it is working to display the state of the first checkbox, but because that is true, it's not moving on to the next thing I want it to test for. Is there an additional logic I could add to the formula that would accomplish this? Or a totally different way of going about it?My current formula in the Status column: IF({Service Started}, "Service Started", IF({Ready for Pickup}, "Ready for Pickup", "New")) Screenshot for context: Thank you so much!Devon

Hi, I am trying to create a formula that accomplishes the following but am not quite sure how to set it up. Does anyone have any suggestions? IF({FPL %} > 125% but < 300% = "Eligible")Thanks!

Hello!  I'm relatively new to AirTable, we're migrating here from FileMaker Pro.  I'm forced to duplicate a calculated field in FileMaker, which is essentially the same a a Formula in AirTable.  Basically it does some complex looking at a zipcode value and either displays a Metro Region if it's a certain zipcode, or instead, just displays the City name.  I've been trying to do this:SWITCH(AND({l_zip} >= 94100, {l_zip} <= 94199), "North/San Francisco",AND({l_zip} >= 93600, {l_zip} <= 96199), "North/Other Cities",{l_zip} = 91604, "South/Los Angeles & Vicinity",{l_zip} = 91711, "South/Los Angeles & Vicinity",{l_zip} = 90755, "South/Los Angeles & Vicinity",AND({l_zip} >= 90000, {l_zip} <= 90199), "South/Los Angeles & Vicinity",AND({l_zip} >= 90200, {l_zip} <= 90599), "South/Los Angeles & Vicinity",AND({l_zip} >= 90800, {l_zip} <= 90899), "South/Los Angeles & Vicinity",AND({l_zip} >= 91100, {l_zip} <= 91199