Formulas

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 am using this formula to populate those fields… IF({Stage}=“Reviewed”, LAST_MODIFIED_TIME({Stage})) The problem I am now running into is keeping those date entries even as the stages changes. So when the multiselect is changed from Reviewed to Approved, I would like both the Reviewed Dates and Approved Dates to remain. With the above formula, only the date of the current stage remains. Please help! Thank you!\ -Vicki

Hello, I am trying to substitute all the occurrences of ", " (comma, space) in a Lookup field with “%20” and place that into the formula field.

Hello everyone ! I have to calculate the cost of a product. It can be made of Tissu, Wool or both. The problem comes from the “total” columns with a Formula field. When I have some empty fields, the “total” field shows “NaN” because it divides by 0. I can’t write the formula to avoid this. Does anyone have a solution? Thank you in advance, My formula : IF({Prix Tissu}=0, {Prix Tissu}/{Nb par tissu}=0){Prix Tissu}/{Nb par tissu}

Hey all completely new to Airtables, merely hours in! I was wondering how do I move on to the next cell if the current cell has a value? I’m trying to calculate deadlines given an estimated number of days a project will take from a production date, however occassionally I’ll have a predetermined deadline but no start date so want the value to remain and move onto the next row. My formula is: IF({Build Start}, DATEADD({Build Start}, {Build Days}, ‘days’)) This works but if a value already exists it replaces it with a blank space? Ideally I’d just use an else and continue.

Hi, I am experiencing unexpected behavior. Is this expected behavior that MIN function in formulas doesn’t work with lookup fields that contain numbers (in the same format)? Would be great if I someone can check & maybe I am getting this wrong? Thanks in advance

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