Formulas

Hey guys, I’m fairly new to IF statements, and looking to create a really simple IF statement by saying: IF [collaborator = Meg], show “Design” I have a column of “Collaborators” (Meg, Rob, Sarah, David, Josh) and a column of departments (Design, Analytics, Research, Strategy, Development). If Meg is selected as a collaborator, show design in the department column, if rob is selected in collaborator column, show analytics in the department column … etc … Any help would be greatly appreciated .

I have a table with a Lookup field that returns a date from a linked record. I’ve confirmed that the Lookup field contains valid date values. The MONTH() and YEAR() formulas work as expected with the Lookup field. But the DAY() formula only returns an “ERROR!” message for each record. Any suggestions?

I’m trying to gather information from employees about what teams they are in and how many hours they are available to spend on them that week. I’m using a form to collect the names of the teams using a link to another spreadsheet where they can select from a list of all the teams. They then have to input the number of hours they have available to spend on that team further down. I wanted to add dependencies so that these would only show once you have selected a team but that’s not possible so I am trying to find out if I can select all of the teams they are in by checking if the hours they have inputted for a team are more than 0. Any ideas would be greatly helpful thanks :slightly_smiling_face:

I’m trying to set some automation for warranty dates that if there is nothing selected it leaves it blank. But if the warranty drop down is selected it will read whats in the Warranty Start Days and apply the correct length of time to the date for the expiration date. Other than googling I don’t have much experience with coding. Sorry. Thanks for any guidance IF(Warranty=BLANK(),BLANK(), IF(Warranty= 1 Year), DATEADD({Warranty Start Date}, 365, "day") IF(Warranty= 2 year), DATEADD({Warranty Start Date}, 730, "day") IF(Warranty= 3 Year), DATEADD({Warranty Start Date}, 1095, "day"))

I am trying to extract an ID number from a URL field. The number is at the end of the URL. I have tried MID, RIGHT & LEFT but no luck. The formula will need to: get the number only Ignore other numbers in the URL Get the number right of the last ‘-’ Ignore a ‘/’ if there is one at the end Example data: https://airtable.com/shrM2cPJoq05OMoom Example URLs: ‘https://www.eventbrite.com.au/e/long-name-of-workshop-here-could-be-very-long-48271930495|48271930495’ ‘https://www.eventbrite.com.au/e/shorter-name-859045958274|859045958274’ ‘https://www.eventbrite.com.au/e/medium-length-name-95827485904/|95827485904/’

Hey guys, I made the following formula on Google Sheets: =LOOKUP(WEEKDAY(J2),B1:H1,B2:H2) Where J2 has the =Today() function, B1:H1 tells the function what day of the week it holds and B2:H2 what its operating hours. I am looking to use the lookcup function on Airtable, and depending on =today() date find out from the spreadsheet a respective operating hour of a certain restaurant. Thanks.

I am using the program to help me in the production of a play, and thus far its great. I have one thing that I want to do with the program that I have not been able to figure out and I was wondering if anyone had any advice. A quick background: I have a “Company” table with casting that includes Actors and Roles. Two other tables (Songs and Scenes) are linked to “Company” where I select roles which are in either the “Song” or the “Scene”. I also have a Lookup Column on these tables so that my Actors appear in those tables as well. I would love to build a “Daily Call Table” from this and I was wondering if it is possible to create a Lookup column that can contain values from Actors in all three tables (“Company” “Songs” and “Scenes”). This would enable me to input actors into one column by selecting them individually (“Company”) or by group (“Scene” and “Song”) Or to create three Lookup columns that I can combine (and then hide the original columns). Thanks everyone

