Formulas

Hello everyone,I can't create a formula to find pieces of text in the "Libellé" (Text) field and transform them into a word.Example :IF “PAYMENT” or “PRLV SEPA” in the "Libellé" fieldTHEN “VER” in the formula field

Hello.I need to calculate the average of the entire column (called total score) but using the average formula I am getting the average of each cell. Is there any solution please?Thanks

Hello,I have been googling this for a few days now and really need some help!I am trying to set up an existing customer email reminder automation that uses date the next time we are going to visit a venue. The trigger will start a month or so before the visit date. A new customer fills out a form and a record is created. This gets linked to our Class bookings table with the venue that they have visited. The bookings table is linked to a classes table which shows each venue and the 4 dates we visit that venue. So far i have managed toLookup the dates from Classes in the class bookings table - this gives me an entry with all 4 dates in the same cell. The format of this row is a date. (perfect)In the customers table i am able to use a rollup to get this date across and then use an ARRAYSLICE to get the individual dates i need. They come in a really long form

What can I use in a formula field to copy the date from one field to another without changing it into a text string?For context, I have install dates and site visit dates in the same table, and the dates are in the same column for 'start' and 'end'. I am setting up 4 more fields (Site visit start, site visit end, install start, install end) that will remain blank if the date type does not match. However, I can't find any way to keep the date as a number/ date format and not a text string? I basically just want to copy a date (unchanged, not adding or subtracting days or I could use DATEADD), but the only one I can find is DATETIME_FORMAT but this changes it into a string. Can anyone help? I understand I could use automations to copy the date, but I'm trying to limit my use of those because I am nearing the limit of automations per base! So a formula would be much better if possible. Code I'm currently using is below:IF

I have a table where static information about my volunteers lives.In it, I have a field (Static Notes) with an IF statement that looks to see if they have a check mark in "Qualified" (to walk dogs) and if they do, it returns "DDW" (designated dog walker), which then shows up on the schedule.Can I write a nested IF statement that looks at a different field altogether (Training Status) and if they "Need More Training" returns "Trainee" in the Static Notes field (the same field where DDW would appear)?Or do IF statements only distinguish between options in one field? And if that's true, how would I get various information (other qualifications, for example) to show up in my Static Notes field?Thank you! (I'm learning all of this by feeling my way in the dark - appreciate everyone who is willing to take my elbow and keep me from crashing into furniture along the way!)

Getting and #ERROR! when using ENCODE_URL_COMPONENT() on a lookup field of a text field. Can the formula be used on lookups?

Hi,Is there a way I can create a formula that will determine the Monday for all these dates and return just one date. For example, in this table it's looking up data from another table, in this case the Monday for this set of dates is the 12th. How con I accomplish this? I need it to create a "end of week".. which I already have the formula for that. Please help.

Dear all, With my team we are using airtable to do systematic reviews and extract data from science literature papers. For each study we created 2 rows and each one is assigned to a reviewer. So, for example, we have the coloumn “sample size” and reviewer 1 writes 89 in his record and reviewer 2 writes 98 in her record. Let us suppose that there is an incrugruence. I am using rollup function to ask Airtable to show me the content of the two cells. First I used rollup function ARRAYUNIQUE(values). It was ok but if a reviewer did not report the value he should had reported, I cannot see it and it might be useful to see this discrepance between filled and unfilled cells. Thus I used the ARRAYJOIN(values) to see both the values. It fixes the problem above because I can see what both reviewer have extracted, but for long text it might be difficoult to spot differences. So, here it comes my question. Is there any direct or indirect way to compare with (or without) roll

HelloI have a lookup field that lists every area code in each time zone.  These are listed by zip code within each time zone so there are a ton of repeats.  You can see them in the area_codes field:I've tried to create a formula field (UniqueAreaCodes) to remove all the duplicates but as you can see it is still outputting some duplicates.  I have tried ARRAYUNIQUE(area_codes) as well as ARRAYUNIQUE(ARRAYFLATTEN(area_codes)).  These provide the same result.  I've also tried a myriad of other complex formulas with no luck.  I think part of the problem is that area_codes field is seperated by a carriage return and is not all one string.  I tried to put that field into a continuo

