Formulas

Hello! I’m looking to create a calculated field that shows me the number of days it takes to complete a record. The start date should be when the record is created, and the end date will be based on a dropdown field’s value (e.g. when the status field is set to “complete”). My thinking is that I need to create three formula fields: The date when a record is created (I’ve managed to achieve this part). The date when the status dropdown field is set to “complete”. The difference between the two dates in days (I’ve managed to achieve this part). So basically I’m missing how to create the 2nd field. Am I approaching this the wrong way? Is there a better way to show this report that doesn’t involve using an app add-on? Thank you!

Hi! I’m making a database for me to keep track of my train delays and the compensations I need to request, have requested and are approved or declined. I like to have an overview of the route I took. For this, I have two fields: Departure train station Arrival train station Both have these two options in a single select: Antwerpen-Centraal Noorderkempen Basically I want to only select the departure train station, and the formula should put the other option in the arrival train station. For example if I select “Noorderkempen”, the arrival train station should be set to “Antwerpen centraal”. How would I achieve that? Thanks in advance!

Hi, As you can see in the image, formula (price - discount + shipping charges) is returning 1 in first field as I have two values separated by commas that is 399,1076. Is there any way to write this formula, so it takes into account units separated by commas and return the correct result.

Hello! Can anyone advise on the IF formula to state if a date has passed a number of days? So if the date in “DATE” column is more than 60 days ago, then :white_check_mark: or :no_entry: . I’ve been looking through date and time functions in the Formula Field Reference, plus lots of posts in this community, but haven’t been able to find an answer that works. I’ve got a formula to display the number of days since, and looked into creating a view filtering for those above 60 days (that didn’t work anyway), but I would prefer it to just display :white_check_mark: (60 days or more) or :no_entry: (less than 60 days). Thank you for your help. Ian

