Formulas

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 configurat

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}

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

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 th

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 c

Rollups using ARRAYJOIN will wrap in a Medium, Tall, or Extra Tall cell, but those with ARRAYCOMPACT and ARRAYUNIQUE get truncated. I would think this is not the desired behavior.

Conditional Formulas seem not to work if a Single Select field is the condition when lowercase letters are used in that Single Select field. For example IF({Paid} = “paid”, “Yes”, “No”) doesnt work if Paid field consists of single select of paid or unpaid. Whereas, if the Paid field consists of single select of Paid or Unpaid, the above formula works just fine.

Hello guys, What is the exact formula to get that from another table and count data on a column? Thanks

Hi there, I run a coffee service business. i have a table called “scheduling”, and then one for “sales orders”, and another “work orders”. my schedule for my driver includes a mix of coffee deliveries and work orders. So I would like to be able to generate either type of order and have it populate on the ‘schedules’ table. I also have a column on schedules for “complete”, and this would be defined (look up) by the status set for the order in the respective two tables. i can’t figure out how to have this column auto-complete from two different tables. I know how to use the ‘look up’ feature, but it seems in this case i am trying to have the column look to both tables. if this isn’t possible I suppose next option would be to create two different columns in Scheduling, one that points to the status if its a work order, and the other column if its a sales order?

What I want to do is insert a formula in an airtable field that multiply’s values I input in Hourly rate, hours and rate fields. This is the formula I have tried but I get an error {Hourly Rate}HoursRate I’ve attached the spead sheet image of what I want to do on airtable

I use Airtable to track inventory sold on a variety of platforms. One multi-select column notes if the inventory is Available, Sold, Donated, Trashed, etc. In another column I note the date it was sold. I’d like it to automatically mark it as sold if I’ve entered a date into the Sold Date column, but to still have the ability to mark the status differently if it is trashed, etc. Don’t want to add any new columns, but just make a formula.

IF({Sale Date}="",(DATETIME_DIFF({Sale Date},{Purchase Date},‘d’), (DATETIME_DIFF({Today’s Date},Purchase Date},‘d’))

I’m not sure this is actually a bug, as it looks more like intended behavior. However, it is inconsistent and undocumented, and it has cost at least one user some money.[1] I’m documenting it here so that, bug or not, there is at least a trail of breadcrumbs for those encountering it in the future. There’s a known but seemingly undocumented Airtable behavior where, if a formula field might sometimes evaluate to a string and sometimes to a number, numeric responses are converted to strings before being displayed. For example IF(MOD(VALUE(RECORD_ID()),2)=1,'Yes',1) results in a column of various 'yes's and '1's, left-aligned, showing the ‘1’ has been converted to ‘'1'’.[2] However IF(MOD(VALUE(RECORD_ID()),2)=1,'Yes',0) results in a column of 'yes's and blank fields. Furthermore, if I test the results using T()