Formulas

Hi Everyone, I have a field called “GL value” that is a number between 0 and about 100. I’d like to have a separate field that does the following: If GL value is equal to or over 20 = :x: High GL If GL value is between 11 and 19 = :orange_circle: Moderate GL If GL value is 10 or less.= :white_check_mark: Low GL Can someone help me write this? This is where I’ve got to: IF(OR({GL Value}>=20), " :x: High GL”), IF(AND({GL Value}>=11, {GL Value}<=19), " :orange_circle: Moderate GL”), IF({GL Value}<=10, " :white_check_mark: Low GL”) Thanks so much!

Dear ones, I need help. I’m using a filter on another system that searches the result based on a certain date. If I inform the filter as follows. Date = “2021-06-25”, The expectation is that I would return all the rows that appear on this date, however filtering this way I have no return. If I use the Date filter >=“2021-06-25”. it works, but it brings me back not only the lines corresponding to that date but all the other dates ahead.

Hello yall! Got a quick quesiton here! My goal is to connect airtable with hubspot through zapier but when there import of info occurs there is a weird inflow of info due to internal problems between connecting the two that spits out code instead of text. My goal is to translate such code as lets say “100011010” into blue for example where each value of x that gets imported automatically has another column that can spit out y values. so similar to vlook in excel. Please let me know how it can be done

Hello everyone, Tinkering with my garden automations and I have seasons on my mind. There’s a couple of items I’m working on and thinking about, and figured I may as well post my questions here to help advance my Airtable skills. Firstly, spending time with a Switch statement, is it possible to stack cases? For example; SWITCH(Month{Date} ,1,"Summer" ,2,"Autumn" ,3,'Autumn' ,4,'Autumn' ,5,'Winter' ,6,'Winter' ,7,'Winter' ,8,'Spring' ,9,'Spring' ,10,'Spring' ,11,'Summer' ,12,'Summer') SWITCH(Month{Date} ,[1,2,12],"Summer" ,[3,4,5],"Autumn" ,[6,7,8],'Winter' ,[9,10,11],'Spring') And what are peoples thoughts on having rolling seasons within a recurring calendar? Not too sure about this one though, trying to map it out still, so perhaps it’s more of a base design question. :thinking: DATETIME_PARSE(DATETIME_FORMAT({Recurring Date},'MM/DD')&"/"&YEAR(NOW()))

Hello All! I am using a base to pair students based on shared interests they selected in a multiple select field. I am then trying to tell them what interests they had in common. I am easily able to do this when they selected all of the same options using IF({Are you interested in being paired based on any of the following interests?}={Are you interested in being paired based on any of the following interests?(from People)},{Are you interested in being paired based on any of the following interests?}) The problem I then run into is finding the interests they do have in common when there are additional topics they chose beyond the common ones. Here’s a picture of my fields to better help explain what I’m talking about. Thanks for any help or thoughts!

Column A: Active (Checkbox) Column B: Expiry Date (Date Field) Column C: Date Created (Date Field) Column D: Package (Single Select) Contains two packages 2 Week Subscription, 1 Month Subscription I would like for: If the customer has a 2 week subscription enabled then the Expiry Date becomes Date Created + 14 Days If the customer has 1 month subscription enabled then the expiry date becomes date created + 31 Days If the customer’s subscription is Expired (After the expiry date) the Active checkbox must be deselected. If the customer subscription is not expired (Before and on the expiry date) the active checkbox must be deselected. Can Airtable do this?

hi dear airtable community, i have a colum named “size” and it contains for example the following text “45 1/3 EU - 11 US - 250€”. now i am trying to find a function that will delete everything after “EU”, so it looks like “45 1/3 EU”. i already saw a bunch of formulas in here but somehow none worked. downloaded the scripting app by airtable and added codes like “LEFT({size}, SEARCH(“EU”, {size})-1)” but then i get an error message saying " ERROR ReferenceError: LEFT is not defined at main on line 1" and i dont know what it actually means. any help is highly appreciated, thanks for your time and help! Kind regards Karim :slightly_smiling_face:

I would like to Combine this become Below Result : 65 Kodi (Seri warna) , 10 Kodi (Pilih Warna) If I simply using Concatenate For This it simply Show like This I am Using This Formula CONCATENATE({Jumlah Pesan (from Pesan Ke Sup)},{Satuan Pesan},{VarianPesanan (from Pesan Ke Sup)}) Is There Any Can Help , TQ

Any way to rewrite this formula? Instead of looking at State for “BC”, using {City} SWITCH( {State}, “CT”, “New England”, “ME”, “New England”, “MA”, “New England”, “NH”, “New England”, “RI”, “New England”, “VT”, “New England”, “NJ”, “Mid-Atlantic”, “NY”, “Mid-Atlantic”, “PA”, “Mid-Atlantic”, “IL”, “Midwest”, “IN”, “Midwest”, “MI”, “Midwest”, “OH”, “Midwest”, “WI”, “Midwest”, “IA”, “Midwest”, “KS”, “Midwest”, “MN”, “Midwest”, “MO”, “Midwest”, “NE”, “Midwest”, “ND”, “Midwest”, “SD”, “Midwest”, “TX”, “Southwest”, “OK”, “Southwest”, “LA”, “Southwest”, “AR”, “Southwest”, “DE”, “South Atlantic”, “FL”, “South Atlantic”, “GA”, “South Atlantic”, “MD”, “South Atlantic”, “NC”, “South Atlantic”, “SC”, “South Atlantic”, “WV”, “South Atlantic”, “VA”, “South Atlantic”, “DC”, “South Atlantic”, “KY”, “Southeast”, “AL”, “Southeast”, “MS”, “Southeast”, “TN”, “Southeast”, “CA”, “Pacific”, “AK”, “Pacific”, “HI”, “Pacific”, “OR”, “Pacific”, “WA”, “Pacific”, “AZ”, “Mountain”, “CO”, “Mountain”, “NM”

How to get the first digit of a number? Like “4” from “413”

I’d like the numbers in a numeric column to be unique. Is there a way to check with a formula whether the new entry already exists in the column, no matter how many items there are? The result can appear in the column itself if possible, or in an extra field like for errors. I mean if there is a field with 1 2 3 …and I add a fourth item, which is 1 again – it should not be possible or return a warning

hi dear community, i need help finding the correct formula, but at least i know that the mid and find function have to be included. i want to extract the text between both “-”, so for example “11 US” is visible in the “us size field”. any help is greatly appreciated, if there are any questions regarding my concern, please let me know. kind regards karim and thanks in advance! :slightly_smiling_face:

so this is my current Formula - IF({Sold Platform}=“GOAT”,{Unit Sold Price}-({Unit Sold Price}*.095)-5,"") I am trying to have this result in a $dollar amount. I’ve looked through the community pages but I’m struggling to get the desired result. Can anyone help?

Hi, guys! I couldn’t find a Roadmap for Airtable (I suppose it doesn’t exist) and I’d like to know if Airtable has plans to add financial formulas to the current list of formulas. I have a table where I collect some financial indicators and I’d like to do some basic calculations like standard deviation, variance, etc. I can’t do that in Airtable and I have to use Excel for that. I know Airtable isn’t a spreadsheet, but I hope developers could add more functionalities….

Hello community, I have a question about trying to create a schedule. We have created jobs that has an x amount of hours to do those jobs. So I have a start date for example 7/1/2021 and the job is to take 20 hours which would be two 10 hour days. When I try DATEADD({Start},{Duration},‘hours’) or replace ‘hours’ for ‘days’ it literally adds 2 days for a completion date of 7/3/2021 but that’s wrong. It should be 7/2/2021. I tried to format the start date to include a time, however, Airtable looks at everyday as a 24 hour day. So then I come up with start time of 7/1/2021 7:00am and completion date/time of 7/2/2021 3:00am. Is it even possible to create business hours so when calculating start and completed by the dates are correct? Because ultimately I’m trying to set my start and duration as a Gantt view to show my forecasted schedule.

Hi, I am in need of some guidance on a formula. I have two columns that I am comparing against each other - basically determining the dollar difference between the two and then the % difference that amounts to. Once that is figured out, I want to grade the resulting % as pass or fail, but I want to do it as a range. Here is an example: A) Final Estimate: $10,000.00 B) Preliminary Estimate: $9,000.00 C) Difference in $: ($1,000.00) D) Difference in %: (10%) Here is where I need a formula: IF the result of D is within a range of 10% under to 10% over the Final Estimate then the grade would be “Pass”. IF the result is either more than 10% below or over 10% above the Final Estimate, then the result is “Fail”. So I want to allow a spread of 10% each way off of matching the numbers as allowable. Any help would be appreciated. Thanks!

