Formulas

I am trying to create a formal that adds a specific amount of days to a date field. The amount of days varies and is pulled using a lookup field. Initially, I was trying to use the DATEADD function but it doesn't seem to work when the added value is another field instead of a numeric value.The formula I have currently is DATEADD({Submission Date},{Health Plan SLA},"days")The field Health Plan SLA is a lookup field that pulls a number from another tab. I need this number to be what the formula uses. 

Hello!I have a list of records that all have a start date and end date column for a number of employees.  I am wondering if there is a way to calculate the number of employees on a given day.  All the employees have different start and end dates.  So for example I want to know the total working on March 1 vs March 7.  I know there is a way to do this in excel using an IF/AND formula but can't figure out how to do it in Airtable. Appreciate any and all advice!  

Hi there. I'm trying to make a custom auto number. The constant is kasse 00 and then want the autonember to be kasse 001, kasse 002, kasse 003 and so on. Can anybody send me in the right direction?And off cause will there be a problem when I reach kasse 010. It needs to be kasse 010 and not kasse 0010 best regards Thomas

I'm trying replace the following text with different values within [] with values from single select columns[art medium], [main objects], [attribute], [expression], [key light], [detailing], [camera shot], [media], [engine], [artist]I thought this would be fairly easy, but somehow can't get it to work.I tried a Substitute() like this:IF(Medium = BLANK(),SUBSTITUTE(Label, "[art medium], ", "" ),SUBSTITUTE(Label, "[art medium]", Medium)) &IF({Main objects}=BLANK(),SUBSTITUTE(Lab

Hey, I am looking for a formula field to extract city, state and zip code from a full address fieldThis is how a full address looks like "New York, NY, 10031". I want to split them 3 ways using formula, how can I do thatI used this formula to get the City   LEFT({Full Address},FIND(",",{Full Address})-1)   

I'd like to count how many linked records have occurred with another linked record. I have a table where each record is a contract between a company and a governmental entity. The company's column is a linked record and the entity's name is also a linked record. Shown below is an example table.(again, company and entity's columns are supposed to be linked record) As you can see, The company AF Engineering has two contracts with the Ministry of Electricity and one with the Ministry of Health. I'd like to find a way to count those occurrences. It can be from the Companies table, the Entities table, or the Contracts table (the one on the screenshot.) It sort of like the dictionary data structure in programming: {Ministry of Electricity: 2, Ministry of Health: 1}.  How can I do that?   

Hello,I have a rollup field with the following output:FirstName1  Last Name1- XXXX Title Company , First Name2 Last Name2 - XX Title CompanyWithout going back to the original table to pull the "XXX Title Company"  I need a formula to help split and display everything after " - " for each value. Any help is appreciated!

Dear Airtable + Apple Shortcuts Mavens,N00B, here with a N00B question.Using Airtable's API Encoder, I crafted a GET URL with filterByFormula.curl "https://api.airtable.com/v0/app[Redacted]/Items_Table?filterByFormula=%22%7BItem%7D%3D'D123'%22" -H "Authorization: Bearer [redacted]"The resultant URL works fine in CURL.When I store the URL (minus the Authorization bit) as an element in a DataJar Dictionary like this:https://api.airtable.com/v0/app[Redacted]/Items_Table?filterByFormula=%22%7BItem%7D%3D'D123'%22

Hi allIs there a way to delete the certain matching characters that might appear in a column? For example I have a column of data (800+ cells) of company names, and 90% of them are prefixed with a 'number' (that run between 1 and 9), followed by a 'period', followed by a 'space'. For example "1. Some Company Name" or "8. Another Company Name".10% of the data in the column may not have any numbers or spaces, OR some of the company names may not have a 'space' after the 'period' for example "7.Yet Another Company Name" or "And Another Company Name".Amy advice would be greatly appreciated.Stephen 

Hello-I am trying to roll up 10 date fields that are all deadline dates to a long text field in a bullet format.Ideally I would want the field name to be visible as well as the date like this (Field name/Date):- Designer due date: 1-2-23- Submittal due date: 1-15-23- Final proofing due date: 1-30-23I saw this from kuovonneIF( COUNTALL(ARRAYCOMPACT(values)), "• " & ARRAYJOIN(ARRAYCOMPACT(values),"\n• ") ) Thank you!Scott

I have a series of fields I want to concatenate together. The final result needs to include a semi-colon in between each piece of data, but not all columns have data. So I was working to do an IF statement to check if the last of the columns (far right column of data I want) had data, if so, concatenate it with the other fields. The formula I came up with (but is not a valid formula and I can't figure out why) is:   IF({Revenue Rolodex}, CONCATENATE({Revenue Rolodex},”;”,{VIP Day Social},”;”,{4M Event},”;”,{Applied to DID},”;”,{DID Calculator},”;”,{VIP Day Bootcamp},”;”,{2022 Advent Calendar},”:”,{VIP Day Quiz},”;”,{FREEBIE: Roadmap},”;”), IF({VIP Day Social}, CONCATENATE({VIP Day Social},”;”,{4M Event},”;”,{Applied to DID},”;”,{DID Calculator},”;”,{VIP Day Bootcamp},”;”,{2022 Advent Calendar},”:”,{VIP Day Quiz},”;”,{FREEBIE: Roadmap},”;”), IF({4M Event}, CONCATENATE({4M Event},”;”,{Applied to DID},”;”,{DID Calculator},”;”,{VIP

Hello everyone, I would like to create a form where people can add their event dates (event begin at 1st January 2023 and end at 2nd January 2023) and prevent the possibility of the end date to be before the start date (event begins at 1st January 2023 and end at 31 december 2022).To sum up I would like people to not have the possibility to make mistakes with their event dates.Thanks for your answers ! 

HiI am setting up a zap connecting stripe to airtable.I want the zap to find a record in Airtable that matches the stripe data in 2 fieldsTo do this I need to enter an AirTable search formula into the zapThe 2 Airtable columns I want it to search are “Email Address” and “Subscribed to”So let’s say that a change is made in stripe that fires the Zap.The zap will search Airtable for a record where both the “Email Address” field and the “Subscribed to” field match the data in both those fields in stripe. The combination of the 2 fields indicates it is the correct record to be updated with the changes made in Stripe.What search formula would get this result for me in my Zap?ChatGPT helped me get to this formula:"{Email Address} = {Stripe Subscription}[customer.email] AND {Subscribed to} = {Stripe Subscription}[items.data.0.price.nickname]"but I'm getting the message"Failed t

I looked at the posts about progress bar formulas and I have used them but my result is not looking correct. In the post, they said you need to use chrome and not safari I am using the latest version of chrome and its sill not working. Can some one please help me.

 Hello everyone,I have to create a calendar of course sessions and I have a table in which I enter in one column the date, in one column the start time and in another column the end time because different days might have different times.I use the formula DATETIME_PARSE(CONCATENATE(DATESTR(Date),' ',{Start Time})) to create a date & time that can be displayed on the calendar. However, the result of the formula returns the wrong time, increased by one hour.Am I doing something wrong or is it a bug?Thanks for any assistance!

Hi, new to Airtable and I'm trying to parse out specific records from a lookup field and have Airtable display them in a new column. In one table called "Downloads",  I have a lookup field called "Guests" that is pulling records from a separate table called "Guests". The lookup field returns the names of all the "Guests" for a given entry. There a multiple guests per each entry. In the Guests table there is a variety of data about each individual guest (demographics, affiliation type, etc.). In my Downloads table, I want to create a new column that displays all the Guests names of a certain type (e.g. only Guests that have "Affiliation Type" = "Media"). How can I do this in Airtable? My attempts to create a formula aren't working as I can't get Airtable to return the Guests names. Thanks!

Hi everyone, I’m trying to figure out how to calculate the Mode (ie. the most frequently occurring number in a series). I need this for the purposes of turning crowdsourced data into something useful. For example, if I ask 10 employees of one company how many days of holiday they get a year, I want to return the value most frequently cited, not the average (mean) because that would be inaccurate. In google sheets, it’s simply the function: MODE() but Airtable doesn’t have this. Does anybody know how I might do this?

Hello, Is there a way to configure this formula to only return a time during specified business hours (7:30-4PM)? DATEADD(WORKDAY({Last Modified},1)) Thanks :slightly_smiling_face:

Dear Community,I am faced with the following problem:I want to pull data from a different table when the status in each table is the same.I am creating a text.Say the status in Table 1 is "First Text" then it should check in Table 2 for the row named "First Text" and pull the information from a specific cell in that roll.In sheets it looks like this: =IF(A4=Table2!A2;Table2!B2;IF(A4=Table2!A3;Table2!B3);0)How can I replicate that in Airtable?Thank you

I'm trying to clean up my text with Regex. Some ranges are listed as 3.0 - 3.0 which I want simplified to just 3.0 whenever the two numbers match. I tried using regex101 and MDN references, but Airtable seems to use some different syntax than either of those.The following expressions should be matching but aren't. Does Airtable not support these "back reference" expressions such as \g{n} or simply \n(\d.*) - (\g{1})(\d.*) - (\1)Desired Result:3.0 - 4.0 ==should remain 3.0 - 4.03.0 - 3.0 ==should change to 3.01.5 - 1.5 ==should change to 1.51.0 - 3.5 ==should remain 1.0 - 3.5 

Hi everybody !I would like to know how to add in column that features only the similar values found in columns A and B ?(In my case the A and B columns are Lookup from two différents tables but I guess it works the same).Thanks a lot for your help !

Hey guys, I've got a new challenge in my quest to create a base for tracking personal finances.  A quick review:TablesCategories (I call them buckets)TransactionsDesignationsTransactions are either entered manually or bulk imported via csv from my bank.  Each transaction is assigned at least one category via the designations join table.In the Categories table, I have assigned each category a monthly budget.  Then I roll up the designations amounts filtered by month (this is my current setup - I'm working on a different option).  Now, you would think that a simple third formula field for [ budget - total spent (roll-up) ] would give me the balance for each of my budgets for the month.  Should tell me whether I've received all my salary for the month or if I've gone over budget for eating out, for example.Here's my confusion:  there are situations in which categories may have

Hi there, I have a single select field name "Département" and I want to concatene the 2 first letters (later for automation).I have a problem with this formula :IF(  OR(    {Département} = "75",     {Département} = "78",     {Département} = "77",     {Département} = "91",     {Département} =&nbsp

Hello!I have created an estimates / intakes base for a client. One of the goals of creating this was to have an automation that sent a confirmation email to the customer once their estimate was scheduled. When I tried to insert the Estimate Date/Time field directly into the email automation, it was not working. Someone suggested creating a different field that makes the formula more 'readable'. However, for some reason, the date is not converting properly:As you can see, while the time converts properly, the date is way off. Here is the formula I'm currently using: Can anyone identify the problem for me and offer a solution? Thanks in advance! 

Hi there,We recently decided to use Airtable as our payroll software, everything is fine but there's one problem and it's about the automatic calculation of overtime per week.We found a way to calculate those hours but it involves manually copying the sum of all the hours worked per week in the "Total heures travaillées" (total hours worked) column, we find this number of hours in the "sum" of the column "Durée" (duration). Thus, copying this sum in the column "Total heures travaillées" (total hours worked) allows us to see the total of overtime hours per week in the other column "Heures supplémentaires" (Overtime hours).In conclusion, we have to manually copy all the total hours worked per week to have the number of overtime hours so I wanted to know if there's a way that overtime can be calculated automatically without having to do anything and that we can delete the column "Total heures travaillées" (total hours worked)?For your information, in France