Formulas

How do I delete multiple duplicate data from the sheet?

Hi, I’m trying to calculate if {Table 1 Field 2} (date field) is before {Table 2 Field 2} (rollup field to a date field). The structure is: Table 1. Field 1: Student name Field 2: Date student enrolled into college Field 3: Subject name(s) (linked record to Table 2) Field 4: Date(s) (rollup field through Field 3) Table 2. Field 1: Subject name Field 2: date of class Field 3: Student name Basically, any formula cannot work around the rollup field because 1) it’s an array and (usually) has more than one date as there is more than one linked record… so I get something that looks like this: date, date, date, date. Does anyone know how I might calculate if the Date student enrolled into college comes before Date of class, given that there might be more than one class? Alternatively, creating a bridging table to capture both of these so that there is no use of a rollup that is showing more than one date. Thanks for any help. Tom.

I’m working on a P/L table to find the revenue per job. I’m running into a roadblock trying to figure out how to get the percentage increase or loss per job to show a more accurate %? No matter how I change the percentage set up (1, 1.0, 1.00 etc). See below of my current table - suggestions are appreciated. Here are the formulas I am using: Overhead: Net Income: Gross Profit Percentage :

Alrighty — hopefully I can explain this correctly – So I have a table with all my Load information – the Master Table. I have a side table that has my Sales Reps info —contact, ppw, etc. What I would like to do is pull info from the Master Table - Load Information to my Sales Rep table. if the Sales Rep is Adam in the Master Table, then I want to take the corresponding Revenue Margin in the same row to pulled to the Sales Reps table and then ultimately added together so we can get a grand total of revenue this sales rep brought in. Thanks guys!