Hey all, I’m trying to build a formula right now that generates copy based on various fields, specifically a date field. Some of these values have a date and others do not. Whenever I try to build the formula using DATETIME_FORMAT({Presale Start}, "ddd, M/D") values that don’t have a date show as #ERROR!. That being said, I tried using an IF statement but it won’t even save the formula. Can’t tell what I’m going wrong but this is the latest version I’ve tried to use: IF({Presale Start}=BLANK(), “0“, IF{Presale Start}!=BLANK(), DATETIME_FORMAT({Presale Start}, "ddd, M/D") Any help would be much appreciated!

I am considering purchasing AirTable Pro. But before I do, I want to make sure that I am able to automatically populate a calendar with AirTable Pro. Certain jobs I have are weekly and routine, and I want to set dates (Mon, Tues, Wed, etc) and have all jobs show up in my calendar for the select days.

Hi all! I am trying to create a master calendar with two categories: 1) reminders on when to contact someone and 2) program application deadlines, which are in two separate tables. My approach to consolidating these is to create a new table called Master Calendar where records are automatically created based on conditions about the other tables. I’ve run into an issue with the second category, app deadlines. Issue For the program app deadlines, I created an automation that adds creates a new record with the program and deadline in two columns in the master calendar table. The issue here is that the date that appears in deadline column of the master calendar table is the date the record was created, not the deadline. The automation I used is: when deadline column is not empty in Table A → create a record in master calendar with fields → Program Name (Table A) = Name (Master Calendar) and Deadline (Table A) = Deadline (Master Calendar). It may be important to note that I’m pulling automa

