Formulas

I have a “Rates” table that I am linking into a column on an “Estimator” table. It seems as though I can’t use the linked-in Rate data in a formula on the Estimator table. Is that right??

Hi there, I am trying to extract a string from a field using the MID() Formula. It should be pretty straightforward, but the output is blank for every single record. I have tested this by using several differently formatted fields (i.e. Text, Single-Choice, Multi-Choice), and have not been able to output a result in any of my tests. For example, I want to remove the emoji at the beginning of the string “ :hammer_and_wrench: Service” in the field {Department}. My formula is: MID({Department},2,0) The result is always blank. Is this something I’m doing wrong, or an actual bug?

This is for a registration DB Column 1 - Order Amount (this is a currency format) Column 2 - Amount Paid (currency format) Column 3 - Balance Due (formula with 1-2, formated as currency) Column 4 - Scholarship? (checkbox - if family got a scholarship then all fees are waived and this box is checked) Column 5 - I want this to say 1 of 3 things: If Column 3 is $0.00, then “Complete”, if Column 3 is anything greater than $0.00 AND Column 4 is not checked, then “Pending”, but it if is checked, then “Scholarship” THANKS!!!

Hi, I need a formula to calculate 4 seperate Service level agreements The SLAs are as follows: 4 hours Same Day 48 hours 10 days I need a field to show the “due” date for each record based on its sla. My base creates Opened Date when record is created. I assume I need a nested formula to calculate the date/time when the SLAs will expire. Ive written this but only the first part works IF({SLA}=“same day”, DATEADD({Opened Date}, 1, ‘DAYS’), DATEADD({Opened Date}, 60, ‘DAYS’)) IF(SLA=“A 4 Hour”,(DATEADD({Opened Date}, 4, ‘hours’,IF(SLA=“P 10 Days”,(DATEADD({Opened Date}, 10, ‘days’)))))) Once the completion date is added I will create a formula to comfirm if the SLA is met or not. Many Thanks, Ant

I’m wondering if there’s a formula that will return the date of a specific weekday, such as Monday, relative to a set date field. Any ideas?

Hello, I am looking to write a formula to populate a field based on two separate fields using the IF AND statement. This would be Field 1 = A and Field 2 = B then insert C into the formula field. Is this possible? Thank you. Jason…

Hi, I ran the below experiment with DATETIME_DIFF. It seems the units argument only performs a simple division, but most implementations I have seen of this function always compare the actual units. Am I missing something?

Hello Guys, I would like to do something like: IF(Taxes"= 1") Cost * 1.14975 IF my taxes column is checked, do the math “Cost Column” * 1.14975 = Sum Is someone can help me? I look on community and documentation, but I’m still clueless. Thank you! JM

Hello, I am trying to create a field where I can calculate the difference in time from one record to another. I.e. calculating the time difference from Time Left Work (from the previous record) to when the commence work (the current record). I have read various multi-record calculations but I am unsure how best to approach this. Any assistance would be appreciated. Thanks!

I was idiot-checking some advice I was about to give (meaning I was the idiot needing to be checked) when I ran into this. There’s an earlier mention of this problem from January 2016, so I am going to assume that was fixed long ago and something similar has broken since then. In the following screenshot, {Date} is a looked-up date field, while {Month}, {Day}, and {Year} are formula fields that simply call the function indicated with {Date} as the parameter. (That is, {Month} is a formula field with the formula MONTH({Date}), and so on.) Note the results for {Day}. I then modified {Date}'s configuration, changing it to a date field. Here’s the result:

