Formulas

Hi, I want to how can i get remaining salary when employee I absent few days and from total salary the days he is absent should be excluded and formula gives me answer. Below are 3 tabs from which I need formula to be completed. Please help me.! @Gareth_Pronovost , W_Vann_Hall anyone please

Hello, I’m trying to subtract a specific time (3:30 hours) from a duration field using formula that looks like this: Difference field = IF({Ride duration}=3:30,0,({Ride duration}-3:30)) Basically, I want to subtract 3:30 hours from the duration field to know the difference between the times What am I missing here? Thanks in advance, Liat :slightly_smiling_face:

I need to write a formula that tracks our uniform distribution and cost. Here’s what it needs to say: We have 6 types of shirts: Newbie Shirt ($18) Gorilla Gang Shirt $26 Macaw Shirt $26 Rooster Shirt $26 Champion Shirt $26 Executive Shirt $26 Hat: $17 I need a formula that tells me based on the type of shirt they have * the qty based on the cost of the shirt what the total comes to. Let me know if you are able to write this and if you need any additonal information.

I know that Airtable has recently changed a few things and as someone who struggles to remember the technicalities of all of this, I just want to make sure that I am not doing any more work than I need to. Or maybe the update has made it simpler as it has for other things! What I want to do is populate fields from another table into a single cell, one on each line. This has multiple purposes, though right now I am trying to display to-do ID’s one at a time so my brain can actually grasp it, and then make a makeshift-dashboard displaying multiple categories in one place. Another purpose this has is when I do documentation I want to list goals we addressed and have it listed on a different line to, again, make it easy to read. I currently have a link to the table, an “array join” rollup, and then a formula to substitute the commas for new lines. This has the negative effect of creating a list, but having the first be one space off of the rest of the lines, which looks BAAAAD. Is there no

Hi In Excel I can put formula in one field which will calculate Sum(all fields in the column)/Sum(all fields in another column)*100= percentage How can I do it in Airtable for current and future records? I need just one value to gauge progress.

Hi :slightly_smiling_face: I want to create formula that will return true if today is after date or s within two dates. IF(AND(NOW()>{Check-in},NOW()<{Check-out}),“NOW”,"") Above is the formula that does not work for me, it always returns false. I would appreciate some help. Thanks

I consider myself a fairly adept user of Airtable, but I think this question is going to say otherwise. My objective is to see this (the red dollar sign represents my Job Cost field): So on a calendar view, I want to see a concatenation of the Company Name, Contact Name and the Job Cost. The Company and Contact are two separate linked tables, and work fine in the primary field using the following code: {Link to Companies}&" | "&{Link to Contacts} But my Job Cost field is a Lookup from another tab called Proposals. My modified code of: works fine in any field/column other than the primary field/column: When I add the same code to the Primary Field, I get this error: Which makes no sense to me (novice apparently). Is there a way to do what I am attempting to do? Here is a link to the base for your amusement and hopefully help:

I’m trying to put together a quick gardening database app that allows me to pull together information about a particular plant, including what companion plants it grows well with and what should be avoided. I have a list of “companions” and “antagonists” for each record, but I can’t see a way to make those relationships reciprocal. For instance, if carrots have a companion of tomatoes, then it follows that tomatoes should automatically inherit the companion of carrots. It seems like this is going to require a script, but I don’t know enough about scripting in Airtable to start from scratch, and I can’t find examples of people doing exactly this. If anyone knows of any other ways to handle this or a script I could use as a base, I’d be much obliged. Thanks, Ryan

Hi all, This may be a quick fix that I’m missing but figured I’d ask the community for guidance here: I have a base with 8 tables. On 1 master table, I have a primary field (auto-numbered) and 7 additional fields pulling in information from linked records across the other tables. Each record on this master table is only linked to 2-3 other tables, so the majority of the Linked column fields are blank - making it a pain to see all of the linked information quickly when expanding the record (I have to scroll passed a bunch of empty fields). I’m wondering if there is an easy way (using either a formula or rollup field) to create an additional column on this master table that searches the other 7 fields and if there is a linked record, pulls it in to this field. In a perfect world, the information being pulled in wouldn’t just be the text of the linked record but rather the actual link itself - essentially just combining all linked items across the different fields on the record into this

I have rate fields low, mid, high. The rate fields are lookups - formatted as decimals. Then I have a formula field IF(rate = “low”, {low rate}, IF(rate = “mid”, {mid rate}, IF(rate=“high”, {high rate},""))) which results in the correct number, but the formula will not allow me to format as a number. What am I overlooking? Thanks!

With the new conditions formula I am excited! However, now that I have split up my columns to have only the selections the previous question asked, I need to get them all combined into one column again to upload. For example I do kid’s clothing so they choose infant, toddler, little kids, and big kids category then they only have the options for that size in that selection. However I now need to combine the selections in one column to upload to a shopping cart system. They are NOT all numbers. Some might have 2T… Is there a formula for this? Thanks in advance for posting your question! When someone provides an answer, remember to mark their reply as the :white_check_mark: solution. Please delete this text before writing your topic.

I am trying to understand a why a simple MOD function isn’t returning the correct answer. Airtable Airtable: Organize anything you can imagine Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free. ( I can grant you access so you can see the formulas) On the table titled “SEASON STATS OVERVIEW”, in the OVER field, I am trying to build a larger formula and part of it requires me to use the MOD function. The formula in the OVERS field is MOD({BALLS BOWLED},6) but for some reason the record for player 2 is returning a value of 6 when it should be 0. I have tested the MOD function for the number 30 as a number i.e. not generated from a roll up function and that works. But for some reason when the number 30 is generated from a Roll up formula is returns an incorrect value. I can grant creator access to you if you need to see the formula fields/ Any ideas/assistance, all gratefull

