Formulas

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?

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?