I have a table where I need to display the amount given (in field {amount} if it was given between particular two dates. Like 5/1/17-5/1/18 I’m at a total loss of how to do this.

I’m currently manually entering labels for all dated entries so I have a single select with Sept 2017, Oct 2017 etc

I use this base for creating invoices, and my formulas are not calculating the right numbers, giving me incorrect sums. GW correctly rolls up (using SUM(values)) hours from a separate table, and the field is formatted as a numeric with two decimal places. But when those sums are multiplied by the Rate (a Currency field with two decimal places), it gives the wrong number. When I copy and paste the rollup values into a new column (GW2) that is just numbers rather than rollups, and then multiply by the rate, it gives the correct number. Example: In the below screenshot, the total number of GW hours (“GW”) is 27.87, and the rate (“Rate”) is $35/hr. In reality, 27.87 * $35 = $975.45 (“Reality”), but the product given in “GW Subtotal” is $975.33. Formulas used: “GW” : SUM(values) “GW Subtotal” : GWRate “Reality” : {GW2}{Rate} This seems to be some bizarre rounding error, because some products are high and some are low. Can you help? Thanks, Patrick

When a field using FROMNOW() is sorted from A-Z, the sort appears to be based on the first number of the output, rather than the time. Example: Days should be considered smaller than months, which should be smaller than years. Output should be read the same as DATETIME_DIFF(): Secondary issue: FROMNOW shows deadlines a day earlier than the actual deadline (see difference in images above)

Hello, DateAdd appears to be adding days incorrectly. To replicate: Col1: date field (date only, no time) Col2: DATEADD(Col1,1,‘d’) Format Col2 to show date only, no time. In this case, Col2 shows the same date as Col1. When time is shown, behavior seems to be normal. See photos attached:

Hi everyone, Need some help here. I am part of an ecommerce site for second hand luxury items. I need some help with an equation to calculate the value sellers receive for their products. I am guessing will need to use the IF Function. The price is being split up. Here is a breakdown of what a seller will receive. On the first AED 400, you will receive 40%of the selling price. On anything between AED 400 – AED 8000, you will receive 65%of the selling price On anything exceeding AED 8000, you will receive 80% of the selling price. Here is an example for you. If you are selling a handbag at AED 10,000 this is what you will get: First AED 400- you will receive 40% of the value, that is AED160 For balance AED 8,000between AED400 – AED8,000 – you will receive, 65% of the selling price, that is AED 5,200 For balance AED 1,600 exceeding 2000 – you will receive 80% of the value, that is AED 1,280 Could you help me formulate this on airtable? Thank you so much

Good Day, Looking to subtract a running total collected daily from the previous total to give me the value used since the last entry. Better explanation (I hope) Jan 1 my total is: 1111 Jan 2 my total is 1112, so Jan 2 total - Jan 1 total = 1 Jan 3 my total is 1115, so Jan 3 - Jan 2 total = 3 The running total is kept in the same Column/Field and I want the next Column/Field to auto-calculate the difference from day to day. In excel: Cell B2 I would use =A2-A1 Cell B3 would be =A3-A2 and so on and so on forever since the total never stop rising. Clear as mud i hope. I’m likely just overthinking it, and not seeing the whole picture. Airtable is new to me, but if I can get this to work, it will be perfect for our needs. Thanks, Marc

I recently implemented a data input routine whereby a user enters data into a single-line text field when is then error-checked and possibly converted to another data type based on the value of another field. For instance, if the {Type} field has a value of '[ Y or N ]', the system will accept only 'Y' or 'N', and it stores the value as either 1 or 0, as if it were a checkbox. Similarly, if {Type} is '[ Date ]', it assumes the entered string is in a specified date format and converts it to an Airtable date type using DATETIME_PARSE(). I try to verify entered data is in the proper range and format, alerting when that isn’t the case, but it’s not always easy to do so, given Airtable’s somewhat lackadaisical commitment to its own internal rules. For instance, I’d like to be able to use an ISERROR() check to catch supposed numeric or currency values that contain invalid characters, but a while back I learned such mangled input as VALUE('295gomje78') would effortlessly return 29578: Airtabl

Hello, I am trying to perform a mathematical formula on a Roll Up Field. Specifically I have… A bunch of records on Table A with FIELD ONE = (Float) On Table B I take an Average of the value of FIELD ONE s on Table A which I am doing using a Roll Up Field. This field with be named FIELD TWO. On Table C I would like to lookup the FIELD TWO from Table B and then multiply it by a factor of 1.2. Whenever I try to do this, every cell prints #ERROR!. I have tried using a Lookup Field and various types of Average/ Array Field Formats. I have also tried to use the “value()” function in my formula on Table C however it is not working. Does anyone know how to make this work?

Sorry if this has been asked before. Is it possible to add logic such as. IF column X is “yes” then populate a autonumber column, ELSE leave blank? Thanks.

I have a column with the type formula and it populates the date, but when I try to filter that date it treats it as a text, is there a way to treat that date as a date when filtering? meaning that I can filter by today or filter by date within a certain range…etc. ? Thanks in advance!

I need a formula for calculating when to send a birthday card that will return a date 10 days prior to the person’s birthday. So if the person’s birthday is 10-10-2019, I want the formula to calculate 10 days prior, or 09-30-2019, returning a date format. Any ideas on that? TIA

I have created a base for my classmates to update their contact info. How can I make the state field the 2 letter abbreviation vs the state name spelled out?

Not really a bug, I guess… In an earlier post, I said something like I can’t imagine the formula you cite could come anywhere close to maxing out Airtable’s maximum formula length or even a maximum line length (if such a thing exists); I’m guessing the maximum formula size (counting EOLs) must be 32,768 characters, or thereabouts. Yes, I exceeded it, by +/- 11,400 characters. I think there are workarounds, and I’m certainly not going to squawk about Airtable “only” supporting formulas 2^15 in length — but in case you run into a similar problem, now you know…

Hi! I’ve been a casual user of AirTable for a few years now, but at my current new job I’ve decided to spread the gospel so I’m building my first bases from scratch. Turns out not as easy as I thought! I have two tables, one tracking external companies (let’s say table 1) and one tracking internal programs (table 2). Both have columns identifying “population” and “condition”. These multi-select columns contain the exact same dropdown options. I would like to create a column where, If condition AND population from table 1 = condition AND population from table 2, table 1 would get checkbox checked, and table 2 would get the relevant company linked. Right now I’m manually linking the company name when this occurs which seems really unnecessary. Similarly, I have a third table tracking tool requests (let’s say table 3). If tool in table 1 = tool in table 3, I’d like to link the company name from table 1 into table 3. Again, I’m doing this manually now. I’ve gotten kinda close with this on