Formulas

Hello Airtable community!Hoping someone can assist with this formula conundrum. In short, seeking to calculate 'pending' (sold but not yet activated) subscriptions within an Airtable base. Subscriptions Table primary field is per nominated subscriber record but subscribers are grouped by their organisational contracts which can have multiple subscriptions sold/attached. To arrive at this true subscriptions pending figure I'm thinking the below calculation path:Total #subs contracted (Number field)  -  Status  '_Activated'  (Multi Select field)  -  Status 'Deactivated/Cancelled'  (Multi Select field) = Pending (subs remaining to activate)Is there a way of achieving this within the table or via rollup formula in another table??  (pics attached for elaboration). Status field is currently linked to an activations summary tabl

Hi Guys, I'm working with a project tracker template which has tick boxes for subtasks in one cell. I want to create a column which can calculate how many of those subtasks check boxes have been completed. Is anyone able to help me with this formula?After hours of looking how to do this I'm coming to a dead end!Thanks so much!

I wish Airtable Regular expressions followed some common flavours so we could test it with regex101.comI can make it work in these builders, but then when it comes back to Airtble #ERRROR. It's a huge potential but otherwise Airtable makes it hard to figure out why it isn't working.I am trying to pull out the Patient's name from a Text extract. It always occurs after "Patient's name: " & before "Birthdate:"Example string:Radiology Report Patient’s name: Barney (57493) Birthdate: 7/5/2013 One Regex101.com, and regextester.com I can get it perfectly with this regex:(?<=name: )(.*)(?= Birthdate: )But when I put that in airtable:REGEX_EXTRACT({PDF Text Extraction}, "/(?<=name: )(.*)(?= Birthdate: )") I get a big fat #ERROR I know it's me but can anyone point me in

Could someone help me understand why this formula throws an error? SWITCH(1=1,TRUE,“Y”,FALSE,“N”) Thanks.

