Formulas

Hi,I have a currency column that is being auto-calculated, and the way Airtable is summing the numbers is sometimes incorrect - off by a few cents.I suspect this is because the auto-calucations sometimes come from .00 or .000 fractional ownership of certain goods, so when Airtable sums a bunch of those at once it's somehow dropping or carrying over fractions of cents...If I copy and paste the whole list of numbers in Excel, for instance, it gives me the correct total. So I'm searching for a way to somehow convert the auto-calculated field to a plain number field which will hopefully only give me the sum of the numbers as they are.Any suggestions for how to to this? Thanks!

I have two tables: "Billing" and "Activities". I'm trying to find a way to combine this data so that I can see $ received from bills while only including "TimeEntry's". In this specific scenario, the amount I would need to see is $530, since the $100 is an ExpenseEntry.It is way too time consuming to manually count the hundreds of records, so I need a formula or an easier way to compute this. Thank you very much!

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 format with the time 2024-02-01T00:00:00.000Z. I can clean this up using some variation of DATETI

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( {Date Type} = "Install", IF( {End} != 0

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) 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.