Formulas

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({🚩Actual Eng. Delivery Date},{🚩Original Planned P50 Date},'days') < 0,          DATETIME_DIFF({🚩Actual Eng. Delivery Date},{🚩Original Planned P50 Date},'days') = ""), 0,    &nb

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 🙂 Best,Claudia   

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} = "Refresh",      {GR/UG} = "Undergraduate"   ),   "1,500",IF(   AND(      {New/Refresh} = "New",      {GR/UG} = "Graduate"   ),   "5,000",IF(   AND(      {New/Refresh} = "R

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 have to hard code it. I want to say "only include records where TaskDate is > Start and <

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}) But it doesn't work, and I've tried adding CONCATENATE, but I can't get that to work either. I'm not very proficient with formulas, so hoping someone here can help me get what I'm attempting. Thanks!

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ækdato (FarPay)}, "YYYY-MM-10"),

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!

Hello amazing people! I am working on my film workspaces and trying to build a table to use for a Page Designer for a Call Sheet. I have several tabs I need to pull details from, all based on the Date Field. This Scenes tab is one of the places to pull from, and it has a Filming Date field.I am pulling IN to this Call Sheet tabWhat I need is if the Date on a record in the Scenes tab matches the Date in this Call Sheets record, I want the records to link. I will be using a number of associated lookup fields from the containing records.I assume I will need to use a Formula? I have trouble wrapping my head around those for some reason 😖 I've tried various other options and none will allow me to just say "If this date matches that date please link" and I can't restrict to a view because I would need to change that parameter for every new call sheet.I hope this makes sense! If anyone has any suggestions I would really appreciate it!

Hi everyone,I have a scenario one Make, sending infos from piwaa (linkedin connected inbox) to my airtableFor the module Airtable search records, I have a formula to help finding the record concernedI want it to find the right record to update thanks to my formula, -> linkedin url, OR if it’s not possible, firstname + lastname + company nameMy formula is not working and I can’t figure out why…Thanks a lot in advance for your help  

I have a column named MIN and need to add leading zeros when the value in column is less than 4 numbers:BeforeAfter Formula220022456045610000100008888888888999999999999999999This is what I have right now but is giving me an error for the largest number:IF((COUNT(MIN) <= 3), (REPT('0',4 - LEN({MIN} &''))&{MIN}), {MIN}))

If you look at my attached images below, I'm struggling with the CONCATENATE function. I want to add the COMPANY as a  prefix to each of the multiple items listed in the PRODUCTS column, but concatenate only adds it as prefix to the first item.I am doing this so that it will reference a price list. We offer the same products to each company, but each company has a different set of prices. I was going to figure out how to automate the copy/paste from PRICE CODE to PRICE, but I couldn't even figure out how to get the price code in the correct format. Thanks in advance for any help!