Formulas

I’ve scoured the message boards and cannot find a workaround for this. I have a formula to calculate a Due Date in a formula field; however, the filtering options for the formula field is quite unhelpful. I would love the option to filter for due dates within a week from now (etc) but the only options I have are “contains” and “empty” - which prevents me from creating filtered lists of Tasks that need to be done soon. The best I can do is sort the entire list - which isn’t nearly as helpful. Anyone have a workaround or idea here (I would like to keep the formula as is - it tracks when items need to be done based on a final date)? I could copy and paste all the values into a date field, but that extra step might be unnecessary (hopefully)? Here’s the formula I’m using if helpful: DATETIME_FORMAT(DATEADD({Event Date},({Days Before Event}*-1), ‘days’),“M/D/YYYY”)

Hello, I’m pulling my hair out… I have the following formula: REGEX_MATCH({Products},"Personal") {Products} is a ‘A Name’ column and the above formula produces a 0 if “Personal” is not present in the column’s field and a 1 if “Personal” is present in the column’s field. Using the same formula on a different table: REGEX_MATCH({Products},"Personal") {Products} is a ‘Lookup’ column and the above formula produces a 0 if field is blank and a #ERROR! if not blank. The only difference between the two scenarios is the top formula is checking a ‘A Name’ column and the bottom formula is checking a ‘Lookup’ column. What am I missing? Why am I getting an #ERROR!?

Is there a formula I can use to replace, say, " with " ? I find myself often having to go in and do this manually. Thanks in advance for your suggestions! :slightly_smiling_face:

Hi I am having trouble working out the following. When “delivered” is written in the status field I would like “Late” to appear in the PO field after 48 hours and be blank otherwise. Hope this is clear enough, fixing this would be amazing. Jos

New to airtable and a novice to formulas, so please ignore my ignorance. I am trying to come up with a formula to apply a fee based on the difference in days between two fields. I have been unable to get it to work. I think I can just create a chart and calculate fee based on results, but I am trying to avoid that. Basically i an trying variations of IF(DATETIME_DIFF({Cancellation Date},{Order Date},‘day’)=1,50,DATETIME_DIFF{Cancellation Date},{Order Date},‘day’)=2,75,)etc… No mater what I try, I can get the first part of the formula to work, displaying the first result. I cant figure out how to continue the formula! I need to end up grouping 1-2,3-5,>5. ANY help would be appreciated as I am going nuts!

I have a column A with strings and columns B and C with formulas from column A. I would like to create another column D which calculates the value from column B, but if an error occurs then receive values from column C instead of column B. Then, the IF function should be… IF(formula(B) has a valid value, formula (B), formula ©). How can I implement this? It should consider #ERROR! as a ‘false’ condition.

I need to use DATETIME_FORMAT two times in my IF formula, otherwise it won’t show the date correcty. The current formula that I am using is: IF({Execute day (before/after)}=BLANK(),{PROJECT DEADLINE},DATETIME_FORMAT(DATEADD({PROJECT DEADLINE},{Execute day (before/after)},‘days’),‘D-MMM-YYYY’)) Can’t figure out where I am supposed to add the first DATETIME_FORMAT formula. Any suggestions would be much appreciated!

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: