Formulas

While looking for some tutorials I ran across this on the airtable website. I am very interested in using this, but can’t find it. Does anyone know where I can find it on the workspace? You can read the whole article here: https://support.airtable.com/hc/en-us/articles/360022745493#h_a8ad9c3a-e2c3-4b82-a4fc-b27a1d87104a

Hey, everyone! Our nonprofit is trying to use airtable to keep track of many grants that span different grant periods. I’m trying to figure out if one of the tables we transfer to airtable from excel can be our grant cash flow, where we keep our list of funders, the total grant amount, and then the amount divided by the number of months in the grant period. each grant has a different grant period with a different start and end date. is this possible?

I need to create a nested IF(AND statement for my commission sheets. Logical Argument 1: If the Set By field has a value in it AND Logical Argument 2: If the Month field is January, February, March or April The result would be: Calculate the formula Profit/2 multiplied by .125, otherwise blank. I also need in the same column: Logical Argument 1: If the Set By field has a value in it AND Logical Argument 2: If the Month field is May, June, July, August, September, October, November, December The result would be: Calculate the formula Profit multiplied by .0625 ., otherwise blank.

This is pretty simple problem, but I can’t figure out how to get this to work on Airtable. What I want to do is program a column with a function that converts information from a date column to another column in Month word form. Using Month () I can get it to work in Numerical form (i.e ‘12/14/89’ becomes ‘12’) But I can’t find a way to make it say ‘December’.

I want to assign a numerical value to all recipes that include steps of “Cutting” or “Chopping”, but it doesn’t seem to recognize these or ANY digit/number when using the find function.

Hi, can anyone help me figure out how to add a percentage rate to a chart? I have a list of candidates who took a test, some passed some failed. I want a line graph, tracking pass rate = %pass/total with the date on the x axis. Also additionally, is it possible to have two lines on the same line chart? Like pass rate by background and one line for each background?

Hi all, I am attempting to write a formula to tell me if a contact is overdue or not. I have contacts that I am to get in touch with on a Weekly, Monthy, Bi-Monthly, and yearly basis. Below, I have written a formula to tell me if someone who is tagged “weekly” is overdue or not. However, How can I have the formula caulculate for those who are tagged, Monthly, Bi-Monthly, and Yearly? Any input would be much appreciated. IF(Frequency = “Weekly”, IF(DATETIME_DIFF(TODAY(),{Last Contacted}, ‘DAYS’)> 7, “OVERDUE”, “DUE”)) Thanks!

Hi! I’m trying to write a formula field that will read as “true” when the “end date” is either BEFORE today or ISBLANK. In other words, the label “true” should correspond to records where the end date has not yet passed or is not known. Can someone help me with the formula for this? I’ve tried my hand at various IF statements, but can’t quite get it to work properly.

Hi, I am wanting to create autogenerated Job numbers for new projects. The sequence for job numbers is JXXX (where the x’s denote a unique job number. Does anyone know how to do this on Airtable?

Does anyone have a project management base they use they can share to give me an idea of other perspectives? My base is just way too much. It’s getting confusing to follow, track and update. We are currently tracking projects (job description, job#, photos, status, and subs assigned) A Scheduling Table (that doesn’t schedule) it’s really used for a daily job form for field employees to complete at end of shift (this one is a lot and confusing for me to track dates/time on site) Estimates table to keep up with estimates (Project Manager is in control of this and he doesn’t fill it in) Materials on Order - same with the PM not filling it in then my accounting table that I am slowly moving to another work base as the information doesn’t need to be shared with others.

Hi there, Our users are uploading multiple images to an attachment field. I need to grab the URL of the first of these images in a large thumbnail size (not the full resolution URL). I am using 2 steps right now. a formula field to render the images as coma separated urls. a second field in which I use a formula to successfully grab just the first of the images as a url. BUT I can’t figure out how to get the lower resolution thumbnail versions of this image, which I know Airtable has hidden somewhere. Can anyone help? Here’s the formula I’m using for now to grab the single image url. MID({SINGLE IMAGE WORKINGS},FIND("(",{SINGLE IMAGE WORKINGS})+1,FIND(")",{SINGLE IMAGE WORKINGS})-FIND("(",{SINGLE IMAGE WORKINGS})-1)

I am soooo excited that this function is available! But now that it’s here, I’m having a hard time thinking through how to set this up for my workflow. Looking for some formula help here… My scenario: I need track when someone submits a project to a specific status. Currently, I have it set up so I have a view in Airtable that filters out everything except projects in that particular status and then have a Zapier integration to send me an email when there’s something in that view. I then go in to that view and mark whether that project is late, on time, early, etc. based on the due date for that project. I want to be able to set up a formula to compare the last modified time (only for that one status though) and compare it against the due date for the project and return a result of early, on time, late, etc. Help?

I have 3 Checkbox Fields : Workout Meditate Eat Healthy If all 3 Checkbox Fields are checked (True), I want a Formula Field to equal True so I can set the color for that row as Green Here’s my broken formula : IF(AND(Workout, Meditate, Eat Healthy) True, False) Thank you in advance

Hi all, Looking for some assistance with a formula I’m working on. Columns A and B are checkboxes and Column C is a multiple select field with one of the options to select being “Prioritized”. How would I write out a formula for "if A and B are checked AND Column C = “Prioritized” return = “Ready” If anything else (neither are checked, only one is checked, or the project is not prioritized) then return “Not Ready”. Does that make sense? I think I’m struggling with the “AND” portion since this isn’t excel. Help!

Hi - I’m creating a purchasing database for a furniture install project and would like to make various calculations to the summary of a field. For example - multiplying the summary/subtotal by 8.25% for tax - then multiplying it again by 10% for warehousing and install. Finally - adding up the subtotal, tax and warehousing and install numbers to get a final total. Thank you!

Hello fellow Airtablers! I’m in a bit of a bind trying to put together a formula for what I’m trying to do here. I have 3 different single select fields, which based on if they meet certain criteria, will populate another formula field that’ll show “Complete” or “Incomplete”. That formula is working wonderfully. However, I’m trying to come up with a formula that’ll state the reason as to Why it’s showing as incomplete. I can sort of get it to work for one field, but not multiple. Here is the formula I have for the field that populates with “Complete” or “Incomplete”: IF(AND(Status = "Done", {Billing Status} = "Payment Received", {E-file Status} = "Accepted", {Billed}!=BLANK()), "✔ Complete", "❌ Incomplete") Here’s an image of what it looks like: As you can see, it’s marked as Incomplete, because the billing status is not marked as “Payment Received”. I’m attempting to create a column after the {Final Status} column that will show why it’s marked “Incomplete”. Is this possible? Any hel

Hey Airtable friends, I was wondering if my problem had a solution so here it is. I ask questions to users who can answer them or decline (blank field if they do so) and I would like to know if I could create an IF formula that tells me if their profile is complete (all questions answered, thus fields are filled) or not (one or several values in selected field missing). There would be around 10 fields/questions (one in each column). Any solution for this? Thank you in advance

I’m wondering how I can transform the formula to date format. I have the date 15/04/2019 20:46, but I got it using formulas. Now I need to add some days, thats why I’m going to use DATEADD. But there is some problem. How I know we can use DATEADD just with date format.

Hi there, How do I link Table A in spreadsheet A to Table B in Spreadsheet B? Many thanks!

Is there a way to duplicate single line text into a multiple select column automatically. My end goal is to be able to add additional item descriptions in my form while on the fly (Cell Phone Form). IE: Add Flamethrower to Item Description while on the go. Later I can filter based on the Item description.

Hello, looking for help on how to to create a formula that references text in another “cell” on a different table to complete the formula. Here’s what I’m looking to do: Create an “IF(logical, value1, value2)” statement formula for a column in table 1 in a base, that references a single cell from table 2 (within the same base) that contains text to be included as a one of the values? Can someone advise if this is possible, and if so, how to go about doing it? Thanks, Cristy

I want a formula to look at a string of 10 digits and only write the last 4. How would I do that?

Hi there! I am organizing seating arrangements for a gala. Each table number I assign to a group corresponds with the floor their table will be located on e.g. (Table 1-19 = Floor One, table 2-29 = Floor 2, etc.) Right now, I am entering the table numbers for each group in a Number field and want to create a Formula field that automatically outputs what the corresponding floor is. (e.g. If their table number is “43” then the formula outputs “Floor 4”). I am doing this so I can group the view by the Floor # formula output. Is a combination of Nested IF & AND statements the way to go? Here is the code I tried (it doesn’t work because it only returns “true/false” or “Floor One”). Open to any suggestions and thanks for the help! IF( {Table Number} > 0, AND({Table Number} < 20), "Floor One", IF( {Table Number} => 20, AND({Table Number} < 30), "Floor 2" ) )

Not sure why the basic question hasn’t been asked or it’s hidden so much in the help section that I can’t find it but can someone please tell me how to subtract a start date and end date? No time. Just date. Like others, I don’t understand the AT explanations as they just give formula names and not examples. Again - just looking for the basic formula on subtracting 2 dates, without time. weekends/holidays - can be included. :slightly_smiling_face: Thank you for your help!

I’d like to create a column that displays the number of times a specific number (which is the name of an individual record) appears in a linked cell. For example: I have a single cell with linked records. Each linked record is named with a specific number ranging from 0-10, so it looks like this: [7] [0] [5] [9] [0] [8] [5] [7] [3] [7] [7] [3] [10] [7] [2] [7] [0] Looking at the single cell, there’s a bunch of of linked records with the name ‘7’. Is there a formula that can determine how many times ‘7’ appears in that cell? The ‘count’ option only counts the total number of linked records, but not how many times a record with the same name appears. Thanks in advance