Formulas

Hi fellows, I am really stuck trying to solve this problem. Any help will be most appreciated. I have several date ranges that represents employees leave/absence. In order to grant vacations i need to check how many employees are out. So I have a employee date range to be compared with several date ranges. For instance: Range to be compared: begin |-----------------| end b |---------------------|e b |----------------------|e b |---------------------------------------------|e b |---------|e So i need to filter (show matches 1,2,3,4) and count this matches. Any clues ? Thanks in advance.

Hi there, I’m trying to query my main table by grouping Enterprises (Company Name) to see which portfolio companies have them as either a lead or customer. I need to query such that only groups with more than one record (where count > 1) show up to be exported/printed. Is there a way to filter out by the count after they’re grouped? Or is the only way to do it with a manual counter and if so how should i go about that? Thanks

Hi there, I’m working on a database to manage shots in a video piece. In the most basic form, Table1 has a list of every unique shot with timings and other info. Eg.as below (the actual tables have far more fields). Table1: SHOT __ TIMING 1 __ 01:00 2 __ 01:05 3 __ 01:14 4 __ 01:22 My 2nd table relates to a series of notes, and each single note relates to a single shot from the 1st table, but a shot can have multiple notes. E.g. Table2: SHOT __ NOTES 1 __ Low Quality 1 __ Not cleared yet 3 __ Fixed 4 __ Cleared 4 __ Low Quality 4 __ Awaiting Update So using a Look-up on Table1 and Roll-up I can manually add and display all these, but I’d like the Look-up to auto-complete / auto-populate with all the entries on Table 2 that contain the same numbers Eg… 1 __ 01:00 __ Low Quality , Not cleared yet 2 __ 01:05 3 __ 01:14 __ Fixed 4 __ 01:22 __ Cleared, Low Quality, Awaiting Update Am I

Can a SWITCH() formula be used to output different criteria for different options and/or combinations of a multiple select field? Essentially, I want to say: “If multiple select field contains Option 1, return Result A. If it also contains Option 2, return Result A, Result B.” So far, when I’ve made a SWITCH() function, it will only return if a multiple select field is one thing or another but won’t return if it’s both things. How could this be built without a zillion characters of different scenarios written out in the formula?

Hello, I want to have a field that concatenates the Due Date field with the Description field. It is showing up as this: 2019=05-31T00:00:00.000ZCafaro Service My formula is: CONCATENATE({DUE DATE},Description) We do not have the time format selected in due date, so I’m not sure why it is generating this way. We would prefer a simplified date be shown in Month/Day/Year and have that displayed in front of the project description. Thank you!

hello to all. im trying to export a base as CSV, and i need to have the name of the base change via formula. i want the name of the CSV file to be the date of creation and a couple more informations. how can i use formulas on the name of the base?

I am trying to create a view that is filtered for the last event/row that occurred > 90 days ago. This is for my fleet maintenance tracker I use in AT. Potentially, the structure I have created for this base may prevent me from accomplishing my task. With that being said, I log maintenance in AT via entering a new row for EACH service. On each row, I post the date of service, type of service, and vehicle (along with some other nonessential data). I am trying to create a simple view that displays vehicles whose LAST oil change is > 90 days. Any thoughts?

I’m struggling to make the bars of my bar chart ordered from low y-value to high y-value. Even though my view is like this, the bar chart orders the x-axis in alphabetical order. Anyone have a tip?

Hi - new to Airtable. I have a small web design agency and we manage hosting for some of our clients but not all. Consequently I have a ‘Hosting’ field with 2 just two options, ‘Managed’ and ‘Not Managed’ I then have username and password fields. When ‘Not Managed’ is selected in the Hosting field, I would like the Username and Password fields to be auto-populated with ‘not known’ rather than fill this in manually every time or left empty. When ‘Managed’ is selected there should be no action so we can fill in username/password manually. Can this be done?

Starting recently images appear cut off in all lookup fields on IPad. They look ok on a iPhone.image|690x178 Any idea what happened and how to fix it? Thanks

Hi AirTable Community - I have a grid with one single select column indicating contributors (Freelance Writers) and another column indicating Word Count (#). The Word Count field will be an integer, but the cost calculation will depend on where the word count falls within a numerical range. I’m hoping to generate a formula that generates the cost of the article based on the word count. It would read like this: If {Freelance Writer} is “option from single select column” and {Word Count} >=500 and {Word Count} is <=700, Cost “150” but I am striking out. Any advice on what I’m missing?

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!