Formulas

Hello, I have one table that has both insurance and investment products. The Product field splits these out. They share other common fields such as “Payment/Investment_Amount” and “Payment_Frequency” Payment frequency holds single select options of weekly,biweekly, bimonthly, and monthly. I am trying to create a statement specifically to calculate annual premium paid for insurance products. This is a conditional IF statement as the product has to equal insurance, then if it does, premium amount * frequency to equal annual payments. Can someone assist? Much appreciated! Thank you, Ben

Hello, I am using a single select column with options as predefined numbers, 1 to 6 - representing hours (time sessions). I would then like to multiply the selected option with X (day rate) and output the result in a currency column (total cost). I can’t get it to work. Is it possible? I would like to use a single select instead of a number field to input hours.

My records are created from automation, representing tasks that were started. I have a created date field that represents the start date of the task. I also have a single-select field that staff use to mark a task completed, among other statuses. I want to be able to know when the status has been marked completed and show that date as a record field so I can then compare and check if tasks are completed the same day they were started, and if not, how long they took to complete. I’m not even sure this can be done or if there are other ways to get to what I want but I appreciate any advice! Thank you!

Hello, how do i create a formula to select records that have a specific value in a linked field. Note: The linked field allows multiple linked records. My guess is that it is something like: IF field has value in array.