I am trying to use the CONCATENATE Function to create a primary field entry that returns something that looks like: Lot-123b234 [Product Variety] Seems pretty straightforward, but, the resulting entries come back with variable text formatting that seems to be related to the syntax of the text in one of the fields I am using. For instance: (This is good) --> if a particular entry in a text field being concatenated is Product Variety it works fine, (This is bad) --> if a particular entry happens to have a Variety, Product syntax, the CONCATENATE Function result always shows “Variety, Product” (with the quotation marks magically appearing instead of just showing Variety, Product as I want it to) What am I doing wrong? How can I convince the computer not to add quotation marks where I have not typed them myself? I need to retain the flexibility of the concatenated field to have text entries with both types of syntax and I don’t want the CONCATENATE Formula result to add quotation m

Is there a way to set column 2’s values to be a random value from Column 1? Let’s say I have 5 records. There are 5 values in Column 1. I would like to pick a random value from Column 1 and set that as a value for each record on Column 2.

Hi there - I’m trying to create a formula that will remove the double quotes and comma from the middle of a string, and although it works on one field, it doesn’t work on another. IF(LEFT({Text}, 1)=’"’, MID({Text},3, LEN({Text})-2), {Text}) That works for one field, but not another. What am I getting wrong, or is there another, better way? Thanks!

I use the ARRAYUNIQUE formula often. It works great with text, but not well with numbers. I am failing to pull a unique value that is found within a cell/column. For example, if a cell contains “Tuesday, Tuesday, Tuesday,” I can use the ARRAYUNIQUE formula in a new column to show only “Tuesday” However, I cannot get the same to work in a number format. Any suggestions? I am using a lookup based on a field and it is pulling multiple numbers that are not unique.

Hello and thank you in advance if you can help with this. The rate chart example we use below, I would like to be able to pick filed cat1, cat2, cat3, etc and then another field zone1, zone2, zone3, etc in another field enter the miles and have the formula / base out put the correct rate. z1 z2 z3 z4 z5 z6 z7 cat1 20.06 26.81 35.06 1.09 1.09 1.09 1.09 cat2 27.56 34.31 44.81 1.24 1.24 1.24 1.24 cat3 $82.50 82.5 90 1.13 1.13 1.13 1.13 1.13

I’m trying to roll up the smallest value from Table A into Table B that’s at least “1” or greater with MIN(values) rollup formula. The problem right now is some is table A has values of “0”, and I don’t want to return that number. Example below: TABLE A record #: 4 record #: 2 record #: 15 record #: 21 record #: 0 My formula returns the smallest number in table A, which technically is “0”, but I don’t want that number, and I’m trying to figure out a formula to exclude any value of 0 or less.

All of my tasks have different duration times it takes to complete. I need a formula for auto populating the due date of the specific task based on the type of task. I.E. if task=email populate the due date -10 days from the launch date. I have about 15 different tasks all with different completion duration’s. Can someone help with this?

Hello! I’m trying to create a really simple roll up field that spits back the max(value) for dates in another table. I have looked at other posts for recommendations, but continue to get 0’s. I think the issue is that the date field the roll up field is pulling from is a formula itself (date another field was last modified). So my question is, how can you do a rollup formula on another formula field? Or am I missing something else? Thanks!

Hi there. I currently have two tables. My {School} table includes a column called “Age Group” which is a Multiple select field. This field provides the selection options of Pre-School, Year 1, Year 2, Year 3 etc. I have another table called Statistics with a rollup linking back to the “Age Group” field in my {Schools} table. I want to create a rollup or other formula that looks at all the schools from Bedfordshire, then counts to see how many of them have “Year 1” etc selected in the Age Group field. This to then be displayed as a total. Any help would be greatly appreciated.

I’m trying to write a formula that would format a long text to a short formatted text. Example: “We offer just the drawings and floor plan in addition or exclusively as a service” to “floorplan-only” The tricky part is there are 4 different types: Service Type (Single line text) Nothing, we are a turn-key service, from site prep, permits, to completion The client has some responsibility like permits, site-prep, and transportation We just send the kit of parts and the client takes care of the majority of work We offer just the drawings / floor plan in addition or exclusively as a service And I want to change it to the following based on the above text: Formatted (Formula) turn-key door-step kit-of-parts floorplan-only What I want is the formula to pull any of the four fields and format it. For example, if the service type was all four, the formatted cell would have: turn-key; door-step; kit-of-parts, floorplan-only. I’m not sure how to add that type of logic and how to add “;” in-be

Formula not producing anything. What did I do wrong? I have spaced this out so it is easier to read. {Bank / Lender Owned?} is a check mark. If it is not checked, I want to base the answer from the AOC field. If it is checked, I want to base the answer off the B2B field. Any possible result should either be 500 or 1000.

Hello, I have a base where I need to produce a report/view each day between 05:00 - 05:00 rather than a midnight to midnight (yesterday) view. Each entry has a Opened Date field which includes the time in the field. Is there a formula which I can use to extract the time element so that only records created in the last 24 hr period are shown between 5am - 5am? Hopefully that makes sense? Many thanks, Jason…

I’m working on an Airtable to track film festival submissions and I need help generating a formula. I have two date fields, an “OPEN” date field and a “DEADLINE” date field. I fill in the day submissions open and when submissions are due. If TODAY is after OPEN, then I’d like the field to say “OPEN”. If TODAY is after DEADLINE, then I’d like the field to say “CLOSED” Any help would be greatly appreciated. Thanks!

Hi guys, I have a formula that I want to update automatically if a condition is met - IF({U/C Date}=TODAY(), “yes”, “no”) Based on the answer to that formula being “yes” I am trying to use integromat to send an email. It is my understanding that the today() function will not update until the base is reloaded or a record is edited, therefore my question is - can I use zapier or integromat to update the base on a schedule each day and therefore have the today() function update without the need for a human to open Airtable? Any suggestions would be really helpful. Thanks,

I have a service where users can give their friends a referral code and if a new user signs up using a code, both referrer and referee get a bonus. I have a Users table that includes a field for a unique referral code generated for each user. This is the code they can share with their friends. If someone signs up using one of these codes, it gets writtten to a referer code field for the new users record - this value will always correspond with a referral code from a pre-existing user record in the same table. For each referal code I want to calculate the number of times its been used by a referree - whch mean counting the number of times that same code appears in the referrer code field in my Users table. What’s the best way to calculate this value?

I have the formula below to give us a due date for items, BUT when the item is due I would like to add an emoji OR change the field highlight color. Can anyone help me figure out what I need to add to this formula in order to make this happen?? DATEADD({2-Step TB},11,‘month’)

I’ve got dates from two different columns being drawn into a main column that I need to be able to filter and sort. To get the data into the main column I cobbled together different formulae from this forum, however when I try to format the column it gives me the error message “Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.” The formula I’m using is: IF( {Policy End Date} = “”, DATETIME_FORMAT(DATEADD({Contract End copy}), “LL”),DATETIME_FORMAT(DATEADD({Policy End Date}), “LL”)) The {Contract End copy} and {Policy End Date} columns are look-ups from two other tables and I also can’t format this data because of the same error message around not being a number or date. Does anyone have a suggestion around changing the formula so that I can format the column for dates? Thanks.

Hi guys! I’m very new to Airtable and so far I’m loving it! However, I’m having an issue with formula’s. I have a feeling I’m on the right track, just not quite getting it. I have a table called Schools. This table contains all the data relating to each school such as school name, email, phone, pupil total etc. I have created a field called Beds Total Schools with a formula stating County=“Bedfordshire”. This then populates that field with 1 if the record has Bedfordshire in it’s County field. I can then use this to create a Summary count in my Dashboard called Total Bedfordshire Schools. I can do this for any other county too. The issue I’m having is as follows. I want to create a field that counts the record’s Pupil Total field IF the County field says “Bedfordshire”. I’m sure it’s something like COUNT {Pupil Total} IF {County}=“Bedfordshire”. I can then create a summary from the new field called something like {Total Beds Pupils}. Any help would be greatly appreciated!! Thanks in ad