Formulas

I’m hoping the community can help me out. I think that my formula is a bit off. Here’s the logic I’m trying to do in the Difference field: If Baseline Value is empty, leave Difference empty If Baseline Value is 0, use the value in Target value If Baseline Value is NOT empty, calculate Target value minus Baseline value divided by Baseline value so I can get the percentage difference As you can see, if Baseline Value is 0 or empty, there’s an error My current formula: IF({Baseline} != BLANK(), IF({Baseline value} != BLANK(), (INT({Target value}-{Baseline value})/{Baseline value}), IF({Baseline value} = 0, INT({Target value}), BLANK() ) )

I’m trying to set up a date/time field. I’m in Israel and I’d like the timezone to be NYC. My first problem is that when I use CREATED_TIME() to get the current time, the time is off by 3 hours (behind). Then the Set_Timezone function is not calculating properly either.

Hello! Does anyone know how you can transform a week number and a year number to a date?

I need the formula to display the duplicate values found in a rollup. I can’t do ArrayUnique because I actually want to know what the duplicate ones are and there are too many combinations for me to code it using an if statement for each result. Thanks!

Hello! I am new to Airtable and have been chugging along until now lol. I have done hours of research and received advice from a few people but I am still very much stuck. I have a multi select field and I am trying to write a formula that takes the options in that field and replaces/substitutes them with values from other fields. So let’s say my multi select field contains the following values: Buyer Agent Closing Attorney Closing Attorney Assistant I want a formula that looks at each value and replaces each one with an email address from a lookup field. ‘Buyer’ would be {Buyer Email} ‘Agent’ would be {Agent Email} ‘Closing Attorney’ would be {Closing Attorney Email}’ ‘Closing Attorney Assistant’ would be {Closing Attorney Assistant Email} The email addresses would need to have commas separating them. And if there is no email address in any of the lookup fields it would just ignore it. Please help!

Hi! Is it possible to compare single and multi-select fields across columns in one table? Would a formula be able to do this? I have a single select field listing the launch month for content. Then there are 2 lookup fields with the launch months for connected content (each with multiple months selected). Ideally, I want to compare the single select field to the 2 lookup fields and output anything that is after the single select field month. Would these fields need to be dates rather than single or multi-select fields for this to work? If so, is there a way for multiple dates to exist in one field? Or is there another way to do this? Thank you!

Hi All, I’m in the process of creating a catalog of related books that I can hopefully email out to customers, and, so far, that’s involved creating an Entries rollup field in my Catalogs table that pulls from my Books table using the ARRAYUNIQUE(values) aggregation formula. I did as much formatting as possible in the Books table, adding caps with UPPER() and spaces with \n, but I can’t figure out how to eliminate the comma after each rollup field item. I tried this: SUBSTITUTE (ARRAYUNIQUE (values), “,”, " + ") But I kept getting “Sorry, there was a problem saving this field. Please check your formula text.” I’ll add a screenshot here so you can see what I’m talking about and why I’d want to eliminate the comma . . . Thanks in advance for your suggestions!

Hi I would like to change the date format returned in my formula field - I’ve tried using datetime-format but keep getting an error DATEADD({Date Invoice Raised}, 30,‘day,’) DATETIME_FORMAT(‘DD-MMMM-YYYY’) thanks Clare

Hi I would like to add a formula field which calculates when an invoice is due for payment. I have an ‘invoice issued’ date field and would like to use that to calculate +30 days. this is where I have got to but its returning an error DATEADD({Date Invoice Raised} +30,‘day’) many thanks Clare

Hey Guys, first time here. Airtable support forwarded me here. I need your help. I have a such formula: IF( {routine type}=‘daily’, DATEADD({task date}, 1, ‘days’), IF( {routine type}=‘weekly’, DATEADD({task date}, 7, ‘days’), IF( {routine type}=‘bi-weekly’, DATEADD({task date}, 14, ‘days’), IF( {routine type}=‘monthly’, DATEADD({task date}, 30, ‘days’) ) ) ) ) and I would like to combine it with something like this: IF( WEEKDAY({next date})=0, DATEADD({next date}, 1, ‘days’), IF( WEEKDAY({next date})=6, DATEADD({next date}, 2, ‘days’), {next date} ) ) Can you help me, please? The first formula creates new task depending on the routine type. Weekly routine, monthly routine, etc. The second formula should move the task to the Monday if the first formula lands task on the Saturday or Sunday. Appreciate your help. Jack

Hello community I was wondering if you could help me. I want to make a formula that shows me the emoji “ :white_check_mark: ” if the names and emails are in the contact fields, otherwise it shows me “ :x: ” but I don’t know what I’m doing wrong in my formula. Could you help me?

Hello- Any help on not showing negative days here, just show if the number is negative, it says zero? IF({DESIGN DUE DATE}=“”,‘’, CONCATENATE(DATETIME_DIFF({DESIGN DUE DATE}, {TODAY’S DATE},‘days’), ’ Days ')) Thank you!

Hi everyone! This is my first post in the Airtable community, and I have quite the ask. I need to create a formula that is able to look across four different fields in the same table, and check if those fields have one of two particular values (Let’s say the possible values are ‘Complete’ and ‘None’). If all four fields have either ‘Complete’ or ‘None’ at the same time in the same row, I want the formula to print ‘Yes’. If any of the four fields have a value other than ‘Complete’ or ‘None’, then the formula should NOT print ‘Yes’. I have tried using a combination of IF, OR, & AND statements, but this has proven to be quite the formula, and I am not the most experienced coder. My reason for this request is that I want to be able to filter off the value this formula creates, that way I can see all of the tasks in my base that are in progress. I hope this makes sense, any input is appreciated!!!

Hello all! I am trying to compile a formula that dynamically spits out a value based on the values in 2 other static data fields, the first being an Additional Authors Count that counts the number of authors that exist on a record and the second field being a deliverable field. The end goal of this formula is to compute a number amount that a freelancer should get paid based on completely specific deliverables and taking into account the number of authors on said project they worked on. For example: If a freelancer works on a project deliverable of content calls and they do so on a project with an author count of 2 additional authors, I want the formula to be able to spit out a number based on those two external values and the static values that live in said formula. I’ve been able to get this far, but now need to somehow add in the deliverable field condition so that if the deliverable is Content Calls and Additional Author is 1 then add 1000 to the existing multi author amount (which

Hi! This one seems quite easy, but having a tough time to loop LEFT and RIGHT to make it work … I have the following examples 5-DBR_4.JPG 5-GD-WHI_1.jpg In both instances, I want to extract the text between the “_” and the “.JPG”, so “4” and “1” respectively. This is to populate an {Position} field. What is the right formula to use for this?

Hello! I have seen similar posts but cannot seem to get them to work. I have the following string of text: New voicemail in mailbox 1 from “AWESOME DUDE” <555-555-5555> I am trying to extract AWESOME DUDE and 555-555-5555 to seperate cells. I have tried the solution suggested here but cannot seem to get it to function, as it returns a -1. Any assistance would be greatly appreciated.

Hey people, please help me here. I am building a new house and I need to understand pricing for all items we need to buy. To clarify, here is an example: Here I need the fiels "M2 from Walls"to show a sum of the size of all walls on the multiple select column. My objective is for example: When I add a new price for wall painting’s square meter I can multiply this price to all walls on my multiple select field. So, If a square meter of wallpaper is $10 And I have a room with 20 sqm and another with 30 sqm It would give me 50sqm * $10 So the sum of that room’s cost would be $50 Makes sense? Can you guys help me please? Thanks a lot

Hello- Looking for a solution please, I need a formula or a zapier suggestion - for if I have a attachment field with 4 versions of a design, and I want to auto send the last version added say version 4 to a new field that only show the latest revision - possible? Thank you!

Hey, everyone! I’m having a really tough time tracking down the issue here. I have two forms, both of which have a field that I’d like prefilled. The facts are: 2 tables: (Companies, Vehicles). Entries in Vehicles are linked to a Company. Each company has a gallery view, filtered to only show vehicles in that company. The primary unique field in Vehicles is the VIN. 2 forms, 1 for each of 2 unique companies, and the field names are the same on each form. The VIN field on the form is linked to the gallery of the respective company, thereby constraining to only those company’s vehicle’s VINs to be selectable. When I fire up form 1 for company 1, no problem! The VIN is pre-selected without issue and works exactly as described in link_records_form_demo - airtable. No issues whatsoever. When I fire up form 2 for company 2, the VIN is never preselected, no matter what I seem to do. I’ve tried the troubleshooting steps listed here: Prefill not working - #2 by ScottWorld Change your primar

After through the documentation on DATETIME_PARSE() I’m not seeing the timezone respected when used in conjunction with either SET_LOCALE() or SET_TIMEZONE() for conversion of a string field containing date and time information. What am I missing here:

I am analyzing data from an evaluation form where all field are mandatory. When reporting them using Page Designer I want to omit any entries where the person has typed n/a (with any combination of capital or lowercase letters). My first thought was that there needs to be an IF statement in there some where, but where? Currently I have: ARRAYJOIN(ARRAYCOMPACT(value), “/n/n”)

I have the following phone number +33661291700, do you know a formula that converts it to a US phone number? (e.g: (415) 555-2671) Thank you

Hello All, I am trying to convert a concatenated text cell into various records, split by the semi-column. Ideally, an automation would somehow split the cell contents so I can return the linked lookup value for both Optimisers, and Double Storey and their relevant costs in another lookup column. Is there a way I can split the contents into separate records for the pricing data column?

Quick question, I need the Item Description to not be blank at all times (I need to see it in the primary field regardless), the way this is set up - the task needs to be selected to make the Item Description appear, thoughts? IF( AND( {ITEM DESCRIPTION}, {TASK TYPE}, NOT( {TASK NAME} ) ), {ITEM DESCRIPTION} & " - " & {TASK TYPE}, IF( AND( {ITEM DESCRIPTION}, {TASK TYPE}, {TASK NAME} ), {ITEM DESCRIPTION} & " - " & {TASK TYPE} & ": " & {TASK NAME} ) )

I have a “Lookup” field that shows me many values ​​in the same field according to the tab relationship. For example: It is related to a field called orders, the lookup shows a field with the number of orders, and the next lookup shows the month in which they were made, but it shows me the same repeated many times, (July, July, July, August, July , August). What I need is a formula that breaks me down and shows me only the months (July, August).