Formulas

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 ap

I want to write something that reads something like, “IF Number is less than 2100 but greater than 1600 than “Awesome”, IF number is less than 1600 than “Perfect””. I’m not sure how to so this…

Good afternoon everyone, all right? I really need your help because I don’t know how to relate the columns … I put a picture of what I need. When someone searches for something, they get information from those who have something just like what they are looking for. |15|Procuro|Casa|Venda|2|230000| 47 > result| |47|Tenho|Casa|Venda|2|230000|15, 50 > result| |50|Procuro|Casa|Venda|2|230000|47 > result|

Hi everyone, Can someone help me make a formula to extract text. How can you isolate the text and make it to its own field in excel from the following : (1.5" X 7" 14PT Matte/Dull Finish Postcards w/ Spot UV on back only, No UV on the front) TO: Field 1: 1.5" x 7" ; Field 2: 14PT; Field 3: Matte/Dull Finish

I have afield with inventory numbers. There numbers start with a bin letter designator and then a number. ex, A2, G22, AA15, AB21 I was using the LEFT function to grab the First character to identify the bin letter, but now that I have grown and now have bins AA, AG, etc. How to I have have Airtable grab just the letters out of the field or grab two letters is the inventory has one? Thanks,

This is my formula IF(DATETIME_DIFF(TODAY(),{purchase-date},'d')<1,DATETIME_DIFF(TODAY(),{purchase-date},'h'), DATETIME_DIFF(TODAY(),{purchase-date},'days')) Why do I get all this results? Why is all this numbers negative?

I am putting together a base for some schoolwork. I have a field for Points possible and points earned. Then I have a formula field with the following formula to calculate the grade percentage (Earned/Points*100) displayed as an integer. My problem is that not all assignments are graded so the ones that are not come up with NaN. Is there any workaround that would just leave it blank or 0. Thanks for any help you can offer.

Hi there, So I have a Table were I am tracking Costs in one field and in another field I have Financial Years. Financial Year 19/20 Costs $100 Financial Year 20/21 Costs $200 What I would like to be able to do create another field where I calculate the growth from 19/20 to 20/21: Cost Growth 100% I’m really not sure how to go about this. Does anyone have any suggestions? Thanks in advance.

Hello World! I’m curious if anyone has a BurnDown chart solution. The logic I’m currently using is not effective.

Hi. I’m new to Airtable and trying to find a formula that will count the number of spaces in a string. For example, when this formula is applied to the string “How are you doing?”, I want the formula to return 3. I tried using the FIND function, but all that does is to find the position of the first instance of, say, a space in this example. I’m looking for a formula to count the number of spaces. Thank you.

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!!