Formulas

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

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

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 some

I’m trying to calculate dates based on two values. I have a formula that works for this: IF({Renewal type} = "Annual", DATETIME_FORMAT(DATEADD({Last register date}, 90, 'days'), "MM/DD/YYYY")) If I try to add another IF statement then it fails: IF({Renewal type} = "Annual", DATETIME_FORMAT(DATEADD({Last register date}, 90, 'days'), "MM/DD/YYYY"),IF({Renewal type} = "Semi Annual", DATETIME_FORMAT(DATEADD({Last register date}, 300, 'days'), "MM/DD/YYYY") What am I doing wrong?

Airtable doesn’t seem especially forgiving of formulas doing numerical operations when the original cell contained text. For example, I have a cell describing engine sizes of cars. They’re pasted in like the following: “1498cc”. I’d like to show the engine size in litres, which would translate to “1.5 Litres” What I’d like to do is have a formula substitute out the ‘cc’, then divide the remaining 1498 by 1000 (which would be 1.498), then round the 1.498 to 1 decimal place (1.5), and then put " Litres" at the end of it. The following formula works exactly as you’d expect in Google Sheets / Excel: =concatenate(round(substitute({Engine Size}, “cc”, “”)/1000, 1), " Litres") However, Airtable formulas doesn’t seem to be able to treat ‘1498’ as a number after the ‘cc’ has been removed.

Hi, When there is a field with a pure text value “1 + 1”, I would like to dynamically calculate it and put the result in to the other field. For example: | 1 + 1 | 2 | | 2 * 3 | 6 | Thank you.

Hi, whenever I try to reference a set of results I have in a “YIELD” column I get an error. the yield data is the result of a nested IF formula which itself references a single select field. Is there something I’m missing that means this data can’t be referenced by another formula? The Yield formula is: IF(TYPE = “Firkin”, “62”, IF(TYPE = “Pin”, “31”, IF(TYPE = “20L Keg”,“0”, IF(TYPE = “30L Keg”,“0”, IF(TYPE = “24 Case”,“24”, IF(TYPE = “20 Case”,“20”, IF(TYPE = “18 Case”,“18”, IF(TYPE = “12 Case”,“12”, IF(TYPE = “8 Case”,“8”, IF(TYPE = “6 Case”,“6”, “”)))))))))) Where TYPE is a single select field. But even if I try another formula field with YIELD*2 I get an error. Any help much appreciated!

Hi all – new to Airtable, but over the last 3 weeks have absolutely loved it and now using it for everything. It’s awesome. I need help with something that I’m not sure can be done yet. I have a CRM, with a table that is a funnel, each record being a different lead. I have a separate table that is my business dashboard: a summary of my activity. I want to have on my dashboard the number of leads I’ve contacted per month, based on the creation date of each record in the CRM Funnel. Is there a way to count all the records in a table, determine what their creation date is, and then get that single number and put it in a cell? Thanks in advance, Scott

This seems silly to me, but it appears that any percentage type field used in a calculated field is treated as a number instead of a percentage. For example: A percentage field set at 20% A currency Field set at $500 multiplied together = $10,000 Change the 20% to 0.20% and then it equals $100. Maybe this is intentional, but I cant’ figure out why.

Hello! Using the current formula: IF(DATETIME_FORMAT({Date of Outreach}, ‘DD/MM/YYYY’) = 22/07/2017, “ :white_check_mark: ”, “ :x: ”) But for some reason it doesn’t validate when the date is 22/07/2017 in Date of Outreach field… Any ideas why?

Help! How do I combine first name (column 1) and last names (column 2) in a 3rd column?

I have created a long list of concert dates in AirTable. I group the list based on artist name, and sort by concert date, so each concert date for a given artist appears chronologically in one data set. For an integration with another system, I need the last date listed for an artist to have a check box checked in the same row as that final concert date. Keep in mind also that as concert dates are announced, they get added to the list, at which point the check box for the previous last date must be unchecked, and the new final date check box should be checked. My process now is that I manually review the list and uncheck the previous last concert date, and check the updated last concert date. Any thoughts on automating this process?