Formulas

For each record in my table, I have a Start_Date and an End_Date. These dates define a period of performance for each record. Fiscal Quarter 1 begins on 7/1/2019 and ends on 9/30/2019. FQ2 begins on 10/1/2019 and ends on 12/31/2019. FQ3 begins on 1/1/2020 and ends on 3/31/2020. FQ4 begins on 4/1/2020 and ends on 6/30/2020. For each record, I want to determine how many Workdays (including holidays that fall on weekdays) of the period of performance fall within each FQ. Any guidance in developing a Formula that can do this would be greatly appreciated. I am a Pro user, if that makes a difference. Thanks in advance!!

Is there an easy way to grab share price data? Google Sheets has a function to do this - is there anything equivalent in Airtable - or do I have to write a script? Thanks

Hi there, I would love to get some guidance on how to do something like a conditional roll up within one table. If you see the table example below you can see that I have contractual start and end dates that I want to use as a guide for calculating Total Revenue so that I can calculate % of monthly revenue against the total revenue. It is the TOTAL REVENUE column that I am having difficulty with as I don’t know how to make the calculation conditionally based on the contractual year that a particular month falls within. And as you can see, a contractual year may differ from calendar to financial year. Is there a way to do this? Thanks in advance!

I am trying to create a scoring system for this table. For example IF my Status is “Interested” then Field 18 equals 5. IF Status equals “Sent High Level” then Field 18 equals 2…IF Status equals “NO - PASS” then Field 18 equals 0.

Hi - I’m looking to create a cohort analysis of my user base. I need to make the first column a week-long date range for (Sun-Sat). Each successive column would have the number of users who signed up during that week-long period that returned during the following weeks. The second column would be Week 0 (i.e. 100%), the third column Week +1, then Week +2, etc. How do I do: The date ranges in the first column The successive columns to represent the following weeks? Thank you all for any help! Best, Chris

I have a Training Schedule table with different Training topics (Topic A, Topic B, Topic C), assigned to Agents (let’s say agent Stacey). I have a second table called Agent System Status which tracks the training of the agents. (Stacey has completed Topic A, and Topic B, and she is scheduled for Topic C). When the training is complete, there is a box on the Training Schedule table that says ‘Training Complete’. When that is checked off, I want the Agent System Status for Topic C to update to ‘Completed’. I currently have a formula in the Topic C column which reads IF(FIND(“TOPIC C”,{Training Schedule}),“Completed”,""). This will show Completed as soon as it is assigned, but I want it to say ‘Scheduled’ when assigned, and ‘Completed’ when that box is checked off on the Training Schedule. Is that possible? I cannot figure it out.

I’ve read numerous examples, but can’t seem to get what I need working. At a guess, I’d say this is a very simple formula. Essentially, I have a table with a field ‘Box size’ where people can select small, medium or large sizes. There are also ‘small box with eggs’, ‘small box with coffee’ as just a couple of variations that can be selected. I have tried to create a formula field that simply searches for ‘Small’, ‘Medium’ or ‘Large’ from the records ‘Box size’ field and places the simplified value into a new field called ‘Simple box size’ so it can be used to display on reports etc. Is someone please able to show me an example so I am able to get my head around this? Thanks so much. Cheers, Dave.

Hello community, I have an issue where I have a list of people who took part in a survey, for which I have assigned gender to in a single select field. In the next tab I have a rollup field which to bring up the number of males and then another field for females and so on. However, I cannot seem to get it to count the number of males who took part for example. Is rollup correct for this? I tried typing in COUNTALL(“males”), result is 1

Hello all, I seek your advice please. Is it possible to switch views in a linked field using a conditional statement? Eg if colour = black, Switch to linked view filtered to black item, if colour = blue, switch view filtered to blue items? Thank you in advance

Hello everyone! Trying to figure out the best way to do perform this formula. Column Name: Invoice Status Formula rules within column: If “payments” column is filled, and “balance” column = zero show “Paid” If “payments” column is empty, show “Unpaid” If “payments” column is filled, but “balance” column is greater than zero, show “Partially Paid” All help is much appreciated!!

I need to find the number of days an agent is active. Currently, I have 3 columns “Start Date”, “End Date”, and “Days Active” and my formula in my Days Active column is: DATETIME_DIFF({End Date},{Start Date},‘days’) Some agents are still active and do not have an “end date” and I get an error message in the Days Active Column. How do I enhance my formula to replace null values in the End Date column with today’s date?

I have a Base with the following tables: People, Contact History. In Contact History I am logging all conversations with the people from the first table and many times there are multiple records in Contact History for every person in the People table. I am trying to show in the People table just the notes from the most recent contact with that person. As I understand it, I can’t use a rollup field to do this because it’s a string. Any advice pointing me in the right direction would be greatly appreciated!

