Formulas

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

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(), all fields in the first column evaluate as text, while only the ‘yes’ fields in column 2 do. This occurs regardless of whether the ‘0’ value is stated or derived; that is, if

Hello! Please help. I would like to transform (through “Formula”) integer minutes number into hh:mm. Can you help me build “Formula”, please? I still can not build it. Example: 297 min = 4 hr 57 min = 04:57 I tried different combinations, but in vain: DATETIME_PARSE(date, [‘input format’], [‘locale’]) DATETIME_FORMAT(MINUTE({Odpracováno copy}), ‘H:mm’) DATETIME_PARSE(MINUTE({Odpracováno copy}), ‘H:mm’) DATETIME_PARSE(MINUTE({Odpracováno copy})) DATETIME_PARSE(56, ‘mm’) DATETIME_FORMAT(IF(Minutes < 1, CREATED_TIME(), DATEADD(CREATED_TIME(), Minutes - 1, ‘minute’)), ‘MM-DD-YYYY hh:mm a’) DATETIME_FORMAT(IF({Odpracováno copy} < 1, {Odpracováno copy} - 1, ‘minute’)), ‘H:mm’) DATETIME_FORMAT({Odpracováno copy} - 1, ‘H:mm’) DATETIME_PARSE({Odpracováno copy}, ‘mm’) Thank you!

I am new to working with formulas in spreadsheets and I’m just curious if they are the same as the ones used in Excel or are these specific to Airtable. See: Airtable - Support Formula field reference For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation... Is there a name for this type of formula language? Is it VBA? Sorry if my question seems unclear.

Hi. I have an IF formula that should result in a number (except for one particular case). I need the result to be treated as a number and not text, so I can count the values, etc. I’ve set the results as a number with no “”, and tried formatting as an integer, but it still doesn’t work. Any workaround for this? Here is the formula: IF({Festivo especial}=1,“NEGOCIAR!”,IF(AND({< 7AM}=“SI”,{+12h}=“SI”),50,IF({FM Trabajo}=1,40,IF(OR({+12h}=“SI”,{Fin de semana}=“SI”,{Festivo}=1),30,IF(OR({< 7AM}=“SI”,{FM Viaje/Descanso}=1),20))))) This is the table: (the field to look at is “Importe”) https://airtable.com/shr1hzKX9IJzAgIec Here is an example where you can see it doesn’t SUM the values: https://airtable.com/shrE71ocEdRT5MU12 I hope somebody can give me a hint. Thanks!

Hello, I am trying to create an average based off of letter ratings. For example, I have a table of individual’s projects which has a column with a grade or rating for that specific project. It can be A, B or C. In a linked table for the individuals, I have a column which has all their grades/ratings in the one field. So one may just have an A and another may have B,C,B, and another A,A,B,B,A. I would like to have a formula field which will take the group of grades/ratings for each person and averages them. I don’t care if the result is a letter average or a numeric average. If I were a teacher, I guess this would be equivalent to getting each students GPA based off of just letter grade entries. Each formula i try, i get an error…I feel like i’m missing something very obvious. Thank you!

Is there a limit to the number of IF() formulas you can nest together?