Formulas

Hello, would anyone know a formula to add a date with a time and bring the result of date and time updated? example: Start date column: 6/7/2021 1:45 am Duration column: 0:45 Result column: 7/6/2021 2:30 pm. Thanks in advance ! :blush:

Hi, I’m wondering how to count all the unique values in a field/column? In my simplified example below, I’ve filtered for orders shipped in the spring, and I want to know how many unique fruits are in the linked field, “Content (from Fruit table),” so that I know how many unique types of fruit I shipped all spring. Thanks for any help you can give.

I have Table 01: Product Columm Price option : $1 , $2 , $3 Table 02 : Customer Column Want to buy at : $1, $2, $3 I want to match these condition together and display its at another sheet and i can track back which customer is which. Example : Display sheet will be display: Product A price $1 have : Mr.A , Mr.B, Mr.C, Mr.D want to buy Product B price $2 have : Mr.A, Mr.B want to buy

I am using a WORKDAY formula to calculate the end date of an event based on the duration. The formula I’m using is: WORKDAY({*Start date},({*Days}-1) However, this does not return a date field that can be clicked and dragged in Calendar and Gantt views. Is there a way that I can make that happen? I don’t mind having another field that converts that returned value to the correct format.

I have a table called Things and another called Options The Options table has a list of attribute columns and each has an associated Cost Modifier Name Type (Color, Size, Shape, Special) Cost Modifier The Things table has Lookup values that point to views of certain Types of Options Color Option (Single Select) Size Option (Single Select) Shape Option (Single Select) Special Options (Multi Select) What I want to do is to aggregate the unique options from (Color, Size, Special) and use the resulting value to create a Rollup Sum of all Costs. Example: Options (Name | Type | Cost) Red | Color | 3 Black | Color | 1 Large | Size | 2 X-Large | Size | 3 Hoodie | Shape | 5 V-neck | Shape | 3 T-Shirt | Shape | 2 Distressed | Special | 2 Glow in the Dark |

Hi everyone, I’m adding a button to my table, it’s a URL that includes a dynamic field agency_cardId from that table. It’s not accepting the syntax, any thoughts, please? “https://d1s1fe8elvctzs.xxxxx.net/?cardId= agency_cardId &source=agency&token=123456789.123456789.123456789M#/”

I have a link that may change regularly. I have put it in a tab with a single cell, and want it to propagate across all my tabs using a lookup, without having to explicitly have it look up ever single time. Is there a way to pull a cell from another tab into a formula for a column? (Basically, it’s a meeting ID that needs to be on every row in each tab - pulled from a single source)

Hi all, Here is a screen of what I want to do automatically: For an example, I have a application with the name “gazelle-evsclient”. This application can be have multiple version (ex: 5.13.1, 5.13.0, …). I want to create a column (ex: Need to be upgrade) based maybe on a formula who say: “If the actual version is not the latest, so I display the latest version for this application”. It is possible to do that ? Thx for your help ! :slightly_smiling_face:

ColA has 3 different transaction numbers. The first always starts with TEX followed by numbers. The second is always just numbers. The third is always blank. I need a formula that will look at ColA If ColA has TEX, in ColB put Text Me If ColA does not have TEX, in ColB put Call Me If ColA is blank, in ColB put Do Not Contact

Hi everyone! I have a table with several courses, and event dates for each course. I’m trying to assign the week of the course each event falls in. So, if Course A starts 11/1/20200 and ends 12/31/2020, all events between 11/1 and 11/7 would be assigned Week 1; all events between 11/8 and 11/14 would be assigned Week 2 (and so on) until 12/31/2020. Is there something in Airtable that can do that? Currently I’m writing a formula (If course = X and date is after 11/1/2020 and before 11/8/2020, then Week 1; if course = X and date is after 11/8/2020 and before 11/15/2020, then Week 2…etc, etc) Any help on how to make this simpler would be appreciated! Thanks! Jeffrey

Hello! I’m new on airtable and would love some help creating an IF formula. Context: I’m trying to create a unique vendor code for SKUs. Ive added an autogenerate number field called “Vendor Number” and added a “vendor code” field where I would like to create the IF formula. I want to to add “00” at the beginning of the vendor number if the vendor number is less than 10, add “0” if the vendor number is less than 100 and show only the “vendor number” if it is greater than 100. Example: Vendor Number: 7 Vendor Code: 007 Vendor Number: 19 Vendor Code: 019 Vendor Number: 122 Vendor Code: 122 Any help would be greatly appreciated! Thanks

Hello, I am trying to identify clashes when scheduling shows in Airtable. I am dealing with performance groups some of which share the same members I am working with 4 tables one containing all of the ‘ensembles’ a second with all the individual members of each ensemble (Contacts) which are linked to all their ensembles. Thirdly a schedule which links each ensemble to a venue, start time and finish time. I would like to find a formula which will flag a show as a clash if contacts are linked to multiple ensembles with the same start time? Any help please, I am banging my head at the table…

Hi! I have a followup question to Format Instagram URL filed and I apologize for the beginner question, but I’ve been fighting with this! I am inputting Instagram links like this one: https://www.instagram.com/mhfirstoak/ Once I’ve removed the https://www.instagram.com/, I’d like to add @ and then link the whole thing back to the original URL https://www.instagram.com/mhfirstoak/ so it looks like @mhfirstoak Any help appreciated!

I have a question about how to build rollup formula. My database is tracks members for my organization and which programming events they attend in each program year. I have a member table linked to an events table. I want to build a rollup formula that will count how many unique members attend each event. That is to say, I want to know how many people who have attended each event are people who have not yet attended any other events in the program year. I have built a column in the members sheet that pulls out the earliest event date that members have attended. In the events sheet for each event (each entry row), I want the rollup to count how many records in the continuum file have the earliest date that matches the event date in that row. How can I do this?

I feel like I’m close with this one, but it’s not quite working. I want to show quarters of the year, so if a column equals “January 2021”,“February 2021”, “March 2021”, I want it to show “January - April 2021”, and so on for each quarter. I’ve attempted it with IF(OR), but it’s not working. Appreciate any tips here. IF(OR({Result Month/Year}="January 2021","February 2021", "March 2021"),"January-April 2021",IF(OR({Result Month/Year}="April 2021","May 2021", "June 2021"),"April-June 2021"))

Hi guys, I’m trying to create a unique alphanumeric code(length 4) out of Record ID and Autonumber. But I am getting an error while doing so. Is there any way to find it? Thanks, Chintan

Hello, I’m exporting data from Airtable into PDFFiller and prices are coming through without decimal places if they are 0. eg $165.00 is coming through as 165. I’d like them to have the decimal places on the invoices. PDFFiller doesn’t seem to have any way to force the formatting to be currency with 2 decimal places. I thought this was an easy fix using CONCATENATE() (or just &) and add a $ at the start and a .00 at the end. Then I came across prices that already had decimal places so to work out if it was an integer I used ROUNDUP()=ROUNDDOWN() and if that came out true then it was an integer. I was using rounding rather than FIND(’.’) as it wasn’t finding it when formatted as a currency. Then there are the prices that have one decimal place (eg $165.50) which would still come through as $165.5. I then used a whole combination of CONCATENATEs to get it just as plain text LEN - FIND to see how far the decimal was from the end of the price IFs to

Someone else asked about adding carriage returns to formulas (it’s done like this “\n”). I’m trying to find a similar command for tab or indentation characters. That way I can generate text formatted like this: Product: Orange Quantity of Product: 5 Instead of this: Product: Orange Quantity of Product: 5 @W_Vann_Hall I bet you’ll know

Hello everyone! I’ve got several cols (days of the week) that have a time that I wasted in doing a task in minutes (ex: 120). All that I’m trying to do is a SUM of this minutes but in a h:mm format. Example: The task is “Book Design” and the minutes I’ve spent in doing that task are (120, 120, 60, 60, 30). The SUM of that minutes is 390… that’s 6:30 hours. All I want to have is this number 6 and I don’t know how to get it. Thanks in advance!

Hey there! Let me give you some context: I am creating a packing note for a shoe manufacturer. Here’s an example: Say we have four product batches, three of which are identical, one of which is different… I’m using the ARRAYUNIQUE rollup formula to condense the three identical product batches into one, which is fine, but I also want to add up the total pairs by size of that unique batch, rather than, say, add up all the size 8s in the total shipment. Here’s a screenshot… You can see i’ve managed to use ARRAYUNIQUE to only show the product reference and description once, but if I do the same with the size 8 column, for example, it’ll add them all up, won’t it i.e. 1 + 1 + 1 + 2 = 5, whereas I need it to be 3 + 2 I’m guessing I need a way of making the size 8 of one style unique to the size 8 of the other style??! Thanks for your help in advance!

Hi! :wave: I am having some difficulty with creating a formula in my database. I have one table - ‘Feedback’ - where each row is a bit of customer feedback, and each bit of feedback has a column for ‘Insight’ (i.e. what type of problem it is) and another column for ‘CSAT score’ (ranging from 1-5). I have another table - ‘Insights’ - which lists all the insights from the feedback table in the first column (these are linked). What I want to do is calculate the average CSAT score for each insight. I have managed to create a new column in the insights table entitled ‘CSAT Score (from Feedback)’ which looks up the CSAT scores from the other table and presents them for each insight, separated by a comma. What I then want to do is create another column which averages these scores. However, when I used the formula average({CSAT Score (from Feedback)}, I get the error message shown below.

Hi! I’m new on using airtable. I’m trying to count specific text values in a row (1 Used) and (1 Credit). In excel, I would just use countif, but I can’t figure out how to do it on airtable, can anyone help me? Thank you! Pammy

For anyone that has been trying to get a formula field to ACTUALLY substitute line feeds, carriage returns, and paragraphs here is a solution I (slowly) figured out. Use the following formula to URL encode the text, and then substitute the other stuff you do want back in. Despite all of the suggestions I saw in here, you can’t substitute the \n with anything because airtable just adds it back in. SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ENCODE_URL_COMPONENT({Your Field}),"%20"," “),”%3F","?"),"%0D"," “),”%0A"," “),”%3A",":"),"%2F","/"),"%21","!"),"%2C",","),"%3B",";"),"%27","’") If there are still some encoded characters showing up, just go here to grab it and wrap it again with another substitution. If anyone has a better solution, lemme know. DON’T SAY I NEVER DI

Hey guys, so im working on an invoicing system. I have three columns: Item Price, custom price, and applied price. I’m in need of a formula for the applied price column that says "default to item price, but if custom price is > 0 use that custom instead. any takers??

Is there any 3rd party formula editor that would show the parts of Airtable formula in different font? e.g. IF formula IF - would be one color formula conditions - would be another color field names - field names in the formulas would be different color “text” - text between “” would be in different color brackets - bracket pairs would be in different color and when I touch the bracket, the paired bracket would glow. Formula editor should give me basic error correction e.g. show missing brackets. If miniextensions had a better formula editor for Airtable with error checking, I would be willing to shell out the $39 per month for it.