I am trying to create the following formula but keep getting an error that there was a problem saving this field. Invalid formula. The intent was if the project is complete or released to customer, then do the followingIf the date diff is negative or NAN, make the result =0 otherwise just do the date diff. However, the NAN is still being displayed. How do I represent NAN value? IF(  OR({🚩Workflow Status} = "Complete",     {🚩Workflow Status}= "Released to Customer"),  IF(      OR(DATETIME_DIFF

Hello!I believe what I'm asking could be a fairly simple fix but been trying to figure out and see if anyone has a formula for this. I created a Task + time tracking sheet for my employees. I'm trying to see if when I put the due date for instance if something is due 04/04/2023 I'd like to see if there's a formula or automation to make a single select field as well to put the month in. So in this instance, the field would be "April 2024" Please let me know if anyone has a solution for this. Additionally, if anyone has any great time tracking/tasks ideas to add to my base please let me know!

hi All, I´m quite new in creating my own bases etc. And I´m struggling with the following. Not even sure Airtable can provide this. The case is the following:I´m working with a campaign which includes multiple types of Activities (Asset Type) and those Activities have multiple versions with unique single ID linked to it. ((and other attributes in the same plan. (Synced by my ID database)). In my Screenshot you can see the data grouped together. All records are linked to the Group Attribute Campaign.  So my question would be, if there is a way to group those records into one data source (Campaign with all assigned IDs) and link it e.g. to another Base into one record? See example attached. This group view would have to be connected as One record. It´s not that easy to explain as english isn´t my mother tongue so please bare with me:)  Looking forward for help

Hi everyone, I would love to have an permanent - Airtable - solution for this problem: the standard Currency format is the US format. Meaning: the COMMA for the thousands and the DOT for smaller figures (lower than $1, for example: $15,388.99). I am from the Netherlands and our system is the other way around. We use the DOT for the thousands, and the COMMA for the smaller figures (€ 15.388,99). What would be the best workaround as long as Airtable does not give me the pick my Netherlands format? Regards, André

I am using `SUBSTITUTE({RENEWAL / NEW MARKETING},",","\n")` formula to convert multi select record to list format as below:Before: After:  As you can see after picture where `Market my products offline (Ads, billboards, trade shows etc.)` is splitting as:`Market my products offline (Ads billboards trade shows etc.)`I want a solution where above formula should not effect commas inside option text?

I have a table called 'Equipment List' and table called 'Projects'.I have a single select field in Equipment List called 'Availability' with 2 options, IN and OUTI have a single select field in Projects called 'Status' with 2 options, COMPLETED and RUNNINGIn the Projects table, I have a linked field to Equipment List, allowing to link to multiple records.The functionality I would like is:IF Status field in Projects is set to RUNNING, then all the corresponding items in Equipment List field, change their Availability field in the Equipment List table to OUT, otherwise set to IN.What is the easiest way to do such a thing?

Thanks to this community I learned how to create nested IF(AND) formulas, but I want to add an additional rule that if another column is No, then it will exclude the amount. Here is my formula. I want to add "IF({PD/PC/PoP Stipend?} = "No", ), "0"), but how would I do that? - thank you!!  IF(   AND(      {New/Refresh} = "New",      {GR/UG} = "Undergraduate"   ),"3,000",IF(   AND(      {New/Refresh}

I have a formula which calculates the # of days between two dates. I want to add if the resulting value is <0, then the value should be set to 0, otherwise, keep the value. This is the calculate of two dates. How do i add the second part? I tried nesting, but that didnt work, F(  OR({🚩Workflow Status} = "Complete",{🚩Workflow Status}= "Released to Customer"),  DATETIME_DIFF({🚩Actual Eng. Delivery Date},{🚩Original Planned P50 Date},'days')  ,"")

so  i have a table to thats shows a field with top 10 finishes which is a formula field that uses the below formulaIF({Amain Finish}<='10','1','0') what i want to do is create view that just shows how many top 10 finishes we have for each driver. so for example in this table data i have a total of 3 top 10 finishesI also have a field called drivers 2 only 2 options (Jeremy and Jaycie)can i create a view to show just the total of the top 10 finishes then sort by driver so Jaycie wouldshow 2 top 10 finishes (blue color on date) and Jeremy would have 1 (red color on date). Thank you for any help or assistance

I'll try to keep this simple. TLDR: How can I use a field in parent table to bound the results of a linked table rollup?I have three tables as follows:People- containing personal infoTasks-Task date and other pertinent dataGraphs-Start Date, End dateUsing integration with chart.io I want to graph various aspects that are pulled from the tasks table, BUT I want that data to be bound by the start/end date of the graph. I'm successfully using an automation to list all tasks (linked field) that fall in the date range, and then from that list another linked field to find all people that did work during that time. Now, what I'm trying to do (unsuccessfully) is use a rollup to count items by person based on DateStart/DateEnd (which have been converted to strings). The *problem* is that when I use "Only include records" it does not allow me to dynamically define that inclusion- I hav

I work in a digital archive and I'm using Concatenate to create a unique naming formula based on certain fields in a spreadsheet. However, some items have different naming requirements. What I would like to do is be able to tell this formula to skip over any empty fields and only pull the ones with data in them for the naming. I would additionally like a way to tie the dash "-" to the numbers so that only the correct number of dashes appear for however many fields there are in the name. Here is the formula I am currently using. CONCATENATE({Collection ID}, "-" , RIGHT("00"&{Box No.},2), "-", RIGHT("00"&{Folder No.},2), "-" , RIGHT("0000"&{Item No.},4))This will produce a name like "collectionID-box-folder-item". Ideally I would like it to be able to produce names like "collectionID-box-item" or "collectionID-folder-item".Apologies if this is a bit unclear, I am not very familiar with this kind of work. 

Can someone smarter than me help me creating this formula 🙂I'm trying to create a body fat calculator formula based on waist (cm) , hip (cm) and neck (cm)The calculation I'm trying to make as a formula (for women) is this one:And this is what I came up with in Airtable, but it says: Invalid formula:   495/(1.29579−0.35004*LOG({Waist (cm)}+{Hips (cm)}-{Neck (cm)})+0.22100*LOG(180))-450   For now I just want to test it and use it for myself, so I've plug in my height directly in the formula which is 180cm.In my table I have a field for each measurements.What am I doing wrong and how can I make it work?

Hello,I have a table with fields like this : The "First char" field is a formula that return the first character UPPER of the "First name" field.  Then, the field "check" is used to filter a view from A to E. So i need a formula that show "OK" if the "First char" field contain A or B or C,,,,,,,,,E or else it will show "KO"Any ideas how to do it ? Thanks.

Hi Airtable Wizards, I have the following formula which is formatted in currency and occasionally returns a -$0.00 result, where to my untrained eye, I cannot see why.  {PM: TOTAL BANK ALL EFTPOS}-{PM: TOTAL EFTPOS SALES}"PM: TOTAL BANK ALL EFTPOS" and "PM: TOTAL EFTPOS SALES" are computed fields that simply tally up totals from other columns. When both of the above columns match, it should return $0.00, which for some outputs it does, and others it computes as a negative.  Just wondering if anyone can figure out why?

I'm struggling to create the right formula and hoping someone here can help me: I have Field 1 as a single select. It's a list of brands with an "Other" option. Field 2 is a write-in field for when Other has been selected. I'm trying to create Field 3 as a formula field that merges the brand names—both the ones selected in Field 1 drop-down and ones written into Field 2. I have the second half working with ... IF(Field 1 = "Other", {Field 2}) But I can't figure out how to get the first part merged in. Whenever I try to add to the formula to make it add in the non-other names, I get an error. I tried a second IF formula, like ... IF(Field 1 != "Other", {Field 1})

I have a field called "Child_of" that links the child with the parent, both located on the same table.Since Airtable does not offer backlinking to the same table, I need a formula to know if the parent has a child. I do not need the backlink to be added to the parent record, such as through an automation. I just need to know if that parent has a child, in which case the formula could return the value 1. Counting the number of children would also work, returning the number of children in the parent record.I wonder if anyone could help me with that?Many thanks, 🙏 

So in my data column A is a created time date, column B has many lots, and column C has a birth date corresponding to each lot, so if column B is: 'LotA, LotB, LotC' then column C is, let's say, would be '03/28, 03/29, 03/30'.How can I add another column containing the Age where I get the following values '2, 1, 0' which is the result of the difference between the created date and the birth date for each different lot?

Goal: Create a list of email addresses that Accepted a Google Calendar Invite, a list of people that Declined, and a list of people that NeedAction.My Data: I have 2 text fields with my data, called "All Emails" and "Attendee Status" (grabbed via Zapier).All Emails = "email1,email2,email3,email4,email5, email6"Attendee Status = "accepted, accepted, needAction, declined, accepted, declined"In a new formula, I want to display everyone that accepted. It should print:"email1, email2, email5"In a new formula, I want to display everyone that declined. It should print:"email4, email6"In a new formula, I want to display everyone that needsAction. It should print:"email3"Any help is appreciated; i feel like i exhausted the docs and attempts. Thanks!

Can someone help me modifying this formula so that if the condition 'Trækdato (FarPay)' field is empty, the formula field is left blank? Right now it returns an #ERROR!  IF(AND(DATETIME_FORMAT({Trækdato (FarPay)}, "D")<10, DATETIME_FORMAT({Trækdato (FarPay)}, "D")<10), IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD"))=2, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD"),1, "weeks"), IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD"))<2, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD"),2-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD")),"days"), DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD"),9-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Trækdat

I'm having a rollup field which is giving me the latest logged date from another linked table. The rollupfield is used in formular fields where it needs to show the date inserted into some text, but no matter what I do it gives me an error when I use the DATE_FORMAT() with the rollup field.  I have the following very basic script in my test field:  DATETIME_FORMAT({Confirmed Delivery Date},'DD/MM/YYYY')  And i tried to add an ARRAYJOIN(), however nothing seem to work.  DATETIME_FORMAT(ARRAYJOIN({Confirmed Delivery Date}),'DD/MM/YYYY')  Does anyone know where this can be going wrong?

I have tried multiple way, know I am missing something, but can't work out what. I have one column for Date and the next column is yes. Meaning that consent has been given and on the date in the column. I now need a formula field that will return the word 'valid' if the consent date is less than 365 days from today, and 'not valid' if the consent date is more than 365 days from today. Hope I have explained that well. I have so many good ideas, and I truly do try to work them out before I come here 🙂 Thanks in advance!