Formulas

Hi everyone, AirTable formula newbie here! I am hoping to change the calculations I have in my formulas dependent on which item was selected in my single-select column. Under “Locum $,” the formula is currently: ({Orientation Day}*180)+({Paid Days Off}*180)+({Coverage Days}*550) The 550 value only applies to the “rural” communities. When “remote” is selected under the Community column, I would like to change that value to 600. Is it possible to create a formula that would automatically calculate the values dependent on whether “rural” or “remote” is selected under the Community single-select? Thank you!

Hoping this makes sense… this is a database for a share-house I have a table called “Recurring bills”. For example, we have rent due every month. I then have a table called bill payments. Tenants submit a form whenever they contribute an amount towards a bill. The recurring bills table then calculates the total paid, to show how much is owing in total, and how much each tenant owes. I’m trying to figure out if there is a way to make each next recurring payment, check if there is a negative balance on the previous payment, and calculate the total based on that… if that makes any sense at all. I created a blank base with an example to show what my current set up is. As you can see, Tenant 1 has made extra payments towards rent that has his balance, and the total owing, in a negative. I’m wondering if it would be possible to then have the next month, take that negative amount and change what Tenant would be owing for that month.

I am trying to use a formula to return the value of a sponsorship package for a golf tournament. I have a column called “Sponsorship” and under that column have values “Level 1, Level 2, Level 3, Level 4, Level 5” I want to use this formula but it keeps giving an error so I must be doing something wrong. Would appreciate any help. IF({Sponsorship}= “Level 1”,“$5,000”, IF({Sponsorship }= “Level 2”, “$10,000”, IF({Sponsorship }= “Level 3”,“$15,000”, IF({Sponsorship }= “Level 4”,“$20,000”, IF({Sponsorship }= “Level 5”,“$25,000”))))) Thanks, Jim

I want my formula to return 1 if it is a 3-month interval after a particular month contained in another field. So say the given field has “March” in it. I want the formula to return 1 if the current month is 3 months, 6 months, 9 months, or 12 months after March. Using this example, it would return 1 if the current month was June, September, December, or March. Or be different if the original month was a different month and a 3-month interval also. What formula would I use for this?

I am trying to have my airtable automatically assign someone a category based on the date range that their entry falls in. Specifically, if someone’s birthday is within their zodiac sign dates, I want it to assign them that single select in the zodiac field. For example, Aries is March 21- April 19th. If someone enters the form with the Birthday as April 1, I need it to add the single select of “Aries” to that record. I know that this needs to be an IF() formula, but I am having troubling getting adding in the date without a year. If I have to make it with a year, then this field will have to be updated yearly, which isn’t the end of the world, I just need to know if there is a way to do a date range only using Days & Month. Thanks in Advance!

Hi! How do I make it possible for the return of my formula to be not more than a specific number? Thanks!

OK… straight to the point… I’m importing order data using Zapier and the data source from which I am importing only provides comma-separated order data. For example, if a customer buys a qty of 3 of item 1, and a qty of 2 of item 2, I get a record like this: item1, item1, item1, item 2 item2. I cannot figure out how to get that string into “useable” fields, like an item field and a qty field. In addition, I also have a price field that acts the same way. For example, in the same order as the above example, I would get: 2, 2, 2, 4, 4. (assuming the price of item 1 is $2 and the price of item 2 is $4). I’ve worked with, and successfully setup tables with comma-separated order data, but those data sources had a qty field that matched the item and price. This one does not have a qty field. In a perfect world, the result would be a record that showed each unique item in a field with the associated qty and price in corresponding fields. Thank

Hello everyone, I have a table that has a few columns. One of the column is ‘amount(price)’ and also ‘status’ which I uses ‘Single option’ field, it consists of 3 options → paid, unpaid, paid half. So, I want to add another column which calculates all rows with status that are selected as ‘unpaid’. And can those rows that have a status of ‘paid’ and ‘unpaid’ change it’s color based on the chosen option? Ex: I want ‘Unpaid’ rows to be in red color, ‘paid’ to be in green color. Appreciate if someone can help. Thank you.

