Formulas

I built a task management table that tracks, start, due, and completion dates.The start date is logged by an AT automation that pulls from a field called Todays Date. That field is set to PDT in the field options.The due date pulls a unique SLA from a field using this formula: {Start Date},DATETIME_FORMAT(SET_TIMEZONE(WORKDAY({Start Date},SLA),'America/Los_Angeles'),'MM/DD/YYYY h:mm A'),"")) The complete date is logged using the same automation method for start date.Where I'm encountering an issue, is how early or late a task was completed. So duration of time before or after the due date. I've tried a few different methods, but I feel like this one should work: DATETIME_DIFF({Complete Date},{Due Date},'hours') The DATETIME_DIFF formula produces wildly inaccurate results. I've tried troubleshooting by adding an hours adjustment for GMT to PDT, or adjusting

I've been banging my head on this one for a while so any outside ideas would be more than welcome!We have three tables.Inventory: contains list of materials and their initial amounts. Each material is treated as a batch material so once it runs out, it's permanently out. Recipes: As the name suggests, contains recipes that can contain multiple ingredients. For each ingredient amount (or rather ratio of ingredients...) and few other details are given. Lookup fields bring some info from inventory for each raw material.Production mix: A right recipe is chosen and how much total material is wanted to be produced. Formula fields then calculate how much of each ingredient is needed. lookup fields bring the names of raw materials from recipe tables.My problem is that rollup does not seem to work over three tables . Basically I would need to take the information of how much of each ingredient the production mix uses and then get the inf

Heya,just a Formula newb here 🙂 Im Trying to set automaticly the Date 3 day before. When i set a Date manualy in a Field, another one should display a date 3 days before excluding weekends.I just only managed 3 days before but weekends are still included.Here my formula (Datefield is my manual selected field)Formula:  DATEADD(DATEFIELD, -3, "day")      Thanks for any advice!

I have 2 tables.Table 1 contains column ATable 2 contains column BNow, I want to check if the text appears in Table 1 also appears in a specific cell in Table 2.For example: Table 1   Table2text         text, text6, text99text3text4So in this example I will get a TRUE only for 'text' since it's located on Table 2

Hi, sorry in advance if this question has already been answered.I would like to compare two sets of date from different table, in the Table A I have a field with single select date that i want to compare with the Table B with multiple select, if in the Table B one of the data is the same I want to show the name of the person. If you need a context, I want to see the mentors that can help a student from a field that they choose, the mentors have given us many area of expertise and the student just one area of expertise, so from those two datas i want to do the matching and see the name of the mentors that can help this student.Thanks in advance!

Hi,I'd like to ask people more advanced than me to see if there is a way to do this.I have 2 tables: a Contact one and an Articles one.Each one has a multiselect field that hold the same data: Contacts select their interest (i.e. Sport, Tourism, etc) and Articles are tagged based on their topic (Sport, Tourism, etc).What I would like is to find the Articles for each Contact: a match would be considered if at least one of the Contact's interest matches at least one of the Article topics.I found a way to do this via code in a script, but scripts have limitations and I have around 5k contacts and around 10-20 articles to search for. Ideally I would like to do this with formulas or linked records or something in the UI.Otherwise I'll just resort to Make.com.Let me know if you have any suggestions or ideas!Thanks,Stefano

Question, I need a filed called START to pull from different fields that have dates:For some reason only the Game Development date is being pulled, any idea's? SWITCH({Stage},IF({Stage}='READY FOR GAME DEVELOPMENT',DATEADD({GAME DEVELOPMENT},'','DAYS',IF({Stage}='READY FOR CREATIVE DESIGN',DATEADD({CREATIVE DESIGN},'','DAYS',IF({Stage}='READY FOR PRODUCTION',DATEADD

I have a field that shows the week of the year, but I break my records up sometimes by leaving some fields blank. How can I get rid of the #ERROR! message that is shown whenever there is no date (or week) in that field?Here is the formula I use: DATETIME_FORMAT({Date to be Posted},"w")

Hi everyone,I'm trying to solve a workflow/formula problem I've run into.Goal: I am building a task tracker, and it's important we have a history of steps already completed for each task. I have built a table that lists the tasks, and one of the columns is for "Most Recent Update," where team members can enter a description of the current status of the task (e.g., Jon talked to Kathy, and they want to present it during the next meeting).Issue: I want to somehow automate the transfer of any new/edited text in this field to an ongoing list of all the previous "Most Recent Update" edits. In my head, this would be a bulleted list in another column that has a bullet added to it every time the "Most Recent Update" column for that task is updated. I don't want to just have a column with an ongoing bulleted list that folks edit as the interface version of this table needs to be user friendly, and I want to be able to show just the curre

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 e

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"  

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},

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 advanc

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&am

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!