I am looking for a way to calculate some percentages in my base. There are a few things I need to do first:1. I want to only include records listed as "Budgeted" or "Not Budgeted", not any "N/A" records. (this removes TEST 1 and TEST 4 from my calculations)2. I also want to only include records with an authorized budget entered, so exclude any that the authorized budget=$0.00 (this removes TEST 2 and TEST 3 from my calculations)3. Of those filtered to only budgeted or not budgeted and not $0.00, I want to count the ones with a remaining budget greater than 0, and count those with remaining budget less than zero, getting a percentage of the two. (in this table you can see there would be 50% on budget, 50% remaining budget is negative.The end goal is to display the percent of records on budget, the percent of records over budget, and possib

Hello. Still fairly new to Airtable and working to better understand formulas. I'm looking for input on the following.We have a rental space where rates are set based on duration (up to 4 hours, up to 6 hours, all day). I've set up a table for Rental Rates. Separately, I have a table for rental details, and this includes a duration field ("1st Day Duration") calculated based on rental start and end times.I'm trying to figure out how to create a formula that calculates the correct rental rate based on the duration and then pulling the correct rental rate. So for example, if a duration is 3 hours, the fee would be $x (since that's the "Up to 4 hours" fee). But if the duration is 7 hours, the fee would be $x since it would be a "Full day" rental.One other variable. I'm not sure how to plan for future rental rate changes, so that when rates change existing entries do not change (only newer entries). I'm assuming this starts with a date variable tied to rate increase

Hi, everyone! It's my first post, and I'm relatively new to Airtable and to databases more broadly. Hoping for some help.I'm using a base to organize staff for a conference. Volunteers are attending some sessions and working other sessions. I have a field for each volunteer's Attendance Time Slots, another field for Work Time Slots, and a third field called Conflict?. There are several (up to five) time slots listed in the Attendance column and in the Work column. I'd like to check to see if ANY one or more of the time slots in the Attendance column matches ANY one or more of the time slots in the corresponding Work column for that volunteer. If so, then I'd like the output in the Conflict? field to be "YES" (otherwise, "no"). In other words, I need the Conflict col

Hi! Wondering if there's any way to make this work...I have a formula field that pulls info from multiple fields & concatenates it all together to create an email (content) to be delivered to the person associated with that record.I want to include a Loom video gif in these emails. (To be clear: This is one shared video link/gif which is going out to everyone. Not unique videos for each person.)Is there any way to do this within Airtable? I'm aware that I could simply paste in the Loom URL. I'm hoping for a way to insert the video's gif file if possible.Thanks in advance!

Hi!I'm new to the Airtable community, I'm still finding my way around. I hope all is well!Sooooo, Im wanting to create a smoother process for sending out invoices to my sub-contractors for a work week (Mon- Sun). I have come across a bump!Let's pretend this work week is Feb/12 - Feb/18 (Mon-Sun)So I have two tables:1. Booking Details: ignore the entry at the bottom, it's not part of the pretend work week2. Payout Details Now, The info from table 1 contains a job record: who worked it, the price, the date, etc. In this case, all those jobs were worked by "test@gmail.com" (one person). These jobs were done in different days but in the same Work week of Feb.12-Feb.18. Table 2 is supposed to put all the jobs together

Bonjour à tous, J'ai un fichier avec une colonne TEXTE LONG et je souhaite remplir une colonne de SELECTION UNIQUE automatiquement en fonction d'un morceau de TEXTPouvez-vous m'aider ?

