Formulas

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"),

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 su

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

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}) &nbs

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")) 

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), "S

Hi there! I am using IF formulas in two separate formula fields and they work just fine. I am trying to merge the formulas in one field and it is not working. The third and fourth IF keep disappearing. Can someone please help me?Here are my formulas :1st fieldIF(AND(Campagne = 'Grands Donateurs - Somme à amasser', {Progrès de la campagne} = 'Somme à amasser'), {Objectif restant},IF(AND(Campagne = 'Grands Donateurs', {Progrès de la campagne} = 'Somme amassée'),{GD - Montant promis})) 2nd fie

I have one table with item names in the first column, and I want to add a column that displays TRUE if the item name is present in the first column of another table of item names. In Excel I would do this with a VLOOKUP or MATCH wrapped in an IFERROR.

I have a table "Performance" with device performance data of my app. The table has the following columns: user-id, device-name, fps, time-stamp Now I want to extract the devices which have "fps" below a threshold (let's say below 10), because I want to blacklist those devices. I want to check in every week and see if there are any "new devices" with less than 10 fps and add them to the blacklist. say I have a second table "Blacklist", where I manually enter the devices I have so far blacklistednow a new record enters the "Performance" tableI want to check if the "device-name" of that new record already exists in the "Blacklist" table and if not, hightlight the field, or set a flag in another field, e.g. "IsBlacklisted"how to do that?

Morning! I'm trying to figure out a formula but struggling to get it to work and wondered if anyone could help please?Status column is a single select column with status numbers from 0-499 which i want to add information into a new column from a formula showing "1. called off and not yet delivered"Status 500 needs to say "8. Not listed in dropdown - detail in Comments" in the same formula column.This is as far as i have got, can anyone point me in the right direction please? Thank you!  IF(  OR(    {Status} <500, "1. called off and not yet delivered"){Status} = 500, "8. Not listed in dropdown - detai

I have a table with a field "Project" by which is grouped.It is filtered by a specific project and by records in which thewhere the field "Project" is empty. Between some records, the emptyproject group and the records of the non-empty project group there isa relationship in another field.The goal is to have the records in the empty project group that have a relationshipto the non-empty project group appear together in one view. Is this representable in Airtable?