Hi! I have a list (multi-select?) in column C, and a text field in column D. I want the value in column D to be added to ever item in column C. Is that possible? The full thing I"m trying to do is I have one table where people make requests to their friends. One column “friends” has a list of all their friends they’re making the request to. I want another table to record the result when making a request to each friend. There may be multiple requests to the same list of friends though, so I want that new table to have the FriendName+RequestDate as the identifier. I think that to do this I need to have a column in tableA that has the list of Friends+RequestDate, separated by commas, so I can make it of field type Link to Records to the new table so that everytime I add a new request, it will populate the second table with one row for each friend+request date. Maybe there’s another way to do that?

I am new to Airtable and trying to uppercase only 3 characters of Company Name UPPER(Company Name,3) & RECORD_ID() (The whole company name is uppercase)

Hi, I need for CRM to create a formula where it is 20 days after the the last day I add a contact with that person. How to do that? Thanks!

A kind of Word-Blacklist : test1,test 2, test3, test4 and if question if its true then markt the formfield with the word Error how can i do that?

max 7 words seperated with , - is functionable count the words- functionable now how can i combine both into a formular? IF(LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ‘,’, ‘’))>7, “ :x: ”, “ :white_check_mark: ” - LEN(SUBSTITUTE({Tags}, ‘,’, ‘’)) LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ‘,’, ‘’))) Attachment fields: you say in the formular: attachment field then cames the full name and the link but i want ONLY THE link how can i do that?

Hi, can you please help me? I hope I can explain it well. Field 1 Single Select (SGL,DBL,TPL) Field 2 Price for SGL Field 3 Price for DBL Field 4 Price for TPL Field 5- I want to pull the information from either field 2,3 or 4 depending on what was selected on Field 1. If I chose SGL, I want to pull the price for SGL. Angel

by a normal multiple selector ist the normal seperator a , how can i seperate with ; instead , ?

Hello everyone, first time in this forum but I’ve been using Airtable for a while. I have an ecommerce and when there is a new order I get this information in my “Purchased items” (cant change this). count: 1 productName: 102 productSlug: rollin-102-bleu rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’} variantImage: variantName: 102 Gravure personnalisée (+20€): Avec gravure or véritable variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’} variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-avec-gravure-or-veritable count: 1 productName: 102 productSlug: rollin-102-bleu rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’} variantImage: variantName: 102 Gravure personnalisée (+20€): Sans gravure variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’} variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-sans-gravure I would like to extract some info for ecah product, for example the count, productSlug, pr

i will combine: LEN({Long Text}) + IF(LEN({Long Text})>280, “ :x: Over Limit”, “ :white_check_mark: Under Limit”) The result should be: example: 150 :white_check_mark: Under Limit

Hey, I have a scenario in Integromat that integrates with my Airtable CRM and it triggers when someone buys my event ticket. When they buy the ticket, they fill some info like CNPJ and the contact email in a form. Then the integromat scenario runs, it need to find in Airtable the CNPJ (rollup field), if it matches the CNPJ in the field, great! if not, it should find the contact email in the field Email Contato 1 (rollup field that join values from many contacts), if the email matches, great, this is the right record, but if not, it should find the contact email in the field Email Contato 2 (rollup field that join values from many contacts), if it is the right record, great, but if not, it should be empty. I made a formula in integromat but it is not working as expected. It is below, but since integromat formulas change a bit because we can change the data before it goes to airtable, i’m also sending pictures. share.getcloudapp.com – 13 Nov 19 share.getcloudapp.com

Is it possible to have a formula take into account whether or not a given field is hidden in the current view? For example, I have a Name (“Bob Smith”) field and a Role field (“Developer”). I want the formula to show the Name if it’s not hidden and just the Role if the Name is hidden. This allows me to share an anonymized version of the record on a publicly shared view, while using the Name field internally when managing data.

Hi there After searching here I have successfully created formulas to manipulate an email field and now I want to know if those formulas can be combined so I only need one extra field. I get email addresses like this … teacher1@plc.nsw.edu.au pupil2@students.acc.edu.au pupil3@student.ncc.vic.edu.au teacher@covenant.nsw.edu.au pupil5@chairo.vic.edu.au pupil6@student.ncc.vic.edu.au pupil7@students.acc.edu.au pupil8@students.acc.edu.au teacher@frsa.asn.au teacher@acc.edu.au and I want to end up with JUST the domain and with the “student” and “students” part stripped out (e.g. acc.edu.au, or frsa,asn.au I can end up with the domain by using this … RIGHT(email, SUM(LEN(email), -FIND("@", email))) And I can get rid of the “student” and “students” by using this … SUBSTITUTE( SUBSTITUTE( {email}, “students.”, “” ), “student.”, “” ) Is there anyway of joining these two so that I only have one field? I tried the “&” but I just end up with two domains. Many thanks! And thanks to those who po

My formula is ({Fee Before Disc} *Discount) - {Fee Before Disc} Despite it being a positive number subtracting the discounted amount, it ends up being a negative number. Example: 1800 -40% disc =1080 but it shows up as -1080? Why would this generate a negative number? How can I fix this?