I am using airtable to make a rota I have a table of doctors and a separate table for each month of the year A field in the month table links to the doctors table so i can put a doctor’s name in that day i would like to count how many on calls each doctor does more specifically, i would like to count weekdays, weekends and public holidays separately to that end, i created another table that links to the dates on the month table and classes each day as a weekend, weekday or public holiday i know the information is there - if i select a doctor from the doctors table, the linked field to the month table contains a list of dates that doctor is on call but how can i count those dates and even count them based on whether they’re a weekend or not etc? i’ve been trying for a few hours now, messing about with rollups, new tables and copious google/forum searching, but have got nowhere sorry if this is badly worded (for some reason i can’t attach the screenshots i’ve taken - the webform wont let

Thanks in advance for your help! I need to create a formula to determine a project’s classification based on Date Added. There are three classifications: Rocks – 90 days or less, Boulders – 180 days or less, and Mountains – 180 days or more.

I have a column named “Weights” with values like “1.0” or “2.2” that I use for basic item ordering. x.0 = default item x.1 = first optional item x.2 = second optional item eg: 1.0 - Include 2.0 - Include 3.0 - Include 3.1 - Exclude 3.2 - Exclude 4.0 - Include 5.0 - Include I’m using a Rollup field to add up prices, but it’s including all the optional items in the sum, where I only want the default items included. My thought was "Create a formula field that outputs the Total Price field, but only if the far-right character of the value of the Weights column was a zero, if it’s anything other than zero, then set the price to “0” instead… Then create a rollup field on that “Total Prices Without Optional Values” column… Can’t figure out how to get RIGHT() to show me the right-most character of the Weights field so far…

Hi all, Just wondering if anyone has an excel template (or Airtable?) that has some embedded formulas to prefill a form and would be so kind to share it with me? I’m trying to create one myself but I’m only intermediate at excel, and the form I’m using has about 20 prefilled fields (plus the record ID at the top hooked in with a Zap to update the record). Issues I’m struggling with is using the TEXTJOIN and CONCAT formulas, especially given that the fields are sometimes long text with multiple spaces. Happy to collaborate with my current work too. Regards, Tom.

I want to execute my own third-party code when I set the value for a record. For example, when I set the value for my applicant to be “Pending”, I want to trigger a texting API to send that applicant an SMS. Can you guide me on how to do so

Hi All! I’m new and I have a basic question. I’m an investor organizing our deals. I need to do the following: In my 1st table, I have a field called “Priority”. One of the options within the Field is: “No Interest”. When I select “No Interest”, I want that record to automatically populate another table called “Deals Declined”. I’ve looked at the following Help article but unfortunately, I can’t get it to work. Any help would be MUCH appreciated.

I am trying to make a simple formula that would show a total amount available between two fields if a third field had a particular category in it. IF {Type of Credit}=“Credit Card” then({Total Credit}-{Total Amount Due:}) I have not been successful getting the syntax right, but I am a new user to airtable and am probably still thinking like a spreadsheet… Thank you for your help! Colleen

I am having trouble getting this formula to work. I need it to calculate 28 days from column one and insert the date in column two, and if no date exists in column one the second column should be blank, any help would be greatly appreciated guys, thanks.

What service do you use for background checks? Checkr.com does not support airtable

I have two columns when a date is inserted in the first column the second column adds 20 days and populates a date which is fine, however I keep getting an error in the second column when a date in the first column has not been entered, what do I need to add in the formula so if the date is blank in the first column no error will be returned in the second column, thank you guys!

What’s an efficient way to see if a time of day is after another. For example, let’s say I have a client who says, “please call me only after noon.” How should I structure a column (text? datetime?) containing their stated time and a second column (for a logical test) to tell me if it is ok to call at the current time? Thanks.

I want to duplicate a single-line text column into a different table, but have both columns update regardless of which one is edited. I.e. I want Column 1 in Table A to be identical to Column 1 in Table B, and if I make changes to the column in Table A or B, it’s reflected in both A and B. Can someone help me with this?

Hi! I was wondering if there was a quicker way to do this. Airtable Grid view - Airtable Explore the "Grid" view on Airtable. I’m trying to split the categories in Column A into Different Multiple Selection Items for Column B. I will review the similar / suggested topics as well. Thank you!

I have a field linked to another table, and when I use roolup with ARRAYJOIN, it didn’t keep original sequences. For example, the sequence of the linked field is [a, b, c], but the output of the roolup is “c, b, a”.

Hi I made two columns (1 for current and 1 for complete) to count how many projects are linked to each contact I have. When I made the formula with an & it added 2+0=20? When I changed the & to a , it is now calculating the correct amount 2+0=2 Why is this?

How to change the map marker color based on a column value for eg. company type

Ah! ALL of my fields that incorporate the SWITCH() function are suddenly returning “#ERROR!”. Is anyone else having this problem? It happened when I just created a new field using that formula as well…

I wanted to enter emails into an airtable. Each email would be its own record and their would be three fields - open rate (percentage), click rate (percentage), and ‘tags’ (multi-choice). I want to be able to average the click rate/open rates based on the different tags I give the emails. So say I have a tag called ‘topic 1’. I want airtable to create an average of all the open rates or click rates for all the emails with the tag ‘topic 1’ in the multi-choice tag field.

Hello, First post and couldn’t find the answer. I’m trying to create a nested IF. Field A, single select as choice 1, 2, or 3. Field B, is a Date Field C, is a Date Field D, is a Date I’d like to the formula to check the value of Field A. If it’s 1, return date from B, If it’s 2, return date from C If it’s 3, return date from D Thanks!

Hi All, I am loving the new Last Modified Time as I have been waiting for this ever since we started using Airtable but now I have run into an issue. I would like the LMT to see when I add a new project that the lookup field finds in another table but Airtable isn’t allowing me to do this. I read that you can create a formula to get around this but I can’t seem to figure out how to make that work. Thanks for the help.

Hi, I have migrated my data from Google sheet and each record has a unique code (i.e. TO-1234). I want to autogenerate a new number in increments of 1 for each record that is added which shold not a duplicate of any previous code listed in the field. ; in regex the formula is: =MAX( ARRAYFORMULA( value( if(REGEXREPLACE({N3:N9290},“TO-”,"")=“XXXX”, “”, REGEXREPLACE({N3:N9290},“TO-”,"")) ) ) )+1 I can’t figure out how to replicate this in Airtable. Help! Thanks