Formulas

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) rollup function two cell of

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 continuous string first before using the array formula.  Still no luck.   Any advice is greatly apprceiated.   

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 possibly the percent of records with no authorized budget entered. I know the process would involve another table with lookup fields and rollup fi

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 increases. In the Rental Rates t

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 column to alert us if we've assigned a volunteer to work during a time when that volunteer is signed up to attend another session, so no one is double-booked. Can anyone help me accomplish this?Thanks in advance! And

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 from this work week and combine them into 1 record: who worked them, the names of the jobs... which would go under booking details (name: from table 1, first column), and add the total of jobs completed.Now, for table 2, the reason I put start date and end date (although

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}) = YEAR(TODAY()),DATETIME_DIFF({End Date}, TODAY(), 'days') < 0),"PAST",IF(AND(YEAR({End Date}) = YEAR(TODAY()),VALUE(DATETIME_FORMAT({End Date}, 'W')) = VALUE(DA

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({Number value (from Zone test)}), 2), "")There is another column named Zones, and in the linked table, I had to put a space after 9 so that it would still register as having 2 characters in the field instead of just one.There are two problems with my outcome.Problem 1: Some of them only have a single number in the Number Value field. So that return looks like "Zone 10-10" and I want it just to say "Zone 10". I'm trying to use a nested IF(LEN) function in there but it's not coming

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:IF({non-cog16}="I might take it personally and feel discouraged.",1,IF({non-cog16}="I'd feel a bit upset but would try t

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 !!

Saw a new ARRAYSLICE function pop up in auto-complete today that isn't in the documentation. Anyone know where it came from? I suppose it allows for a less hack-y way to access individual items in a rollup, but I'm not sure what it provides in isolation, since you would need to know the index of the item you're looking for, unless you're looking for the last item in a rollup.

I need a formula that calculates the number of days we have owned a house.Possession date to sold date and in the case there is no sold date I want it to calculate using today. TIA

Hello!I'm trying to create a formula that will create First Name (Goes By Name) Last Name (Business Name).  I've gotten my formula so that I can get all those pieces together, however if either Goes By or Business Name exists and the other doesn't, I'm getting () included still. For exampleKatelyn () Grant (Airtable) or Katelyn (Kate) Grant () How do I get it to not include the () if the field is empty?Here's the formula: IF(OR({Goes By Name}, {Business Name}),{First Name} & " " & "(" & {Goes By Name} & ")" & " " & {Last Name} & " " & "(" & {Business Name} & ")",{First Name} & " " & {Last Name}) 

Hi everyone, first of all I'm really sorry for the noob question, I'm an English teacher who is not good at math and I cannot figure this out after days of trying.I have an excel document where tasks are given scores based on prioritization questions.Most of the questions follow a simple "If answer = YES add a numerical value of 3 to the overall score" format.But I have a few segments that use COUNTIF logic.In one of the sections where there are 5 prioritization questions the COUNTIF logic looks like this(COUNTIF(I7:M7,"Yes")>3,12,IF(COUNTIF(I7:M7,"Yes")>2,9,IF(COUNTIF(I7:M7,"Yes")>1,6,3))So the yellow section of questions follows:If 1 question in the yellow segment is answered "yes"  - add 3 points to the overall scoreIf 2 questions in the yellow segmentare answered "yes" - add 6 points to the overall scoreIf 3 questions in the yellow segment are answered "yes" - add 9 points to the overall scoreIf 4 or 5 questions in the yellow segment are answered "yes" -&nbs

I want to automate an email that goes to staff two weeks before their leave year renews on the anniversary of their start date each year.Is it possible to create a formula which will automatically show the next anniversary in a date format? For example: If my start date is 28/02/2019 and today is 07/02/2024 then the next anniversary date should read 28/02/2024Or if my start date is 28/02/2019 and today is 01/03/2024 then the next anniversary date would read 28/02/2025I'm UK based so using DD-MM-YYYY formatting. I'm struggling to generate anything that works - any help appreciated.