Hi I’m creating a booking system. need help in creating a formula in creating Start Date/Time and End Date/Time. in my form, I’ve an option for the customer to select multiple slots. I will need to add date add funtion to select start date/time and end date/time for calendar view.

Hello Airtable community friends, It’s been a while! Thanks to some of you, my online book business is running smoothly on Airtable. But I am far from Airtable proficient yet… How can I get the Date and Time formula fields to check and see if there’s something in Manual Date & Time first, and then, if it’s empty, turn to Unix Date & Time? I don’t know how to weave the necessary IF statement into the DATETIME_FORMAT I’ve already got set up in the Date and Time fields. I labeled the screenshots 1 and 2 'cause they’re the same, only the second shows the formula I’m using. Many TIAs for ideas!

My records have a lot of cells (columns). Now i want to write a search formula to horizontally search and scan all cells of a record to find a specific value. How can I solve this? About search formula’s: Airtable Support

I cannot get this formula to work. Can someone help? I have a type field where you can select print or PDF. I need the start date to calculate a date either 4 weeks for print or 10 days for PDF. This is what I have: SWITCH( {Type}, ‘print’, DATEADD({Start Date}}, 4, ‘week’), ‘PDF’, DATEADD({Start Date}}, 1o, ‘days’) )

Regarding property bookings. We have a check-in & check-out field, both with time fields included. Typical check-in starts at 3:00pm and check-out is 11:00am. DATETIME_DIFF to see booking length in ‘Days’ shows 1 less that, in these cases (clearly it is counting days in 24 hour blocks, and 3:00pm-11:00am is < 24 hours). Is there an easy workaround I’m missing, to use date fields w/ time fields included, yet calculate booking length in ONLY # days? Let me know if this makes sense.

Hello, this is more like a documentation rather than a question, for those who might find it helpful and have similar use cases. If there are errors in the formula, please point them out. I have an attachment field called Picture. The number of pictures I’ll have in the attachment field can vary from 1 to 20. I want to grab the URL of each image, and put them into multiple columns. To do this, I’ll have to create 20 additional columns, one for each field. Note that this works as long as the image names don’t have any brackets in them. Hopefully, this won’t be a problem for many. The logic behind the formula is as follows: I have an attachment field called Picture I create a formula field called URL beside it, and the formula in it is {Picture}. This returns the URL of all the images in the Picture field, along with the image names. This is the field I’ll try to clean up and extract the individual

Hello y’all, I’m writing out a formula that will generate dates. The dates will be based off a different calendar field in my table, and will be adjusted based on a dropdown menu. There are two formulas I’m attempting to use. The first is as follows: IF({Recording Date},DATEADD({Recording Date},SWITCH({Status},‘Outlined’,-3,‘Script Writing (including editing)’,0,’Review Outline’,-2,’Review Story Draft’,-2,’Ready to be Recorded’,0),‘days’)) With the goal being to adjust the date off of the “Recording Date” based off the “Satus” (the drop down menu). This has worked well. However, when I tried a similar approach for a column called “Upload Date,” the formula doesn’t work. Here it is: IF({Upload Date},DATEADD({Upload Date},SWITCH({Status},‘Recorded and Ready to be Edited’,-5,’Requests for Re-records’,-3,’Sound Edited and Ready for Review’,-2,’Complete and Ready to be Uploaded’,0),’days’)) Do you by any chance see an error in how I’ve written out this

I have a field that captures the URL for a webpage, but some URLs contain extra parameters after the .html part of the string. How can I remove anything after the .html for those records but not have a blank cell if the URL does not contain parameters after .html? I’d like a formula so that new field displays the URLs, just trimming the ?utm… part where needed: https://www.test.com/page3.html?utm_campaign=hc-sc https://www.test.com/page2.html I figured out the trimming part using this formula, but not how to display URLs where there is no ? LEFT({URL}, FIND("?", {URL}) -1) Thanks!

