Formulas

I am trying to calculate the number of months between today's date and the "Test Date" field. When I use the DATETIME_DIFF formula it does not calculate it correctly for all values.  In the Test copy column I have this formula:DATETIME_DIFF({Test Date}, TODAY(), "months")It calculates some of the values correctly but then other ones wrong and I am uncertain how else to do this calculation in Airtable. What I need is that if todays date is Nov 7, 2024 then any dates in Nov 2024 would give a value of 0, any dates in October 2024 would show -1 but you can see the dates on October 16 and October 30 show a value of 0. You can see this issue also on the last row of the screen shot where the date of Jan 2, 2024 should be 2 but gives a value of 1.&nbsp

Hi, I' trying to figure out something with the form.I need my salesteam to submit sales info in the form.Lets suppoer we have to submit 2 field:1) sale price2) costI would like that after the user typing the 2 infos above, a field called "profit" will show the result of the equation: "sale price" - "cost"Is this possible?I'm trying to insert not editable fields in the form, but I couldnt figure it..thanks

Hello!I'm using a prefilled form URL field. When a customer submits a button it opens the prefilled form in the corresponding URL field.The problem is that it works perfectly on desktop, but when using App, the prefilled form formula does not work.Is this expected bahaviour?Thanks!

Hi, I want to generate a vcard file from my records automatically. I already connected my Airtable to my app (Adalo) where I receive my users information. If I could generate a vcard file I could export it back with Zapier to my app, but I didn’t find that how could I generate it. Maybe a formula, but I’m not sure about it. Thank you in advance!

I need to get the month to date total number of appointments per client, I am still not getting the result that I need I kept getting the total number since they started. Help please

I have created a form to collect input into my airtable, and have it embedded in a webpage vis iframe. Form loads and works fine, but the input fields are displayed in a narrow column down centre of page, rather than loading edge-to edge in the browser window. On moble, this makes the form look very small/unusabel. Is possible to zoom in but I’d much prefer the form loads so that the columns of input fields is displayed at max size in the available browser window. I’ve tried both with and without header graphic in the form, and have specified 100% width for the iframe. Anyone got some ideas what else to try to force load my Airtable form at max width?

Hello. I'm struggling to find a way to combine the contents of several Multiple Select field into a new field, while retaining the value and color for each entry. Here's a stripped down version of what I'm trying to do: I've got 4 separate multiple select field, each with their own set of options. The field called "Combined Types (Manual)" shows what I want the output to be. How can I aggregate all that data while retaining the color coding for each one? Thanks a lot.  

I have a base with a large table, nearly 100k records, which contains a field that we'll call "Invoice #" which should ideally be unique. Over time records have been added which use the same invoice number, and this happens through form submissions as well, so what I'm looking for is the best way to immediately and automatically flag those records as duplicates.What I'm hoping for is a formula that can do this, populating a value of "Duplicate" or "Unique" depending on what it finds, which I can then use to trigger an automation email alert if a record ever gets updated to show "Duplicate" in that field.Unique IDInvoice #Duplicate? (Formula)112345

BonjourJe reçois un fichier importé automatiquement avec une colonne "Date début" au format "texte sur une seule ligne" de type01/10/2024, c'est à dire jour/mois/annéeJe voudrais dans la colonne suivante extraire le mois et le passer au format "octobre" en toutes lettres.J'ai réussi avec la fonction  DATETIME_FORMAT({Date début}, 'MMMM') à créer un lien mais le mois affiché se base sur le jour et pas sur le mois de la colonne "Date début" : Pourriez-vous m'aider svp ?merci d'avance   

Hi everyone, I am trying to get unique values from a lookup field. I have tried the solution suggested in: Solved: Exclude Duplicate/Repeating Values via Lookup or R... - Airtable Community. by  @kuovonne. Which stated: Create a rollup field that rolls up the multi-select field. Set the formula to values. Save the rollup field.Convert the rollup field to a lookup field. Notice that the lookup will be formatted as plain text instead of the usual pill format.