Hopefully one of you amazing pros can help me resolve an issue with a nested IF Formula that isn’t quite doing what I hoped it would. I have four individual products (Line Item #1 through to Line Item #4) and if they are ‘Espresso Blend’ I’d then like their total weight {Total Coffee Weight (G)} to be added together to give me the total amount of Espresso Blend across all of the products combined. For reasons I can’t quite understand the below formula isn’t very happy when the Line Item isn’t Espresso Blend. Rather than throwing the desired ‘0’ into the calculation it is cancelling out everything but the first item to include Espresso Blend. Any help would be greatly appreciated! IF({Line Item #1 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #1 Quantity},0 + IF({Line Item #2 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #2 Quantity},0 + IF({Line Item #3 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #3 Quantity},0 + IF({Line Item #4 Coffee

Hello! I have a table tracking agent info and testing statuses, and I need to create a Current Status and a Current Date & Time field. Imagine a table where (among other things) you have 6 fields, 3 single-selects and 3 date & time fields. Each pair of single-selects & date & time fields represents a testing attempt. So we have 1st Attempt Status, 1st Attempt Date & Time, 2nd Attempt Status, 2nd Attempt Date & Time, 3rd Attempt Status, & 3rd Attempt Date & Time. I need a formula/automation that always shows the current status & date & time of the current attempt. Preferably into 2 fields, 1 for the current status and 1 for the current date & time. I need it to keep up if it changes too, i.e. Scheduled changes to Pass/Fail I have an automation currently to watch each Status field and if they’re updated, it will return the most recently edited information BUT, if it’s updated out of order, (2nd attempt to Scheduled, THEN 1st attempt to Fail) yo

I have a table like this: | level | exp | |1|0| |2|500| |3|1,500| |4|3,750| |5|7,875| |6|14,175| |7|22,680| |8|32,886| |9|44,396| |10|57,715| |11|72,144| |12|90,180| |13|112,725| I want a formula that outputs the right level when an exp number that is within a range is given. For example: With an input of 700, the formula should display 2 because the closest available exp value (rounded down) to 700 is 500 and the corresponding level is 2. With an input of 10,000, the formula should display 5 because the closest available exp value (rounded down) is 7,875 which corresponds to level 5. With an input of 33,886, it should output 8. The solution in sheets would be something like (with E2 being the input): =IF(ISNUMBER(E2),INDEX(SORT(A:A,B:B,0),MATCH(E2,SORT(B:B,B:B,0),-1)),) or =QUERY(B:A,"select A where B >= "&E2&" order by B limit 1",0) How is it possible to do this in Airtable? Thanks!

I’m try to create a new field (Event End Date) based on the frequency of the Event (Event Day) and when the Event Day Started (Event Start Date). I started using the formula below but it doesn’t work. In addition, I need to code for Event Days where it’s a single day (i.e., “Thursdays”, “Fridays”, etc.) in which case the Event End Date = Event Start Date. IF( FIND ( "Tue - Fri", {Event Day}), DATEADD({Event Start Date},4, ‘days’)), IF( FIND ( "Tue-Fri", {Event Day}), DATEADD({Event Start Date},4, ‘days’)), IF( FIND ( "Wed - Sat", {Event Day}), DATEADD({Event Start Date},4, ‘days’)), IF( FIND "Tue - Thur”, {Event Day}), DATEADD({Event Start Date},3 , ‘days’)), IF( FIND "Tue - Thu”, {Event Day}), DATEADD({Event Start Date},3 , ‘days’)), IF( FIND "Wed-Fr”, {Event Day}), DATEADD({Event Start Date},3 , ‘days’), IF( {Event Day} = "Thur - Fri", DATEADD({Event Start Date},2, ‘days’)),

I am sending URL’s from Webflow which does not have URL validation easily integrated. I want to check and add the https:// to the beginning of the URL if it is not present when the field is created or updated. I have been looking through to forums but can’t seem to find an exact match. Would love some help with this please.

Hi everyone - I want to create a formula that will return a date value only if the value in a separate field {campaign duration (days)} is greater than 10. I also want the field to stay “clean” even if the bounding dates (Live and End) aren’t filled in. Please find below the formula I’ve created: IF({Campaign Duration (Days)} < 10,"",IF(AND({Campaign Live Date},{Campaign Duration (Days)}),WORKDAY({Campaign Live Date},{Campaign Duration (Days)}))) Unfortunately the formula returns the following: 2021-08-19T00:00:000Z. Can anyone tell me what I’m missing? The date itself is correct but I’m not even sure what the rest of the value is. I thought at first that this was a formatting issue but according to Airtable the result isn’t a number or a date so it can’t be reformatted.