Hi All, I have question regarding creating a 3rd column by comparing following two columns; I want to have a 3rd column which shows what is the common item for the both first two columns. So if we take above image as the example, in the new column it should only show “Knitting” on 3rd row and leaving other rows empty. In this scenario there is only one value (comma separated) on first column, but in can have multiple values in some cases. However it will only have one common phrase for a given row. Your help will be much appreciated :grinning_face_with_big_eyes: Thank you in advance :pray: -Eranga Perera

I’m very new to using Airtable. I’ve got a table that includes survey respondents each listed as primary records and then a multi-select field that has various themes they each indicated in their responses. After I analyzed and put everything in the multi-select, I realized I should have had these themes as linked fields on a separate table, so now I’m trying to import the respondents on Table 1 by adding the themes as primary records on table 2 and I can tally the respondents who selected each by using the Count field. I’m hoping I don’t have to add by hand the linked record themes from Table 2 for all 200+ records. Is there a way to create a formula that pulls all respondents on Table 1 who indicated each of the themes in the multi-select to auto populate the records in the matching theme record on Table 2? (I did find that the Pivot Table block tallied each of the themes when I used that, but I’m having trouble customizing the view on the order the themes appear and the column width

I want to allocate a list of numbers (product limited editions) to users. They will pick one by one their selected number and I want this number not to be avaliable anymore after the selection. Do you know how to do that ? Thanks a lot

Hi Everyone, I am new to Airtable and enjoy it (for the most part:). I am trying to concatenate two fields (done) and then replace or substitute the spaces " " with dashes “-”. I having trouble placing the substitute syntax and getting a circular reference error. Thanks in advance for your help!

Hello, I’m dealing with DATETIME_DIFF. It worked until today, but now it return “NaN”, what’s happen?

Hello, I have 2 fields, Collaborator and Formula. When I select a collaborator(any) I want the formula to show last modified time. Similar to this(this is for a checkbox, so it doesn’t work): IF({PAGO} = 1, LAST_MODIFIED_TIME({PAGO})) I have been messing arround with the formula but to no sucess. Thank you in advance.

Hi everyone! New to Airtable but already love the product! I’m hoping you can help. To determine which fish can go with another fish in a fish tank I created a table with two columns. Column A is the species of fish and Column B is a self-linked field of the fish that can go with it (simpatry fish). I want to make sure I don’t overuse any particular simpatry fish. I want to create Column C to count how many times my column A fish is used in any of the values in all of Column B. So row 5 (Heros severus) in my screenshot would have a value of 2 in Column C since it has simpatry with the fish in row 3 and row 4. That way I can find out how many times any fish in column A has been used as a simpatry fish. I hope this makes sense. Thanks in advance!

Hi, im trying to insert a space into a concatenate formula in the primary field of my table. ive seen some similar posts, but the answers dont seem to be working for me. my formula is: CONCATENATE({First Name}, {" "}, {Middle Name}, {Last Name}) {" "} is where i want the space. Any help is appreciated!

Hello all, I have imported data from gmail and it came under this date format in text. "Mon, 11 May 2020 14:31:44 -0500" and I need it in date format like this “5/11/2020”.. Can you suggest a solution for me ? Thanks

Hello - I am very new to Airtable (meaning…this morning :)) and I’m very much a newbie to any kind of spreadsheet. I’m trying to create a formula and am hoping I can get some guidance here . I’ve attached a screenshot to help illustrate what I’m trying to achieve - if the column Pay(X) is checked, then the amount in column Balance Due will be put into column Amount to Pay. Finally, the Amount to Pay should be totaled and put into column Bills to Pay Total. Thanks in advance for any assistance that can be provided! Sarah

Hi, Here is my problem: I have a table of SONGS. In addition to the song title, there are the Composers, Lyricists and Publishers fields. Each of these three fields are populated through a link to my Contacts Table. Of course, there can have more than one composers, lyricists and publishers for any given song; sometimes only one person taking all these roles. Now, the idea is to assign to each of these shareholders (this is how to call them) their specific percentage (the total, of course, amounting to 100%). These numbers may vary from one record to the other, even when we have the exact same shareholders. So, is there a way to assign a specific percentage to each of the multiple shareholders for each record? In other words, I’d like to get something like: Composer 1: 12,5% Composer 2: 12,5% Lyricist: 25% Publisher 1: 25% Publisher 2: 25% For example… :slightly_smiling_face: Thanks!

Hi All, I am hoping to find a way to select an option from a dropdown box in TABLE A which triggers the creation of a new record in TABLE B. It doesn’t have to be a dropdown, could be a checkbox. The ultimate goal is to get a new record to show up in TABLE B to trigger Zapier to send an email with the relevant data to my customer from TABLE B. I.e. IF ‘send email’ is selected in TABLE A, THEN create new record in TABLE B. > send email when new record created. I guess TABLE B would end up being like a sent items folder, but I don’t care if I have to clean it out every now and then. Any help is appreciated!

Hi all just trying to decide if AirTable will work for our businesses. So far so good except for this snag. We are in childcare and NEED to calculate our babies ages in Months not years based on TODAY(). This is the section of the child table so far, I want the Age field to return ages under 3yrs (<=35months) in months and the rest in years… Formulas so far M_age DATETIME_DIFF(TODAY(),{C1_DOB},‘M’) &“M” (works fine) Y_age DATETIME_DIFF(TODAY(),{C1_DOB},‘Y’) &“Y” (works fine) Age I’ve tried… to get under 3 years in Months and over in years: IF(M_age <= 35,(DATETIME_DIFF(TODAY(),{C1_DOB},‘M’)&“M”),Y_age) IF(M_age <= 35,M_age,Y_age) No matter what I do the age field returns age in years.