I have 2 switch formulas that work off different fields and they are combined by a 3rd if statement that just says if this one isn’t blank return this one, if it is return the other. is it possible to put them in one field? if so how might i go about parsing it? REGISTRATION STATUS FORMULA: SWITCH({SERVICE STATUS}, ‘INQUIRE’, “NEEDS ATTENTION”, ‘UNKNOWN’, “NEEDS ATTENTION”, ‘OUT OF SERVICE’, “OUT OF SERVICE”, IF(AND(MONTH(TODAY())= MONTH({REG MO}),DATETIME_DIFF({LAST INSP},TODAY(),‘DAYS’)<-150),“DUE FOR INSPECTION”, IF(MONTH(TODAY())!= MONTH({REG MO}), “NOT DUE”,“READY TO RENEW”))) REGISTRATION MOVE FORMULA: SWITCH({REG PIPELINE}, ‘CANT BE INSPECTED’, “CANT BE INSPECTED”, ‘SENT TO TAX OFFICE’, CONCATENATE(“TO TAX OFFICE” & " " & DATETIME_FORMAT({PIPELINE MOVE}, ‘MM/DD/YYYY’)), ‘BACK FROM TAX OFFICE’, CONCATENATE(“RETURNED” & " " & DATETIME_FORMAT({PIPELINE MOVE}, ‘MM/DD/YYYY’)), ‘MAILED TO MANAGER’, CONCATENATE(“MAILED” & " " & DATETIME_FORMAT({PIPELINE MOVE}

Dear fellow airtable wizards, Today my quest has put me up to a challenge I hope you guys can help me with. Short backstory: We offer courses which people can subscribe to (their record enters the base using forms) The first course starts the 3rd of January. Whenever they register we send them an email (using automations). In this email we confirm that the first course is on the 3rd of January. I came up with the following formula: IF(IS_BEFORE({Created time},{Start date}), DATETIME_FORMAT({Start date},‘DD MMMM’)) Now comes the tricky part… If a person registers a day after the 3rd of January, I want my formula to let them know their first course starts a week later (the 10th of January). If they register after the 10th, they start on the 17th… and so on. I’m looking for a formula that knows how far apart the {Created time} and the {Start date} is, so it can add 7 (or 14 or 21…etc.) days to the {Start date}. I know there’s an easy way of doing this by simply making a formula that tells

Hello Everyone, I would like to create a formula that defines a range of dates depending on the due date within next Monday: as follows filter content that due date is coming on till next Mondy only then resets again on Tuesday and shows me what is coming till next Monday. Thank you Guys

I’ve got a table lookup step in Zapier that goes through Airtable and tried to locate a record from a dynamic ID that also resides in the Airtable’s rollup. Here’s the formula I have now in Zapier: Here’s the Rollup in Airtable: I’ve tried removing the double quotes from the formula and have confirmed that I’m searching in the right table. Why are the tests coming up without a match?

Hi! I’m trying to see if there’s a way I can do this. I know Airtable is not a “spreadsheet” and that typically you cannot use different formulas per row/cell because of the database situation. I’m a complete newb to airtable, so I’m still learning the intricacies to see if it’s something I can use in my role. I’m putting together a checklist for a large event that has due dates- start and end dates included in the table. In my google form/excel sheet I have formulas created so that all I have to do is put in the first day of the actual event into a column, and then another column has the # of months, days or weeks out that we need to start working on that particular task for the event. I had to go in each cell to change the formula for the specific task for those that were 1+ months out vs. those that needed to be start 2 weeks out, etc. I’m using this formula in the column (the DATE and Project Start are the columns I’m having it pull data from): DATEADD(Date,-{Project Start (Months/

Hi all, I am importing a data set that stored some numbers as roman numerals. Is there a formula or other method to convert these to numbers?

I use DATETIME_DIFF to calculate hours worked by employees for a given job. I have a Jobs table and a Time Logs table. Employees check-in and check-out to each job in the Time Logs table, then I calculate their hours worked back in the Jobs table with a DATETIME_DIFF formula that references the “Check-In” and “Check-Out” Lookup fields that automatically lookup the corresponding Time Log records. It’s a pretty elegant solution to time tracking and I’ve loved using it for months. Today, I had an employee that I needed to pay for exactly 2 hrs of work, so I manually adjusted their check-out Time Log record to exactly 2 hrs after their check-in time. When I went back to the Job record, I realized by DATETIME_DIFF field was displaying “119” minutes instead of “120” minutes. My first thought was “maybe Airtable is keeping track of a seconds value that I can’t see in the UI”, so I copy/pasted the date/time values into a separate table to test. In that table, the same date/time values and form

In a Base that assigns tasks to collaborators, I would like to show each collaborator what tasks they themselves are assigned to. I’d like to have a view with a filter something like ‘Collaborator assigned’ is equal to ‘Current collaborator’ where ‘Current collaborator’ would be the value of the currently signed in collaborator. If I was doing this inside a script I could use “session.currentUser.name” to get the current collaborator. Is there an equivalent way to get the current user from a formula? The closest solution I’ve been able to think of so far is to just create a separate View for each user and hard code the collaborator (or another unique field) into the filter. I’d have a set of views like this: See Jane’s Tasks See Joe’s Tasks See Jack’s Tasks Is there a way that I could implement just one View, “My Tasks”, that would show just the tasks of the currently logged in collaborator?

Hi everyone, I am looking for the best way to break down Bundles made out of 2 skus (A,B) combinations. The way our theme is set up on Shopify, it requires 1 variant for each bundle combination. The issue is that neither my supplier nor the fulfillment center’s inventory system recognizes these bundles. They can only recognize sku A and Sku B. How can I convert the exported csv with bundles from Shopify into airtable using only sku A and B? CSV file has 1 line per order ID. How can I automatically add a second line matching the same order ID when bundle has both sku A and sku B? Sku A Sku B Bundle 1 = A+B Bundle 2 = A+A Bundle 3 = B+B Bundle 4 = A+A+A Bundle 5 = A+A+B Bundle 6 = B+B+B Bundle 7 = B+B+A I will need to repeat the same step in reverse to push the data back into Shopify once the fulfillment center sends me the orders with the tracking numbers. Any help is greatly appreciate it :slightly_smiling_face:

Porting something from Google Sheets for the relational goodness, but having trouble wrapping my head around this… don’t even have a formula to troubleshoot, sorry. I have a table called Legislators. Each record is a person. I have a table called Legislation. Each record is a bill. Each bill record has a linked field to Legislators, and contains the uniqueid of each Legislator who voted for the bill. Each bill also has a year, say ‘2020’. There are multiple bills per year in this table, and multiple years worth of bills. Now, in the Legislators table, I wish to have a field that indicates how many times this Legislators voted for any bill in 2020. I just want a number. That number is the number of times this Legislator’s uniqueid shows up in the voted for field of every bill in 2020. In Sheets, I use their SQL equivalent search. I started investigating wacky IF statements here, but I just don’t think that’s going to cut it. Thank you.

I am trying to calculate the sum of man hours for two teams that have different production hours and different number of staff members. This works for one team member of {Staff (Team 1)} but I can’t seem to get it to multiply by {Staff (Team 1)}, let alone sum {Staff (Team 2} with same formula. DATETIME_DIFF({End (Team 1)},{Start (Team 1)},‘minutes’) / 60-{Breaks in H (Team 1)} My attempt to achieve this that obviously don’t work lol: DATETIME_DIFF({End (Team 1)},{Start (Team 1)}*{Staff (Team 1)},‘minutes’) + DATETIME_DIFF({{End (Team 2)}, {Start (Team 2)})/ 60-{Breaks in H (Team 1)}-{Breaks in H (Team 2)}*sum({Staff (Team 1){{Staff (Team 2)}) Thanks for your help in advance

Hello, We’re needing some help with a formula (or proper linking) to auto-price our services based on the service area - in this case, by Zip Code. The idea is Service Type + ZipCode = Price. For example… Currently, we have a form that takes customer info (Service Submissions). The Client/Customer selects the service and specifically enters the ZipCode in a required field. Through integromat, a record in Service Orders is created. This is the table our team works in. We’d like the price from ZipCode Pricing table above to be calculated and show up in the Service Orders and Invoices tables. Ultimately, the pricing data will be sent to Quickbooks, but that’s a later step. Any help would be greatly appreciated. If there’s any way I can explain this better, please let me know. Thank you in advance!

Hello! I have an extensive table where we log all data and statistics for our business’s Google Ads, social media advertisements, etc. My boss wants a daily recap of the standings for all of the ads in the table. But it would be too time-consuming to draft an email every day containing all of that info. Essentially, what I want to do is set up an email that automatically sends to him every morning at 8 am that includes all of the information in the table. Is this possible? I have a couple of basic automations running, but I can’t figure out how to make it happen daily no matter what, or how to include the entire table. Please keep in mind, I am a beginner when it comes to formulas and automation, so some hand-holding would be appreciated. Thanks in advance! Connor

I would like to do a lookup based on what is selected in a Single Select field. For example: IF single select field is A, Lookup Record X IF single select field is B, Lookup Record Y How can this be set up to avoid errors?

Good Evening, I must definitely use the “®” & “™” symbols in the text description of my products. I guess the property of my field should be RTF file. How should I do it? I also have a attached photo with my name (for example) that how I want to be done [I made it in Microsoft Word]. Thanks.

Hello! I need to compare two lookup columns with email-addresses. If column 2 contains an email from column 1, the formula should return true, otherwise false. Like this: Email from table1 | Email from table2 | Answer email1, email4 | email1, email2, email3, email4 | true email5, email4 | email1, email2, email3, email4 | true email5, email6 | email1, email2, email3, email4 | false

Hello! This is my first time on the forum and I’m excited to leverage everyone’s knowledge. Our product is in dire need of being able to list the “Top 10” of certain group values, essentially leveraging the Autonumber feature in different views. The best way to explain my question is with an example right!! In this image, I see that Airtable auto numbers the names(Pink), but I cannot figure out how to get that number in an actual field (RED), as this is filtered and sorted by the performance (Orange Arrows). Any and all help on this would fully allow us to move our entire product onto Airtable and to finally get us off Sheets. Thank you!

This feels really basic, but I don’t know what I am missing and new at the formula editing. The issue I am having. I am working off one date (Purchase Contract date) and trying to add a number of days (Home Inspection Contingency) to the contract date, to find (Home Inspection Due) due date. It is working for all number of day options eg: 5 days, 7, 14 etc, except when NONE is selected or when the selection is left blank. In either of these cases it still populates the date as a purchase contract date. Ideally in Home Inspection Due it would populate NONE. Is this possible? My formula is: DATEADD({Purchase Contract Date}, {Home Inspection Contingency},‘days’) below. #1 and #2 are wrong, #3 is correct

I found the following formula I am using to format currency. I want to round up or down and get rid of the decimals . Any ideas on how to modify the formula to do that? Thanks! IF( LEFT({NumberValue}&’’,1,1)=’-’, ‘-’, ‘’)&’$’& IF( ABS(VALUE({NumberValue}&’’))>=1000000, INT(ABS(VALUE({NumberValue}&’’))/1000000)&’,’& IF( INT(MOD(VALUE({NumberValue}&’’),1000000)/1000)<100, REPT(‘0’,3-LEN(INT(MOD(VALUE({NumberValue}&’’),1000000)/1000)&’’)), ‘’)&INT(MOD(VALUE({NumberValue}&’’),1000000)/1000)&’,’& IF( INT(MOD(VALUE({NumberValue}&’’),1000))<100, REPT(‘0’,3-LEN(INT(MOD(VALUE({NumberValue}&’’),1000))&’’)), ‘’)&INT(MOD(VALUE({NumberValue}&’’),1000)), IF( ABS(VALUE({NumberValue}&’’))>=1000, INT(ABS(VALUE({NumberValue}&’’))/1000)&’,’& IF( INT(MOD(VALUE({NumberValue}&’’),1000))<100, REPT(‘0’,3-LEN(INT(MOD(VALUE({NumberValue}&’’),1000))&’’)), ‘’)&INT(MOD(VALUE({NumberValue}&a

I currently have a formula that calculates the cost of a child attending a class over a number of weeks. Extrachildren*({Weeks FM}+{WeeksSR})*5 This works well until I was told if they book for 11 weeks then the 11th week is free (they never book for more than 11). So I need the formula to multiply by 10 if 11 weeks are booked. Can somebody please tell me how that should be expressed in the formula. Thanks in advance Phil

Sorry in advance - I am a very green user to Airtable and Databases in general. I would like to make a formula that can scan a record for a word and then automatically make the value 0. Example: Line Item ------------Unit ----------- Rate---------Total Cost Invoice - 0011-------1.00------------$100----------$100 Bid - 0100------------2.00------------$500----------$0 In this case I am using: Rate*Unit to reach the total cost. This will mean that the “Line Item” Bid-0100 will pop up with a total cost of $500, but I need it to show 0 as it hasn’t actually cost me anything. Is this possible?