Hello! I am new to creating formulas in Airtable, and therefore need help creating a total count field based on multiple selections. What I want to have is the top items ordered (maybe top 15 items) and show which employees have ordered that item. I've created an Interface but it currently shows ALL the items and I haven't figured out a way to display only the highest totals of items. Also, I need to have a filter where when the item has the majority of the same words, it combines those two items together as one item (we did a "sale" so some items have a "25% off" in front of the item name).    Please help! 

Hello all, I have a single base to use as my CRM. A tab for Leads and one for Opportunities. They each have their own statuses. Lead status has 8 steps. Opportunity status has 6 steps. What I am trying to achieve A formula counting how many records are at a certain status (i.e Demos) in that month, to output that into an interface. A formula that tracks how many days it takes to go from status 1,2,3 to 4. It may be 1 to 4, or 2 to 4. A formula that counts, in days total time from status 3 (in Leads tab) to status 16 (in Opportunities tab) What i Have so far Following this airtable article, I have the automations to output when a status has been modified and outputs into a column for each step. When attempting to create a formula, I get an error. (

I am using ArrayUnique(values) as in a rollup field and then referencing this in a make.com scenario to output to a Word document. This works, but the output is a bit unfriendly.Products Acer_Vero B7_B227Q,Acer_Vero B7_B277,Acer_Vero B7_B247Y  I have tried changing the formula in the rollup field to something like ARRAYUNIQUE(values)&" - " but it has no effect.Is there a way to format the output to make it a little more readable - the ideal would be separate lines. EDIT: I have found how to add spaces etc usingARRAYJOIN(ARRAYUNIQUE(values), ', ')but is there any chance of adding a newline character (13) ?

For context, monitoring product listings on Amazon.I have 4 tables:Brands, with a lookup field to ASINs, from CatalogCatalog (containing ASINs), with a lookup field to OffersOffers, showing every offer on each ASIN, where each offer has a Seller ID, a lookup from Storefront Profiles.Storefront Profiles: Contains Seller ID.I'm trying to count, at Brand-level, the number of unique sellers associated with their ASINs. I've tried all sorts of counts and lookups, but can't get the right number. Either counting, duplicates, spaces.So far I have:In Catalog: A linked record to Offers, showing all offers per ASIN, with a lookup to show the active sellers.In Brand: A linked record to Catalog, showing all ASINs per brand, with lookups to show the offers and active sellers.Brand - 'Active sellers' - shows unduplicated sellers. Using COUNT(ARRAYUNIQUE

I am trying to write a formula field that will summarize the responses of three separate single select fields (Prototype 1 Cost Status, Prototype 2 Cost Status, and Prototype 3 Costing Status) so my costing team can have an idea of what proportion of the current prototype stage is "Meeting Target" or "Not Meeting Target". I'm finding this difficult because if we move from Prototype 1 to Prototype 2, we will need the response from Prototype 2 to override the response for Prototype 1 (as shown in the attached png) and so on for Prototype 3. This is what I have been able to draft so far: IF(  AND( {P1 Cost Status} = BLANK(),    {P2 Cost Status} = BLANK(),    {P2 Cost Status} = BLANK(), BLANK()IF(  AND( {P1 Cost Status} = "Green - Meeting or Exceeding Target",    {P2 Cost Status} = "Green - Meeting or Exceeding Target",    {P2 Cost Status} = "Gre

Hi,I have a formula that currently works 90% of the time which finds the number of days between a date range for a given month. The issue is, when the end date, ends in the month I am looking for, example below Oct the formula is correct (row 1, 3 and 4 in the image below). However, when the end date includes BUT does not end in the given month (example: row 2 or 5 in the image below) I get 30 days. I want it to show as 31 days because the date range is inclusive of Oct 2024. Any suggestions? Here is the formula,IF(AND({Start Date}, {End Date}),IF(AND({Start Date} <= DATETIME_PARSE("10/31/2024"),{End Date} >= DATETIME_PARSE("10/01/2024")),DATETIME_DIFF(DATETIME_PARSE(MIN(VALUE(DATETIME_FORMAT({End Date}, "X")), VALUE(DATETIME_FORMAT(DATETIME_PARSE("10/31/2024"), "X")

Hello,I'm looking for a way or formula to set the records vertical within a cell as I show you in the attached image. Bests,L

Hello community,I'm building some kind of table where it has repeated content in the rows that I would love to have them combined, similar to a merge cell in Excel or Google Spreadsheets.Is it there a formula or a scripting that can help me have some structure like in the attached image?Thanks!Leandro

Hi I am trying to figure out how to remove the NaN value when the formula is 0/0I need it to reflect 0.  I have tried IF() and IF(And( formula's but I cant seem to get it to work current formula is {Surveys Recieved-MGR}/{# of Managers} Does anyone have any solutions?Thanks !

Hi All,I'm attempting to calculate a simple calculation from fields that already exist in a given table: ({gross_expected_revenue_per_qty_sold} * ({your_split} / 100) * {qty_sold}) - {cogs} - ({printing_costs} * {qty_sold})I thought I was going crazy, had two different AIs look over this, and even after that, it the formula doesn't work. None of the fields from which this calculation is pulling are empty, 0, or negative, and for some reason, this still throws an error when I run it.  By the way, your_split is a switch formula that puts out a percentage like "35%" for example. Thanks.Any idea what could be going wrong, here? Thanks!

Hello,I need your help with comments. Users of my interface can easily enter comments on a records. The problem is that I have other tools linked to Airtable, and I would like to find a way to put the date of the last comment or just the comment count into another field to trigger an automation that launches a webhook. Do you know if this is possible?Thanks in advance.

Hi There -Im having trouble with adding dynamic mentions with my slack message automation in airtable. Currently my airtable is connected to salesforce where it pulls a managers name into the airtable record. I'd like to set up an airtable automations that messages a specific slack channel, that mentions that specific manager. There will be a number of different managers and I dont have an actual list.From my understanding, I need to set up a formula that looks up the managers slack id, but im not sure how. I dont want to have to manually link the slack ID as there may be new users added from time to time. The airtable documentation around dynamic mentions kind of makes sense but im still missing the piece on how to lookup the slack id. It says to use the following CONCATENATE("<@", {Slack member id lookup}, ">")but im not sure if i need to manually find the id and insert it above? or if this exact formula should au

Hi everyone,I have been struggling with comparing a date of when a record has been created to a date I am manually inputing into the formula. So basically I am trying to use the IS_AFTER function but have tried so many things and just everything is either coming up with error or false to all fields even though i know some of them are after the date i am specifying.I am trying to get all records that are true to being created after the 21/10/2024 and feed them into a make.com automation using this field or formula.This is the formula I have been using but all the fields are showing up as 0 (false) when there are definitely some records created after the 21/10/2024. I had to use the date format with the date parse as it was automatically formatting the date in US format even though I have specified the format? IS_AFTER(DATETIME_FORMAT(Created, "DD/MM/YYYY

Hi there,I'm trying to utilize Airtable to manage Shopify Metafields (among other things), but am striking out with doing this in what I believe to be the most logical way.I've configured a field (column) as "multiple select" with preconfigured options (the array). This ensures we're dealing with legitimate values.Syncing to Shopify, through the use of AirPower, requires the metafields to be in the format:["MF1","MF2","MF3"] I've used ARRAYJOIN to convert the array to a string in a separate field, but am stumped at actually formatting the square brackets and quotes.Suggestions?TIA,Jeff

Hey there. I’m using this formula for a column: DATETIME_FORMAT({Date of Donation}, ‘M/D/YYYY’) & " — " & IF({Donor Account}, {Donor Account},{Company account}) It’s pulling some company account names that have commas in the title, generating unwanted quotation marks around the field name.: 10/24/2019 — “Farrar, Straus & Giroux” Can I drop something into the formula that will eliminate the quotes?