Formulas

Hello guys ! So, i would like to create a formula that count days from a specific date. Something that would add 1 everyday. I hope im expressing my problem correctly :nerd_face: Thanks

Hey after some help on the best way to calculate a total from 2 different entries in a table. So I am trying to develop a scoring/judging system for a school project. One of the sections is they submit a deign and they have to submit 2 identical copies and we mark them as A and B and we check them both against a set of regulations. I currently have a table that has the team listed twice one entry is for their A copy and the other entry is for their B copy. There are over 30 regulations they need to comply with, I have a column that totals the points scored for A and B separately but I need a way of combining them. I can’t just add them or do an average or take the lowest score. The way we judge it is if they fail a regulation for both copies they only incur the penalty once. If they fail it with only one copy then its still just the penalty once. What can happen is copy A fails regulation 5 and copy B fails regulation 7 for example I need a way of compari

Hi everyone! I’m new to learning how to use the deeper aspects of Airtable and how to use linked records. The problem I’m having … I have a multi-question survey, and am trying to calculate the percentage that people answered a question “yes”. However, some people skipped the question, so I need to remove these from the % calculation. My goal is to determine what percentage of responses were “yes” from those who actually responded. I tried by making a lookup table for each question that would only show answers yes/no (so no blank answers), and then the formula COUNTALL ({my lookup field name}) to first tell me how many responded, but I am not getting the correct answer. Does anyone have recommendations for how to solve this? Thank you I

Hi Expert, i have a field which keep a product volume, for example , 20g, 30ml, 45piece, 150l, 8pack. i just want to extract the numbers, so the after formula field will be , 20,30,45,150,8. i try to think and search for a while but have no idea how to do it , any advise?? thanks

Hello, I am trying to use Last Modified Date for particular fields to evaluate whether those fields were updated today, and if so, concatenate those updated values, separated by commas, and output those into my formula field. The final formula result will be included in a daily email digest of changes to my database, intending to show the recipient which of the specific fields were updated. I first tested the OR function by creating a simpler formula just to make sure I had the right idea, and this does return “Updated today!” for the one record that I updated today, with “No update today” returned for records that don’t match (the ERROR values are due to many of my records not having Last Modified Times yet, as those fields are new). I initially had trouble getting the dates to match up properly before realizing that I needed to convert the Last Modified Time fields via DATESTR() to make sure that I was matching text strings only. My IF/OR formula, worki

Table 1 is a list of clients with attributes. The table is as follows: | Company Name | Contacted? | … | … | | Company A | :green_circle: | … | … | Table 2 is a list of interactions with clients The table is as follows: | Contact person | Company Name | … | … | | Jeffe Bezos | Company A | … | … | Company A in both tables are matched together (as in I select from the drop down list the Company Name in Table 2). How would I make the “Contacted?” column in Table 1 automatically add this green emoji, if Table 2 has a match of the Company?

I currently have this: DATETIME_FORMAT({Date of donation}, ‘M/D/YYYY’) & “—” & IF({On behalf of corporation?}, {Donor’s employer}, Donor) I have another field named “Member Donor”. If the Donor field is blank then I want it to pull the Member Donor field.

Hi All, Having trouble figuring out how to use IF, AND, concatenate for this situation: If {Field 1} then return “Mary”, and If {Field 2} then add “Bob” to the end of “Mary”, and then If {Field 3} then add “Jake” to the end of “Mary” and “Bob” It continues for all combinations of Field 1,2, or 3. This was one attempt: IF( {Field 1}, ‘Mary’ IF( AND( {Field1}, {Field 2} ), ‘Mary and Bob’ ) )

I’m building a credit card base where we store expiration dates. One formula will simply tell us that a card is set to expire (aka when status is “Expiring”) within the month. At this point I have an automation set to email client with expiring card message. Now I’m trying to set up a second automation triggered by a formula field to let me know what the card is now “Expired”. So far this formula works to tell me if the card is Expiring or Valid. IF({Expiration Date}<TODAY(),“Expiring”,“Valid”) However now I want to add an “Expired” status too. Yet when I do this entire formula it doesn’t work. IF({Expiration Date}<TODAY(),“Expiring”,“Valid”), (IF(DATETIME_DIFF(TODAY(),{Expiration Date},‘days’) > 30, ‘Expired’)) What am I doing incorrectly?

Hello community! I’m trying to implement a simple badge system within an volunteer program. Basically, when volunteers pass certain checkpoints I’d like to award them a badge e.g. ran workshop X >> badge 1 ten total workshops >> badge 2 The pivot table extension creates a visualization of what I want to do where… row = volunteer name and columns = count of each workshop But I want this to perform this calculation within the base itself (perhaps using arrays in a formula field?) with badges being awarded via once certain conditions are satisfied. If there’s anyone out there that thinks they might be able to help but don’t yet have enough information pls let me know and I would be happy to try and explain further. Thanks very much, Alex

Hi, I have a database of professionals for whom I want to create a formula to search for the appropriate therapist using MAKE There are categories where I want to search all searchable categories, and there are categories where I want to search one or another category. for example- I am interested in looking for a therapist “for children” in the area or “Tel Aviv” or Jerusalem. AND( SEARCH(“children”,{categorys}) OR( SEARCH(“Tel Aviv”,{categorys}) SEARCH(“Jerusalem”,{categorys}) ) ) But apparently the formula is incorrect. Would appreciate help