Hi !     I created a column to identify if the "End Date" of a task belongs to one of the weeks below, based on the ISO numbers for each week of the year (1~52 or 53).      But I am facing an error due to the year, that I cannot overpass.      It is considering the pure number of a week in 2023 (e.g.: #49) as a week in future, because the actual week number in 2024 is less than that.     Please, can someone help me with it?PAST: "End Date" week number < actual week numberTHIS WEEK: "End Date" week number = actual week numberNEXT WEEK: "End Date" week number = actual week number +1FUTURE: End "Date" week number = actual week number +2Here is the actual formula I have that is running, depite the error from year:"IF({End Date},IF(AND(YEAR({End Date

So my challenge may be in my how I have my dates setup. Essentially what I'm trying to do is to utilize a date to create text based on the current month. I think an example could better explain this, e.g., I have a date that is 4/1/2024 and I'd like another column to display the text "Summaries" when it is within that month. So then if it's the following month, 5/1/2024, I want that column to display "Graphs" for that entire month. Is this something that's even possible? Is there a way in the dates to select an entire month? I think that may make the formula more straightforward. Any insights would be amazing. Thank you in advance. 

I'm new to formulas but have learned quite a bit from these forums. I have arrays of Hardiness Zones for plants, which are written like this: 4a, 4b, 5a, 5b, 6a, 6b and so on. I need to put them into ranges like this "Zone 4-8". So far I have it this way:I used this formula to get it:IF(NOT(Zones=''), 'Zone' & ' ' & LEFT(ARRAYJOIN({Number value (from Zone test)}), 2) & '-' & RIGHT(ARRAYJOIN({

Help!  I am trying to create a field that combines first and last name.  I am using the formula below but getting the error message as shown.  Confused what I am doing wrong? 

We had students respond to a number of self-reflection questions, and their answers are in table 1.  Table 2 uses lookup fields to pull in those answers, and then a formula field to attempt to score them.Each question has 5 answer choices, and each answer choice is worth between 1 and 5 points.  Here's an example with the number of points each answer is worth in parentheses:  How would you react to constructive criticism from professors in your host country?a) I might take it personally and feel discouraged. (1)b) I'd feel a bit upset but would try to learn from it. (2)c) I'd take it as it is, without much emotion. (3)d) I'd see it as valuable feedback for improvement. (4)e) I'd welcome it and actively seek out feedback for growth. (5)We're using a version of the same nested IF statement to score each question, though obviously changing the answer text:

Hello Airtable community! Our team has recently transitioned our CRM system to Airtable and configured a dashboard for straightforward reporting to our leadership.In our setup, we have a "Status" column that includes several choices. I've arranged a status view on the dashboard for a quick glance at the figures. However, I'm also looking to calculate a conversion percentage to demonstrate, for instance, the percentage of leads progressing from "interested" to "won" among other transitions.Can anyone guide me on how to establish a view for this purpose?

Hello Everyone,  Basically I have got a table with documents sign date and expiry date, and created a formula when it shows that the document is "Active" or "Expired" if the date is in the past. The question is how to create an automation so whenever formula changes to expired it will send an email telling a person that the document is now expired.  Hope that makes sense.  Thanks

Hi,I'm not sure if this is the right place for this, but here goes. I have a table containing the following 'data'Project, Person, Start, FinishI want to plot on a Vega-Lite Heatmap style Histogram the 'heat' of these people over time. So the more 'Projects' then the higher the 'heat'. I could see that Joe is cold in Jan, but hot in July.This is the style of heat map: https://vega.github.io/vega-lite/examples/rect_binned_heatmap.htmlI guess I need to transform the 'hits' per say, month. But I have no idea how to do that!Thanks in advance.

I'm having trouble with using the DATEADD function (and yes totally new at this). I'm trying to add 3 months to a date in another column. I've technically gotten the formula to work but it's not quite getting the desired date. So for example if my date is 4/1/2024 and the formula is adding 3 months it's calculating 6/30/2024. I need it to actually calculate 7/1/2024. I've tried to add a day to the formula but keep getting an error. Any help would be great. Thanks in advance.

Hey everyone , I am slightly struggling to find the best formula for expiry data. Basically, I have signing date and expiry date and I need to create a formula that will indicate that if the paper is now after expiry date it will change to 'expired' and if it is within time frame then it is "active" And maybe it will be possible somehow to connect it to "Status" in single select feature? Thanks in advance !!