Formulas

Trying to create several calcs for implementation dates. Need an example of what will give me days for Connectivity that show Project kickoff date -30 so for example if Kickoff happened 60 days ago, Feb 2, then Project Kickoff Date (Feb 2) minus 30 would equal 30. I need a whole number as an output. I know the excel Days formula gave me that but I’m not sure how to convert that into an Airtable formula that works. Date Diff does not seem to work either. So I need something like formula = (whatever function (Project Kickoff Date – 30)) = X Days

Hi there, I would like a formula whereby if a particular field (Subscription ID) is empty then the formula returns “Y”, otherwise it doesn’t return anything (formula is left blank). How would I go about this? Thanks, Blake

Hi, I am creating an Airtable form with a drop-down option. Now the options in the dropdown are the entries of a different table. It looks like this: When I open the form, it shows the IDs, not the names of the Hotels: Is there a way to control which fields of the other table are being displayed (i.e. not just the IDs) ? Thanks, Christoph

Hello All! I am attempting to get the Record ID of a different Record. I’m using a basic numbered column and then a formula that just adds 1 to that numbers field to get its next logical Record (Successor). I’ve used Record_ID() to get the id of the parent record. Does a way exist to lookup the id of another record based on that successor?

Hello, I have seen in this video My Workflow with Merch Wizard and Orbitkit Integration - YouTube 2:20 to 2:47 you have some functions you can make with air table, but I haven’t been able to figure that out. Basically, I want to create a parent child function. And if there Is a parent, I want all the keywords to sum up in an extra column. At 3:48 you can then select one parent or child and it will pull the keywords from one table to another. Way above my head. I appreciate all help.

Hi, I’m trying to get familiar with the SWITCH formula by taking a shot at using it instead of a nested IF but can’t seem to figure out the following. I have a field called “Expiry Date” (type Date) and another field called “Expired?”, which is a formula field where I’m trying to use the SWITCH formula. My goal is that the “Expired?” field formula should output “Expired” if the “Expiry Date” is lesser than TODAY(); it should output “Expires today” if the “Expiry Date” equal to TODAY(); and should output “Still Active” if the "Expiry Date is greater than TODAY(). The formula I’m using is SWITCH({Expiry Date},<TODAY(),"Expired",=TODAY(),"Expires Today", >TODAY()"Still Active","N/A") I’m definitely doing something wrong because I’m getting a formula error. Can somebody please help me with this. Thanks, Sean

Hello, I have a trouble to get mp4 URL’s working, when they are generated by Formula for Attachment field. Situation: Attachment {Attachment} field has some mp4 files in it. Formula field refers into Attachemnt field. {Attachment} Result is an URL for mp4 file. Clicking the URL opens a new window with video file, but video file won’t get started. However, Video file works, when clicked directly in Attachment field, but not via Formula. I need to open it via formula. Is there a work around or way to fix it ? Windows 10, Browser chrome. thank you, Lassi

Hi all, I’m looking to display the result of a formula as a read-only check box. The result of the formula is always a 0 or 1. Here is the formula: IF(AND(OR({Meal Plan Shopping List Recipe}, {Non Meal Plan Shopping List}), NOT({In Pantry})), 1, 0) Adding a screenshot for reference. I’d love it if the 1/0 column could show a checkbox if 1 and nothing if 0, like the other columns. Thanks in advance!

Hello, Airtable newbie here. I have been trying to work out this formula for hours now and I can’t figure out what is wrong. I want to create a custom SKU based on when it was set up. This is my formula that is erroring: “EY” & RIGHT( {LIST DATE}, 1) & ROUNDUP( MONTH({LIST DATE}) / 3, 0) & ”-“ & {SKU IDENTIFIER} The SKU should start with ‘EY’ and then a two digit number where the first digit is the year (1 for 2021, 2 for 2022, etc.) and the second digit is the quarter. I have plugged each subsection in separately and each work by themselves but when I string them with & it errors out. I have tried both Concatenate and &. I’m sure it’s something very simple I"m doing wrong but I’ve read the documentation many times over and can’t figure it out.

I have a table with a multiselect field for stages: Submitted, Reviewed, Approved, Completed. I would like to track the date of each stage change in separate fields. So Date Submitted, Date Reviewed, Date Approved, Date Completed. I used the Last Modified Date field type, but it overwrites each time the stage is changed and that does not work for our needs. Any suggestions? Thank you!

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.