Formulas

I am trying to create and if and statement for one field being empty and the other being filled. My fields Problem Category Closed Date Closed date always needs to be filled for me to provide a return to my field Charge Type. If the problem category is blank then I need to return “Marketable”. If the problem category is filled then I need to return “Problem”. I have tried multiple If And statements always getting an error or undesirable result. The latest one I have gotten is as follows If (And( Problem Category = BLANK(), Closed Date != BLANK()), “Marketable”, “Problem”)

Hi all, I have a sheet of stock data that was too large for excel, so I decided to move it all over to Airtable. But I’m having trouble with one of the formulas I was using. In excel I’m using the FILTER formula that looks across the column headers (dates) to return the price that relates to a date I enter in another cell. For example, you can see in the image below, I entered the date of 10/11/12 in B2, and with my filter formula in K2 =FILTER(L2:O2,L1:O1=B2) it returns the price that is in N2 (as N1 matches the date I entered in B2). I see in Airtable there is a formula field type, but I don’t know how to replicate what I’m trying to do in excel… Any help would be much appreciated!

I have a payroll base that my workers fill out a form and then they submit a role (Main photo, Main video, 2nd shooter) I have a set rate for each role in each wedding that is literally different for each one. How can I structure a IF statement to dynamically pull the right amount to calculate how much they are owed from the job?

Hi, going off of my previous topic about gardening in “How can I use a formula for the below data?” I am wondering if there is a way to mark a row to show that I missed the target date for the plantings. Say like my target date is 11/20/22 but I didn’t get around to it till the 23rd. Is there a way to make it automatically color or trigger that row saying that I went over that date? Thanks.

Hi, so I am trying to make a formula that fits my gardening schedule for the upcoming spring season. I have the pro plan if that makes a difference in what I am limited to do. Here’s an live example of the problem I am having so far. I will plant my cilantro seeds on 3/22/23. It takes 4 weeks till harvest. So I have 4/12/2023 as the ending date. I will need two other harvest date’s after that. The problem is that I need formulas for each plant which is a total of 40. They all have different dates and different weeks till harvest. Would I need different formulas for an auto-fill each time I put seeds into the ground or am I able to create conditions for it to all work? Thanks.

Heyy everyone, I wonder if you can help, I am not the best at Airtable so rely mostly on learning as I go and your threads. I currently have several columns which count the time(days) between Date 1 and Date 2 but now I am looking to make it exclude a list of public holidays, currently, it just measures working days. So this is the formula currently - IF(OR({DATE 1} = BLANK(),{Date 1}<{Date 2}), BLANK(), MAX(WORKDAY_DIFF({Date 2},{Confirmed Date 1})-1,0)) But I can not seem to figure out how to make this formula not count the below, I’d have to implement this into several columns with similar formulas ! 01/01/2021 02/04/2021 05/05/2021 31/05/2021 30/08/2021 27/12/2021 28/12/2021 03/01/2022 15/04/2022 18/04/2022 02/05/2022 02/06/2022 03/06/2022 29/08/2022 19/09/2022 26/12/2022 27/12/2022 02/01/2023 07/04/2023 10/04/2023 01/05/2023 29/05/2023 28/08/2023 25/12/2023 26/12/2023 Any help is welcomed :slightly_smiling_face: Thanks

Hello everyone. I’m love to use Airtable everymonth to publish invoice and user’s portal. Well, I found a problem with “rounddown” calculate. When I input the date like following, the result was not correct. Price field = 147.70 Quantity field = 50 Subtotal field[ rounddown(Price * Quantity,0) ] = 7384.00 I checked with Excel spreadsheet, the result was 7385.00 !!! And I also checked with calc app, the result was 7385 also. I can’t understand why it doesn’t work the same. And I’d like to fix it ASAP. Please help me…

Hi We would really appreciate some assistance with one of our formula fields. Background - also see screenshot: In our first column, we have a date field called “Lifetime Last Payment”, which we manually complete. In our second column, we have a formula field called “Lifetime Next Payment” with the following formula: IF({Lifetime Last Payment}=BLANK(),BLANK(), DATEADD({Lifetime Last Payment},1,“months”)) Which automatically calculates by taking the date from “Lifetime Last Payment” and adding one month. Our third column has a formula field called ” Lifetime Payment Status” with the following formula: IF({Lifetime Next Payment}<=TODAY(),“ :triangular_flag_on_post: Change Last Payment date”, BLANK()) In our fourth column, we have another formula field called “Final Lifetime Instalment Date”, which calculates the final date of payment for each individual line based on the instalment criteria. Requirement: We need help adding/updating our third column formula: IF({Lifetime Next Payment

Good morning, I want to know the current record to use with a button script, so I use “let record = await input.recordAsync(‘’, tableRC);” It works well but the execution (even by the button of course) displays the record content on the dashboard scripting window !! It doesn’t need to be diplayed and the user should not see this. Where is the problem ?Thanks for your help.

Hello, I have a formula in an excel sheet that fills a column with a corresponding value based on the drop-down selected in another column. I’ve made this work in Airtable however it will not allow me to format the data as currency since it is saying that the information returned is not a number- which it is. The formula is: IF({Interactive Type}=“”,“$0.00”,IF({Service Provider}=“Other”,“$0.00”,IF({Service Provider}=“Affiliated”,“$6.00”,IF({Service Provider}=“Numera”,“$5.50”,IF({Interactive Type}=“Cameras Only”,“$0.00”,IF({Interactive Type}=“”,“$0.00”,IF({Interactive Type}=“Basic Interactive”,“$1.25”,IF({Interactive Type}=“Basic Automation”,“$2.00”,IF({Interactive Type}=“LTE Interactive”,“$2.75”,IF({Interactive Type}=“LTE Automation”,“$3.50”,IF({Interactive Type}=“Other”,“$0.00”))))))))))) Is there any way to adjust this so that the result returned is identified by Airtable as a number so that I can use it in further calculations down the sheet?

Hi, I’m so used to working in Excel that im struggling to work with formulas in AirTable… I’m making a dashboard and want to see incomings, outgoings, profit per month. I currently have a table with the incomings and another table with the outgoings. Id like to see a chart on my dashboard which lists incomings, outgoings and profit (incomings minus outgoings) per month. An easy one in excel but im stumbled on AirTable when it comes to using it for calculations. I’m using it just fine for task management but trying to include financial data and run reports accordingly. Thanks for your help

I’d like to create a calculated field that should count the total amount of “checked” checkboxes on a record. Is it possible?

I am looking for some help calculating a graduated sales commission in Airtable. The sales commission is calculated at a rate of twenty-five percent (25%) on the part of the sale price up to and including $25,000; plus twenty percent (20%) on the part of the sale price over $25,000 and up to and including $100,000; plus fifteen percent (15%) on the part of the sale price over $100,000. If the sale price is $150,000, then the commission is 25% of the first $25,000, then 20% on the amount from $25,001-$100,000, then 15% on the amount from $100,001-$150,000. If the sale price is $5,000, then the commission is 25% of $5,000. The commission structure is visualized like this: Any help with this would be greatly appreciated. Thank you

I have a forecasting sheet pulled from our CRM. The sheet lists various deals underway, each sitting at one of 8 stages: Contract Negotiation Terms Negotiation Proposal - Sent Proposal - In Progress Evaluating and Planning a) Evaluating and Planning b) Proposal Sent c) Contract Negotiations Our CRM applies a probability of closing at each stage, but doesn’t place that in any column that I can export, so I have added a formula field to apply that. (Pic 1) As part of our forecasting process, our BDs will review the deals and if the CRM assigned probability is different than they think it should be, they can enter that. A percentage field called {Probability per BD} Lastly, I have a field column to display the CRM % or overwrite it with the BD’s %. (pic2) Now…here is where my issue is: Part of our exercise as well is to ensure that if the deal is over 50% probability of closing, AND over $50K in value, They need to establish what the start and finish date is over the contract is after

Ok, not sure I wrote the title of this topic clearly so here’s an explanation. I have “duration” data imported into Airtable in an ISO 8601 format (just into a normal long text field, more on that below). An example of this format is PT1H30M15S which is equal to 1 Hour 30 Minutes and 15 Seconds of time. PT stands for “Period of Time” for those curious. Problem: I am trying to get the ISO 8601 formatted data into airtable’s “Duration” field but the formatting does not seem to be 1:1. Using the above example, pasting “PT1H30M15S” into an airtable Duration field gives 3:36:55 instead of the expected 1:30:15. I assume this is partially why I had to import this data into a long text field and couldn’t get it to work out the box with the duration field. Can anyone help me figure out how to properly convert my data into Airtable’s duration field format? I have a feeling it’s going to require a formula so that’s why I posted here under this category, but if not my apologies.

So we’re about to hit our max capacity for our Base. I wanted to delete some older records from some of the tables, but they all contain dates/formulas/autonumbers, pretty much the staple of most databases/spreadsheets, and according to the error message I get, records containing “computed fields” cannot be deleted. so Airtable’s response of “to free up additional space, simply delete some older records” would never work for anyone in most circumstances. How on earth do we delete records, Airtable??