Right now we have a formula that uses percentages based on timeliness (Auto Calculated Percentage). This value is determined through a set of time between two dates. However I’d like to be able to override that for certain employees using another field like a checkbox or maybe a number field. So maybe an if statement would go in the auto calculated percentage? Like if override field is blank then use auto calculated percentage field? I can’t figure out how to do it ({Payment from Customer}*({Auto Calculated Percentage}/100))-{Admin Fee}-{Insurance Fee}-{Amount Paid to Employee}

Hey everyone! I’d like to create a tab in my pipeline in Airtable that shows me the number of closed deals each month and the total amount of money made during that month (ideally it should be a tab made of 24ish fields, so for example january (3 deals) - january value (25k) - february (4 deals) - february value (38k) and so on). In our pipeline we have a created time field which is the date the new lead enters the pipeline, and also an “acquired” field which is the date that that deal is moved from Negotiation to Contract. I’d like to track the number of deals that enter the Contract stage each month (if a deal enters Contract in january and it is still there the next month, it should only be counted in january). Maybe I’m overcomplicating things, or maybe it is only my poor english when it comes to explainings things that I have in my head :grinning_face_with_sweat: Hope you can help me, thanks in advance! Lorenzo :it:

Something strange with my formula. It was working fine earlier, but not now (something changed?) IF({Date} >= 365, “ :x: ”, IF({Date} <= 0, “ :x: ”, IF({Date} < 334, “ :white_check_mark: ”, IF({Date} >= 335, “ :warning: ”)))) When field is empty or 0 (zero) it still show :white_check_mark: Why?

Hi, I have product names with sizes inside, I would like to extract just the sizes, example : Converse Black - size : 40 Adidas Amerocaban - size : 44.5 Nike black T-shirt - size : XL Extraction in the column just to the right: 40 44.5 XL In summary, this would be the value extraction just behind “:” Do you have a solution? Thank you in advance and have a nice day,

Is there some way to use only Airtable instruments to calculate the great circle distance between two points? I have those points with lat and long coordinates and need a formula to calculate the great circle distance (or something close to it, no need in high precision). I know the haversine formula, but lack of trigonometry functions in Airtable makes the whole thing impossible. I am not into scripts, zapiers and web-hooks unfortunately. I have a Google API key which is used to create maps from Airtable data, but I do not need to calculate road distance or time, only great circle (use case is aviation). Just a formula of any complexity would be great. Thanks to everyone concerned.

I feel like this is simple but I’m missing something. I want 2 text fields joined with 4 blank spaces in between. Field1 & " " & Field2. It is trimming out 3 of the blank spaces on me. Is this possible?

I’m trying to create a field reference using a formula field. As an example: if I have a table with FieldA and FieldB and FieldC I want to create a formula field that will link to “FieldX” where X is a variable A, B, or C. It seems possible to build this if I know all the possible permutations of field names (using SWITCH statements) but what if it’s an open-ended variable?

I have an interesting problem. I need to create database-sort-friendly versions of names, where all definite and indefinite articles are dropped from the beginning of each name. I came up with this formula: IF(REGEX_MATCH(Name,'^THE'), SUBSTITUTE(Name, LEFT(Name, 4), ''), IF(REGEX_MATCH(Name, '^A'), SUBSTITUTE(Name, LEFT(Name, 2), ''), IF(REGEX_MATCH(Name, '^AN'), SUBSTITUTE(Name, LEFT(Name, 3), ''), Name ))) But it’s not working for names that start with “AN”. The formula is deleting all "AN"s. Why is it working with “THE” and “A”, but not with “AN”? What am I missing? I am at a loss. Thank you in advance!

How can I write a formula where I could say "return true if value is 95000, or starts with 923, or …) I’d probably use a regex for this but airtable doesn’t allow that. What’s the next best thing?