I have a table where I am tracking employee trainings. I have this formula to output whether the staff member is up to date on a certain training: IF({Days} = BLANK(), “Overdue”, IF({Days} > {Max # of Days (from Training)}, “Overdue”, “Up to Date”)) I am trying to add another option. I have another formula that determines whether the staff member is almost due for training: IF(AND({Days} < {Max # of Days (from Training)}, {Days} > {Overdue Threshold (from Training)}), “Soon to be overdue”) I was wondering if I could get help with the syntax to combine the two formulas into one?

Hey Airtable Friends! :slightly_smiling_face: I’m working on a project where we want to have an error message pop up if our team accidentally selects a billable item that is not authorized to bill for that specific product sold. It works perfectly when there is only one linked record in the ‘Product Sold’ field, but if there are two linked records, it will always give a ‘ :stop_sign: This Service is NOT Authorized to use this Billable Item’ message, even if the linked records are included in the ‘What Products Can Use This Billable Item’ field. This is Formula I’ve set up: IF(FIND({Product Sold (from Project)},{What Products Can Use This Billable Item}) <= 0, '🛑 This Service is NOT Authorized to use this Billable Item', '✅ This service is authorized to using this Billable Item') Is there a way to edit this formula to work even if there are multiple records in the Product Sold field?

Hello, I’m trying to get a formula working to determine age in years with one decimal place. The date is in a column called “Created”, the formula I’ve got is: DATETIME_DIFF(TODAY(), Created,'years') & ' years' Can anyone help add the decimal place so the result for example is “1.2 years”? Thank you.

I am wanting to use the multi drop function to automatically assign numerical values to my clients profile line once I choose their desired choice. For example, the client is borrowing money and they choose to have 5% interest rate and 3 points on the loan. Or if they choose 4% and 4 points, I would want the drop down to fill the other two pre made fields with the data instead of having to go and put it in there myself. I have looked a most videos and cannot see the steps to make this happen if possible. Thanks again wonderful community!

I have the following options in a multiselect field with: Table sample options: first row: “$5,400” & “10,400” second row: “10,400” & “Promo” How can I extract the first option; for first row would be “$5,400” and for second row should be “$10,400” Thank fo your help. The ‘comma’ is giving me hard time to extract the right record.

Hello - I am trying to calculate hours difference between two start date + time field & end date + time field, and looking for a formula that works to do that. Here is a screenshot:

My sister can somehow calculate the value for the field End date in her head. Can someone please give me the formula that she is using, as I need to use a formula? :grinning_face_with_big_eyes: https://airtable.com/shrWJArPUxWUy7HG2

Hi everyone, My goal here is to allow my master inventory list to have a dynamic price based on the most recent purchased price of an item. So if an item cost $10.00 in August, but costs $15.00 in September, my inventory will initially show a cost of $10.00, and when the September-priced item is purchased it will update to $15.00 I could get this done with an Automation but i’m looking for a cleaner way to do it without having to add formula columns and update their results to a text field in order to use the Find Records functionality of Automations I’m using a Rollup field to pull the most recent Linked Record by using the MAX(Values) condition shown below: This allows me to grab the most recent record in my purchasing table that’s linked to the inventory item. I would then like to pull the associated price from t

Hi all, I am trying to count the number of ways this particular phrase appears in a cell So for the image above, count the amount of times the string “Late Payment” occurs, i.e would return ‘2’. Thanks team,

Ok, I am working on a project and have been staring at my formula for quite a while now. Hoping somebody can help me out! Essentially, I would like to combine several fields into one, but include certain fields only when they are filled. Here is my formula: DATETIME_FORMAT({Clinic Date},‘MM-DD-YY’) &" “&{Clinic Name + Activity}&” - “&{Blue - Allocated}&” Blue"&", “&{Red Allocated}&” Red “&” , “&{Yellow - Allocated}&” Yellow"&" - HL Provider: “&{HL Provider}&” - ( “&{Lead Team}&” )" In this scenario, the following are true: Blue, Red, and Yellow allocations are numeric fields that may or may not be filled in on each row. I’d like them to only appear if they have a number greater than 0 populating that field on a given row (otherwise, I don’t need to see it in the formulated text) HL Provider, Clinic Name

How would you convert 10000 to 10 000? Similarly, 10000,00 to 10 000,00. I couldn’t find an existing formula for this.

Hi! I am trying to sum certain values ​​from a specific row and column and I don’t know how to do it. Better to explain it with an example: In this image I am trying to get the following value in the third column: (1 + 2 + 3 + 4) / 4 = 2.5. That is, I am trying to add the 4 items from the second column (1 + 2 + 3 + 4) and divide them by the value from the first column (4). The result will be displayed in the third column. Like this: 4 1 2.5 4 2 2.5 4 3 2.5 4 4 2.5 All this for various groupings, such as the one shown below. Like this: 4 1 2,5 4 2 2,5 4 3 2,5 4 4 2,5 7 1 1 7 1 1 7 1 1 7 1 1 7 1 1 7 1 1 7 1 1 Could someone guide me? Ma

Hi! This is my table: https://airtable.com/shr0FiM4g1quNwnpG. It’s a table of our customers’ active licenses. Each license continue to be active until the customer calls us and terminates it. Then we remove the record from the table. Start day = The date the customer signed the license. Notice period (days) = The amount of days in which the customer need to terminate it’s license in advance. Otherwise the license is continued for another Agreement period. Agreement period (months) = The amount of months the customer is tied to it’s license. Tied until = The date the customer is tied to it’s license, I.e. Start day + Agreement period (months). The problem: I want the last field, Current possible termination date, to calculate a future date, that is the last day the cus

Is there a way for a formula to appear only on certain conditions? For example, I have the following situation: I have a # field and a date field I’d like to create a formula that auto calculates three-weeks out from my date field ONLY when the # field is not empty Here is the formula that I currently have: DATETIME_FORMAT(DATEADD({Clinic Date}, 3, ‘week’),“MM-DD-YYYY”) Is there a way to set up that additional condition so I don’t further clutter up my database? Thanks in advance to anyone who can assist!

Hello! I want to have a Formula as the Secondary Field. Now the Formula is: CONCATENATE(“PR-”,Autonumber,"/",YEAR(TODAY())) The “Autonumber” is a Field as Autonumber. How can I achieve, that the “Autonumber” in the CONCATENATE is always 4 digits long, independent of it’s length. Besides that I want only the two second digits of the year. So the values of the Autonumber is “4”, I want: “PR-0004/21” Thank you for your help in advance! Best regards Attila

How can I display numbers with 3 digit all the time ? Like 1, 2, 3, 10, 125 → 001, 002, 003, 010, 125 Pierre,

Hey AirTable brains trust, I’m trying to build a balance tracker in AirTable so that I can not only set up a budget, but also forecast what future account balances might be. I feel like I’m SO CLOSE. But I’ve found 2 flaws: the formula only seems to work if there’s an entry for the day before it. If there are no transactions for a few days, then the formula breaks somehow. the formula can’t cope if there are two transactions on the same day. I suspect the problem is found in the DateAmt column / formula (but that’s mostly an educated guess). I’ve straight copied the formula from the base that I’ve modelled it off (created by @W_Vann_Hall, of course). Here’s the base:

Hi! I want a field to show one of three values (Active, Expires in a week or Expired) depending on if the field “Startdatum” is after today, within a week or before today. How do I solve this? Thanks!

See the attached screenshot for an idea of what I’m trying to accomplish. I’m using a formula to concatenate the names of sellers and buyers. Simple except that if there are two or more names, how can I remove the identical last name, or add an & between names if they are different names?

I could use some assistance with extracting a word from a string. Here is an example of the string. 200 East Broadway #Z001 (Jacob to Seymore) This is the formula that almost has what I need but missing something. MID({Transaction Name}, SEARCH("#",{Transaction Name}), SEARCH("#",{Transaction Name}, SEARCH(" “,{Transaction Name})) - SEARCH(” ",{Transaction Name})) I’m trying to extract the apartment number (#Z001) from the string. Thanks in advance to anyone that tries to help with a solution.

Hello! I already use automations with dates and formulas to set recurring tasks at daily, weekly, and monthly intervals. I have a task that occurs twice a week (ideally on tues/thurs). Is there a way to do this and avoid AT scheduling tasks on weekends or irregular days (tues/thurs one week and mon/wed another etc.) Thanks!