The Count field lets you count the number of linked records. For example, take the screenshot below Line 8, I want the count field to count the number of linked records in Team 1-4 and then add them up. Is there a way to do this? Seems like this should be easy but may have reached the limit of Airtable. This is essential to the business use case I want to get Airtable for my organisation. Any work arounds are appreicated too.!

Hello all, Looking for a formula that takes input from another field to determine which item in the lookup to return. For example; LOOKUP FIELD | OPTION FIELD | RETURN FIELD Apple, Orange | 2 | Orange Apple, Orange | 1 | Apple Thanks all

Hi all, I have three columns: Member Number, Referrer, and Referrals. I am trying to count how many records contain the Member Number in Referrer, for the Referrals field. In other words, if I have 100 member records, and 5 records have “24” in the Referrer field, I would want the Referrals field in Member Number 24’s record to say 5 (the amount of times they reffered members). Does this make sense? Any ideas on how to do this? Thanks so much! Adam

Hey here, I’m tring to find the second to the max value of a array, but i can’t find any functions like Large in Excel. Is there any way to implement this function? Thanks for your help

Hope you can help. I have a table to record customers and products purchased. Each product comes with 6 months warranty. In the table I have a date of purchase column. I need another column that displays how many days of their 6 month warranty are left since the date of purchase. My question is: is there a formula that can do this? Many thanks in advance.

I have a formula that is taking a Start Date Field with value 9/10/2022 2:30pm GMT I am trying to get the time of the event in 24 hour format using DATETIME_FORMAT({Start Date},“HH:MM”) The result is coming up 21 minutes short and giving me 14:09 vs. 14:30. Makes no sense.

I’m trying to create a forecast model where we start, for example at $1000 in period 1, which grows at $250 every period. The growth is cumulative, so I’m unsure how to create this.

Hi team again, I need a quick hand, block with this atm In a table I have the following to fields File - Attachment : It could be an image, gif, video or audio Type - Multiple Select: Details de type of the image based on manually set options (image, gif, video or audio) Any ideas on how to set the Type based on File extension? If you’re wondering, the Type field will be used in a user facing filter to search by file types.

I know how to test for the presence or absence of a value in another field, e.g. IF( MiddleName != BLANK().... But it does not seem to work with a field that should return a number result, when the parameter being fed to the formula is empty. Here’s what I think is the standard Airtable formula for an Age calculation: DATETIME_DIFF(TODAY(),DOB,'years') Works great so long as there is a date in the DOB field. If DOB is empty, on the other hand, this formula returns “NaN” (“Not a Number”). That’s ugly. So I tried IF(DOB!=BLANK(),DATETIME_DIFF(TODAY(),DOB,'years'),BLANK()) But get the same result. Apparently BLANK() isn’t a number value. This wouldn’t be a problem in FileMaker, but if it were, I could coerce the (empty) result to a (non) value of type Number. In FileMaker these are both valid expressions: GetAsDate("") GetAsNumber("") Bu

I am trying to set up Airtable to have an interface where my office staff can get the amounts they need for a meal plan change. We have multiple plans and each week the cost of the plan reduces by a set amount until the end of the change period. The amount reduced is different for each plan. When someone wants to change their plan we take the prorated amount of the original plan and deduct the prorated amount of the new plan. If the amount is positive then the customer is given a refund, but if the amount is negative the customer is charged the additional fee. In either case, there is a set fee for the change. If they want to cancel the prorated amount of the plan, plus the remaining balance of their point plan, minus the fee is calculated so it can be refunded. There are some cases where the fee would not be applied. My goal is to have an interface my staff can use to choose the current plan then choose the new plan and the interface would tell them how much needs

Hello :wave:t3: I get concatenated values from a roll-up. The result is: CotisationsONIRCP I want this: Cotisations ONI RCP (with black in between each value) I tried using this formula but it does not change anything: CONCATENATE(values)&SUBSTITUTE(“”," ") Any advice? Thanks!

hi! I have a field that is manually entered and I’m trying to create a formula field that extracts the middle number (found between “-” and “.”) but excludes the starting zero’s. This middle number can range from 1 to 999. I’ve tried a variety of Right/Left & MID, but I can’t seem to exclude the starting zero’s. Examples: 123456-002.5 → “2” 154773-025.26 -->“26” 89347-031.124 → “31”

Here’s what I need, if someone would be so kind as to assist… I have a Submitted at column which tags the date/time the record was created from a form entry. I have a Completed column which tags the date/timestamp when the Completed box is checked, which concludes the job duration. And I have a Duration column which has the following formula: DATETIME_DIFF({Completed},{Submitted at},'minutes')/60 We work 7am-4pm, M-F… What I need is that duration field to exclude off hours, before 7am, and after 4pm, to get a true measure of the work hours that job took. Any help would be greatly appreciated!

Hello, I am trying to build a simple automation using Make.com (Integromat) when a form is submitted to Webflow I need to create, search and update a record. This is the workflow: I have 2 forms on my webflow website: Form 1 gets email address and IP address → Create a new record Form 2 gets a few checkboxes and IP address → Search for records that matches the IP address and update the record with the checkboxes I tried different formulas but the issue I am having is that when Form 2 is submitted all the records on my table are updated. I hope somebody can help me. Thank you. Here some screenshots from my automation.