Formulas

Hello everyone,Looking for some help on a problem I'm trying to resolve in Airtable. Context and goal of the databaseI'm building a database of our members. The first table lists our members and their level of membership and the second table lists the training courses we offer.Back on the first table, I then inserted a lookup column to say which training course the member has taken. So far so good, everything works.Here is how it looks like so far (sorry, all in french) : Our end goal is to send to each member, at the end of the year (when it's time to renew their membership), a list of all our trainings, and for each one, show them which ones they took part of or not. If they took part in the training, it would say "Yes" and if not, it would say "No". We plan on using the page designer extension to build that report.To do that, I added a lookup column that lists all activities offered so I can insert them in the "page designer" extension. Here is how it looks at this point (

Hi,I am trying to check if the Cell "vB" has a value or not using  IF({vB} != BLANK(),"true","false"). But strangely, even blank cells are spitting out the value "true". No clue why. Can anyone help?

Hello Community,I have tried to find this answer but come up with nothing so far.I wish to create an If Statement based off a Link Field which contains words.Below is my issue:'Proposal Type' is the Linked Field which can have multiple different word options.My objective is if 'Locking' is selected in the 'Proposal Type' field, return the value 0, otherwise return the Minimum Freight Total. (i.e. locking doesn't include freight).The formula I believe should work is:IF({Proposal Type}='Locking',0,{Helper Minimum Freight Total}) However regardless of the Proposal Type, the value is always returning "Helper Minimum Freight Total"   Any help would be greatly appreciated. Thanks Paul 

Hi, I have a database for a marketplace and I want to calculate average rating for each service. I have a table called Services, which lists each service, and there is a column called Reviews, which links to a table called Reviews with a primary column that has the formula for listing the number of stars numerically (rating). How would I get the average rating for each Service and have that as a column in Services?

I am hoping there is a formula to achieve this... but not sure. I want to automatically generate the formatting that is in the "IDEAL" field on the right, that way this does not have to be done manually.Any help/thoughts would be appreciated!

I have two lookup fields that are an array inside my table. One shows the number of products (30, 30, 30, etc.) and the other show the name of the products (product name 1, product name 2, product name 3, etc.).If the fields have just one product, I can easily concatenate them into "number" & "name" (e.g., 30 product name 1), but that doesn't work on multiple arrays. I'm trying to combine the fields such that the output will be number and name in order (e.g., 30 product name 1, 30 product name 2).It seems like ARRAYJOIN() into a list is the appropriate function, but I can't get it to work. The formula of IF(  ARRAYJOIN({number}),  "- " & ARRAYJOIN({number} & {name}, "\n- ")) produces "- 3030product name 1product name 2" instead of "- 30 product name 1 - 30 product name 2." Any ideas? 

Hello guys,I have a field that use a simple formula to calculate the time passed (in minutes) between the created time of a record and Now that goes like this: DATETIME_DIFF(  {Created},  NOW(),  'minutes') * -1 The time displayed in minutes on the field does not update every minute.If can go from 1 minute, to 5 and then to 8 or 9. My question is:How can I have this field value update by the minute of even in seconds if the formula shows the time in second? Hope I explain my self correctly 🙂 Thanks in advanced

Hey Airtable community!I'm working on a table where documents expire but not necessarily I need update them or actually mark them as expired, so my action depends on what the owner of the document decides.My idea is to send an automatic e-mail when a document expires, where it would be a brief informative with 2 options: mark documet as expired or update document. I want this options to be buttons where the person only has to click and I would receive an automatic e-mail with the response. Does anyone know a way of doing this? 

Hi All,I've been trying to use the below formula to exclude weekends, so rather than showing 7 days to show 5 days instead. it seem to make sense using 3 but I'm not sure why? when the formula is returned  and Date Reservation Time Stamp is blank it returns NaN Days Remaining is there a way to say IF {Date Reservation Time Stamp} is blank show as Blank on the output? formula I'm currently using DATETIME_DIFF((WORKDAY({Date Reservation Time Stamp},3)),TODAY(),'days') & " Days Remaining"

I have fields for "First Name" and "Preferred Name." I want the "Preferred Name" field to default to the value in "First Name," but still be editable if a different preferred name is desired. I also want to implement a similar approach for the mailing address, where the "Mailing Address" field defaults to the value in the "Physical Address" field but allows for easy editing when needed.Any ideas on how to achieve this without duplicating fields? I appreciate your input and suggestions!

I have a database that shows the user as a single-line text, I want to create a column that looks at that single-line text and converts it to a user that is listed in my database. Is there a way to write this formula?

This feels really silly but I can't get the LEFT, MID, RIGHT, functions to work with variables at all. It just keeps giving me an error no matter what I try. It works with a string in the function but not if I use a variable. Can someone tell me what I'm missing? I'm sure it's something simple...//Formula that doesn't work: LEFT({Text Phone}, 3) => #ERROR! //Formula that does work: LEFT("123456789", 3) => 123

I am struggling to find the formula that I need.I am trying to calculate the total guide cost for a trip.  I have a multiselect field for the guides that will be on the trip, then in the guide table, I have their rate per hour of guiding.  So on the main table, I have a lookup field that displays the rates for each guide.  I need to now multiply each rate by the 8 hours and then by the total number of days they are out,  and add them together.  https://airtable.com/shrDEDCYEnQAlksAF   

Hello, I have a field that is using a formula in order to create a url. the formula is a concatenate to build a url to a form with prefill value. I then want to use it to send a email with gmail. And little thing I do not want to print the url but replace the text with “click here” I tried several things : in the body of the email [click here](record(step1…) I change the formula in the field it self in order to format. the field is ok in the table but when I send it the URL is there and the markdown is not interpretated… I realize that my URL : https://airtable.com/xxxxxxxx?prefill_First+name=Alexandre&prefill_Last+name=Husset&prefill_Email=alexandre@husset.fr&prefill_LinkedIn+profile=https://www.linkedin.com/in/alexandre-husset-61181a/ all the _ just disappear in the email https://airtable.com/xxxxxxxx?prefillFirst+name=Alexandre&prefillLast+name=Husset&prefillEmail=alexandre@husset.fr&prefillLinkedIn+profile=https://www.linkedin.com/in/alexandre-husset-61181a/

I'm trying to return a value if the date matches any of a string of dates. I'm trying to make sure dates don't land on company holidays. Thanks!

Hello--I have a base filled with content that we publish every day. I'd like a view where I only see content that is supposed to publish on a Monday though, but the options for dates don't have that capability. Any ideas on how to do it?  Thanks!

I have a long text field that takes the food and the preference of the user.Sample Input:Burgers (Enjoy), Pasta (Ok), Seafood (Nope), Pork (Ok), Beef (Ok), Vegies (Enjoy) I want to get all the texts that are "Enjoy"-ed by the user.Expected Output:Burgers, Vegies What formula can I use to get this output?

Hello all,I'm formulating a formula to get the total number of days requested for PTO. The current formula I have is:DATETIME_DIFF({Event End}, {Event Start},'days')The output of this displays the days, but also accounts for the weekend days as well which I don't want.I have a separate table labelled: "Holidays" with a column for holidays showcasing the federal holidays with a format of "1/2/1991". I understand I'd need to use the WORKDAY_DIFF, but don't know how to incorporate that onto: DATETIME_DIFF({Event End}, {Event Start},'days').Any help would be appreciated. 

I an Airtable newbie. I work at a public library and use Airtable to manage and track our booklists. Since there are no sales or orders per se, we calculate usage using physical counts. Every few weeks I count them and record in Airtable the current qty of each booklist and the number added (if any) for each booklist. It's easy to determine all time usage: ([starting qty]+[number added])-[current qty]. But I am stuck on how to derive annual usage via formulas.There are two tables involved: Booklists and Inventory. Booklists uses rollup fields to grab numbers from Inventory. Inventory has fields both for qty added and for physical count.What I want I a view grouped by year, sub-grouped by booklist, showing annual usage. I can brute force it by adding a new field every year for the ending qty, but I'm hoping there's a more elegant way to do it via formulaThe formula would be something like this for each booklist: For a particular year, find the earliest record date. Get the number from t

PLEASE HELP  ME I need to a formula like this. =IF($G4<=200;5;EĞER($G4<=600;7;EĞER($G4<=1000;9))) How is write on air table thank you in advance 

Apologies in advance if this is elsewhere or common knowledge or the query is posted in the wrong location (Formulas). I did look all around to find a similar Q&A elsewhere (found some close but none exactly what I was looking for), so...TWO sample use case needs for the following:1) Gardening: calculating when to sow or transplant a crop indoors or outdoors in relation to estimated frost dates.2) Annual Events (film festivals, awards shows) that occur at about the same time every year.Q1: How can I have a date withOUT the year? (reworded: How can I set up ballpark seasonal dates to faciliate copying over every year?)A-ish? I know I can do three columns (month, day, year) - but then I can't set up an annual calendar subscription, right?Q2: Is there a way to do a formula to calculate X# of weeks before a key date (i.e.: to add or subtract from the earliest frost date in a column).?Thank you!

Hey all, I’m wondering if there is a way to perform element-wise multiplication of two arrays of numbers. For example, if I have two fields with arrays [1, 2, 3] and [4, 5, 6] respectively, can I write a formula that produces [4, 10, 18]? As an example use case, I could calculate a weighted average of an array of numbers [10, 20, 30] with weights [.2, .5, .3]. After element-wise multiplication, I could take the sum with SUM([2, 10, 9]) = 21 (In this example, I’m just taking the dot product of these two arrays).

Hey guys, I'm currently stuck on how to formulate a profit based off the total value of the invoice. This is what I currently have:I have the formulas needed to determine the selling price of each product, however via my "Invoice" tab, I have this: Where I'm stuck at is, how to formulate the formula to determine the gross profit of each invoice in their respective columns.Any help is appreciated! 

Hello,  I am trying to use the WORKDAY function to add X amount of days to my start date, however it is populating an extra day but only if I include the time. I don't need to include the time but its the only way I can get the project "duration" to show up on the calendar or timeline view. Can anyone help please? I've tried to add -1 days to remove a day from the start day but it is telling me there is an error. Current Formula: WORKDAY({Start Date}, {# of Days}) Thank you, 

I have a formula that is achieving 2/3 of my goals, but I need some help figuring out that last 1/3! I am trying to add an "SOS" flag to any to-do that meets one or more of the following criteria:Has been manually flagged as "Behind" in the Alerts column.Has not been given a Due Date (so the Due Date field is blank).Has a Due Date that has passed AND it has NOT yet been marked as "Done."How would I structure that final part of the formula? Currently, I have: IF(     OR(          Alerts = "Behind",          {Due In} < 1     ),          "🆘",          "")I am trying to get the "Post job description on social media" task to be flagged, since it is overdue and not marked as done.Thank you so much!