Formulas

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?

Hi, I am having trouble writing a formula for the following. I would be most grateful for help! I have a calendar field for Due Date. I’d like to make a field that says “overdue” when the Due Date has passed, “today” when the Due Date is today, “coming up” when the Due Date is 2 days out, and “next week” when the Due Date is 7 days out. Thank you,

I have a book database and I am splitting the Author name into Author First and Author Last columns. The problem I run into is certain books are authored by a company. I don’t want to split the company name up. Is there a way to identify an company name and then place it in the last name column of the split formula. Something like this: If Name is Company x than place in last name column If Name is not Company x than place text before space into last name column. Thanks,

I have lots of dates that are setup as numbers in my excel sheet that I am bringing into airtable. I need to change the formatting of the dates while leaving them as numbers in the original column. Example. 20170112 YYYYDDMM I need it converted to 12-01-2017 MM-DD-YYYY I usually convert it using the following excel formula: =DATE(LEFT(CELL1,4),MID(CELL1,5,2),RIGHT(CELL1,2)) Thanks for any help you can provide.

I have an employee time log database where each employee signs in with a timestamp date field (they can adjust it if they need to). Then they log out, and an additional field calculates their hours: DATETIME_DIFF({Time out}, {Time in}, ‘minutes’)/60 This is working fine, but we’ve had a request to have the date display the day of the week, so instead of showing 1/11/18 9:30 AM, it would display Th 1/11/18 9:30 AM. It is my understanding from reading other forum articles that first I would need to change the field from a DATE field to a Forumula field, and then do something like this: DATETIME_FORMAT(NOW(DD-dd-yy ‘hh:mm-’)) I’ve tried various permutations of this but keep getting an error, so I’d like advice on the correct formula, but then would this impact the formula I referenced above that calculates the hours and minutes worked? Thank you. Otherwise, this database has been a great resource for a small business situation that previously relied on hand-written time sheets. I’d be hap

I want to convert a list of words separated by semicolons in one column to a list of words separated my commas in another column. Any Ideas on how to do this in Airtable?

Can multiple formulas be assigned to a field. I have a filed that determines the days between Estimated completion and Date started, Wanted to have the Field change color if late, if not late, or have a label stating “No assigned Date” if no dates were assigned to the task. Can this be done?

Hi I am new to Airtables and trying to create a formula that uses data from another table, is this possible?

Hello, I have a table of courses linked to a table of course runs. The course run table has many records connected to one course. The course runs table has multiple course run dates linked to a single course. I would like to calculate the next upcoming course run for a course in the courses table. I have read through the forums and found the ways that people have found the nearest date for events in the past using the max(values) function, but I can’t seem to find information how to compare all the course run dates linked to a course, to today(), and display the date nearest to today() in the future. Does anyone have advice on how to approach this problem? Thanks, Kevin

I’m wondering if there is (or could be) a way to change a formula for a particular cell or row? For example: I use airtable to track gigs for artists I represent. Typically, we take x% for every gig. In certain special cases, we may take y%. Is there a way to change the formula just for that cell to reflect the correct totals? Thanks!

OK, so, strictly speaking not a bug, but certainly an infelicity. :slightly_smiling_face: Maybe someone can step on it next time something else needs fixing in DATETIME_DIFF… For background, see this post in the Support forum. Specifying 'day ' or 'days ' rather than 'day' or 'days' produces wildly incorrect results: Thanks!

Is there a programmatic way to count columns? I am aware that COUNTA() exists, but I have to manually input the column names into the formula. Is there a way I can access the names of the columns and count the ones that contain the word “Siege” for example?

I don’t quite understand why I am seeing two different kinds of behavior from very similar ARRAYUNIQUE rollup fields. I am making a database of health issues. One table is Substances (nutrients, herbs, medications, etc), another is Issues (diseases, problems, or symptoms), and then I have a Junction table where I can select a Substance and an Issue and add in some other information (how/why they’re connected, along with references). On the Issue table, I want to be able to look at the record for an Issue and see all the Substances associated with it. So I have a Rollup set to ARRAYUNIQUE the Substances it looks up, because while I may have multiple Junction table records connecting the same Issue to the same Substance, I only want to see the Substance listed once. (For example, I may enter in many references in the Junction table for carnitine correcting exercise intolerance, but when I look up the exercise intolerance record in order to see what may be helpful to someone, I only need