Hey everyone! this is my first post here, hope that’s the right place, and thanks in advance :slightly_smiling_face: I have a table that is synced to a Google Drive folder, adding new records each time files are added. I want to write a formula that numbers the files in each folder in a new column, so for example if I have 5 records of a folder named “test” I’ll have a column counting 1,2,3,4,5 in the new records created. when a new folder is added, I want the count to begin from 1 again. Is that possible? Again - thank you so much, everyone!

I am trying to create a reminder function in Airtable that will populate a field in a record with a “remind me” time based on a selection from a previous field. Then, when that field is equal to the date and time, I will have a “last modified time” update and run a Zap in Zapier to send a reminder. Here is the formula I am using to try and populate the date/time for the reminder to run. It works for “At due date/time” and “10 minutes before”. However, any intervals beyond those two are not populating the formula field at all. The spelling is correct between the formula and the trigger selection menu. Is there something in my formula making it run wrong? IF({Remind Me When}=“At due date/time”,{Due Date},IF({Remind Me When}=“10 minutes before”,DATEADD({Due Date},-10,‘minutes’,IF({Remind Me When}=“30 minutes before”,DATEADD({Due Date},-30,‘minutes’,IF({Remind Me When}=“1 hour before”,DATEADD({Due Date},-1,‘hours’,IF({Remind Me When}=“2 hours before”,DATEADD({Due Date},-2,‘hours’,IF({Remin

Hello! I would like to create groups based on a “contains” value in a text field. Since this is not an available option, I think I am going to need to use a formula to create a field that I can group by. Objective: Create a conditional formula that will select a drop-down based on conditions. Sample Data: What I would like to happen: If Campaign Name contains “PF_MU30_Weekly” Change Email Type to “Weekly” If Campaign Name contains “PF_MU30_ReEngagement” change Email Type to “ReEngagement” If Campaign Name contains “PF_MU30_Engagement” change Email Type to “Engagement” From here then I can group by the Email Type field. Thank you for your help!

Hi community, I have this result : [Salon, Bureau, Salle de bain] How can i add quote on every word to have this result : [“Salon”, “Bureau”, “Salle de bain”] Thank’s a lot for your help. Matthieu

Hi, I am fairly new to Airtable and couldn’t find a solution to this here! I have a formula that does a division calulation using the output from a formula field and a linked field value. Here is my setup, I am getting #ERROR! in my calculation field. Here is my formula I have tried various combinations of using the VALUE() function but I can’t get it to work. Thanks in advance. John

Hey guys, Can you please suggest to me a formula that will remove a text after multiple characters ( ‘’,‘’ ‘’/‘’ ‘’ - ‘’ ‘‘and’’ ‘’&‘’ ‘’;‘’ ‘’(‘’. I already found on this Forum the formula that removes a text after one single character, LEFT( {Column name}, FIND( “-”, {Column name} )-1 ) Can you please advise me on how to add to this formula options for other characters, or maybe the new formula that will have a such workflow, thanks in advance :slightly_smiling_face:

i’m looking for a way to creat a formula that takesmy “date created field” date and translates it to the following: January = 1 February = 2 March = 3 April = 4 May = 5 June = 6 July = 7 August = 8 September = 9 October = O November = N December = D and then the Year with just the 2 digits. Examples: October 12th, 2003 = O03 September 3rd, 2014 = 914 January 27th 2018 = 118 February 19th 2021 = 221

Hi all! I have a question about a formula field. In one of my bases, I have time sheets with the following fields : Start (with date) - ‘‘Début affectation’’ End (with date) - ‘‘Fin affectation’’ Lunch break (total time in minutes) - ‘‘Période temps de repas’’ Total time (in minutes) - ‘‘Temps pour feuille’’ The Total time field has the following formula : DATETIME_DIFF({Fin affectation},{Début affectation},‘minute’) I can’t figure out what to put in the formula to substract the lunch time from the overall hours. Can someone help me? :slightly_smiling_face: Thank you!

Hi all, I’m trying to calculate a date based off the launch date that doesn’t exclude weekends (sat/sun) and holidays. I’ve figured out this formula that works but it’s not excluding weekends. Is there a way to exclude weekend dates from this formula below? DATETIME_FORMAT(DATEADD({Go-Live Date}, -8, ‘days’), ‘MM/DD/YY’) Thanks in advance! Bri