Formulas

I have four Airtable fields {Total Cost}, {Event Date & Time}, {Equipment Charge}, {Event Duration}, and {Number Of Techs}If the {Event Date & Time} happens during the Day time, the rate is $69.If {Event Date & Time} happens during the Evening the rate is $79.If the {Event Date & Time} happens during any holidays, the rate is $105.If the {Event Date & Time} happens during a weekend, add $60 to the {Total Cost}.I created a formula to calculate the {Total Cost} but it is not working. Your help will be very appreciated.See the Formula below:------------------------------------------------IF(DATETIME_FORMAT({Event Date & Time}, 'h A') >= '7AM'AND(DATETIME_FORMAT({Event Date & Time}, 'h A') < '7 PM'),{Event Duration} * {Number Of Techs} * 69,IF(DATETIME_DIFF({Event Date & Time}, DATETIME_PARSE('2023-01-01'), 'days') IN [0,1,2,361],{Event Duration} * {Number Of Techs} * 105,{Event Duration} * {Number Of Techs} * 79)) + IF(DATETIME_FORMAT({Event Date &amp

HiI've more than 10,000 questions and counting, and many questions added daily, the issue is finding and labeling then deleting the duplicate, duplicate are not exactly unique same question written with similar words but different context , and sometimes exactly the same but with extra word.i used the 2 duplicate extensions and it is very useful but it need too much manual work (see attachment )so my question, what Formula can i use to label any question in separate column and flag the question as duplicate and linking the duplicate form with lookup/rollup, so i could ? i wish the result to be like that . thank you , i will really appreciate your answers.

Hi,In table Courses, I have a "Related Lessons" field that is a link to table Lessons"Related Lessons" field contains 3 Lessons' records: L1, L2, L3In table Courses, I want to create a second field, named "Open Related Lessons" that contains 2 linked records only: L1, L2. The remaining L3 records should be excluded based on filters.--> How to achieve that?

Hi everybody, I'm trying to come up with a formula that would add after some text (taken from another field) the number of spaces needed to reach a total number of character.Let's say I have to get to 10 total character, starting from the text from a field name "Text".If text is "ab" I would need the formula to add 8 spaces after "ab" in order to reach 10 total charactersIf text is "abc" the formula should add 7 spaces after "abc" to reach 10 total characters, and so on.Is this possible?

Hello there,I have been using Airtable for a month and I absolutely love it.I am creating a basic HR system. I want to create a formula for the employees’ paid leaves.How do I automatically make it add up in Airtable, using the Hours off (8 hours/day)?Is there a formula for that?Thanks in advance.

Hey guys, I am new to AirTable. Here is the problem I am facingI have 2 tables, Apartments and Locations1. An apartment can only have 1 Location.2. A location can have multiple apartments. I want to do an API call to filter apartments with some contraints which will return me a max of 100 apartments.It is possible that all those 100 apartments belong to 1 location so my end result is 1 location. Is there any way I can add a filter that returns me unique values?Eg. If apartment 2 has the same Location as Apartment 1, return it just once and continue. I want my end result to be 100 locations in 1 API call.

Hi!I was wandering if it is possibile to use customers' emails as unique ID through with connect different tables (ex. Table Contact, with only info about the customers; table Customer journey, with info about first contact, conversions,...)Thank you!

I'm new with these and I think i'm having a big mistake but I cannot find it.I'm trying to extract the number of hours for an specific duration record.Obviously I use HOUR command but it doesn't work!As you can see in screenshots, the field Hours works in the following way:Hour({Time})But, it generates ERROR!What am I doing wrong?Thanks!  

Hi all, is it possible to have a formula that searches if a text field contains at least one item of an array?Specifically, I would like to achieve the following:I define topic names and related keywords for each one manually in table #1:TABLE 1: TOPICName (string) | Keywords (Multi-select) Animals | dog, cat, elephant,Fruits | apple, banana, orangeColors | red, blue, yellowI have some articles in another table and I want to update the Topic field by checking if the article title contains items of the array field from table 1.TABLE 2: ARTICLESTitle (string) | Topic (Lookup) (this should be the field updated by the formula)The dog is sleeping | AnimalsThe apple is red | Colors, FruitsI know I can use a formula and manually define keywords combining IF, AND, OR, FIND etc.However, topics and keywords frequently change, so I should update the formula every time a new topic enters the table.Instead, I want a formula that searches if any item of an array (Keywords field) is contained in

Hi Airtable ExpertsWe have a formula field field which calculates the conversion from euro to dollar, including the import duty. The outcome is shown in USD (field  "exwUsdStandard" = currency, 2 decimals to the right), this works fine.The next field ("exwUsdSpecial" = currency, 2 decimals to the right) shows a price in USD), is shown perfectly in the record. This value is not calculated with a formula.Now, I want to combine these two fields into a new field ("priceExwUsd"), however, I am not able to format it as currency $ with 2 decimals at the right side of the ",".What can I do to make it as currency, $, 2 decimals to the right?Thank you in advance 😄Gerben (from the Netherlands)  

Hi,I am needing help creating a formula.It would be if (15% referral says no then it would calculate the daily rate times 15%), if (15% referral says yes then it would be daily rate)

I need help finding all formulas used in a base that would have used 2022 in the formula to either point to 2023, or re-write.  If i need to export all formulas to review, that would work too.

I'm trying to build a formula that will look up a currency value from a record, determine whether it is between two ranges of currency, and then enters a percentage (the federal poverty level) in a new column. I'm getting errors in the formula below. Any ideas about what I'm doing wrong? IF(  {yearly income} < 22763,    "125%",    IF(AND(      {yearly income} > 22763, < 27315),        "150%",        IF(AND({yearly income} > 27315, < 36420),           "200",          IF({yearly income} > 36420,             "Over 200%,      &nbs

Hi, I have a users table with a sigle select column called "status" with the values "Not verified" and "Verified". When I create a new record, the Status field should automatically be set to "Not verified". Does anyone know how I can do this? (Couldnt find a solution). I tried an automation, but this is too slow. I am using Softr.io which accesses that field right after the row is created. That slight delay of the automation, already is too late. Many thanksSimon

Hey,Have some trouble with the summary bar. I’m trying to put up a sales report where I need to see the average in percent. According to the former Excel sheet against airtable I get two different values.In the Excel sheet I get an average of 10,9% and in airtable I get the average 11,83%, using the formula (((sum - {Brt.f.}) / {sum}) -1)*(-1)

the circled part is the focus… let take for instanceIf the "display attachment field" is not empty and the "cloud hosted field" is empty, a formula to display the display attachment field and not the cloud link fieldIf the "display attachment field" is not empty and the "cloud hosted field" is not empty, a formula to display one of the field

Hi there! Essentially, I'm triggering content to "publish" to a site 1 hour after it's been approved. How do I write a formula for 1 hour AFTER the Accepted-time field?  

So I use airtable to organize content for my websites and it’s been great. I initially started using it to create a web archive for a artist syndicate that needed a place to put up all their media, which is where I first encountered this issue. When uploading to wordpress using WP All Import Pro I need to have a bare url, but airtable dumps context into the field when you query the url. I was able to get around this using a regular expression to search for the beginning of the actual url and exclude everything before and ignore special characters. That worked fine considering that I only needed 1 attachment per post. Well now, I’m doing something similar to my own photo/video site and have created a portfolio post type where I can upload multiple photos into a gallery for each post. Thus I have multiple attachments per record in airtable, which the regular expression does not work with since there are multiple urls. So my question is how can I get airtable to spit out a completely bare

Hi,I have a few dates and want to generate the day of the week initial based on the date in a column.If the date is 6/29/2023, then it would return Th.I've tried If and Switch, but my formulas are definitely wrong I think because it is related to a date. Ideas?

use case, crop planning.Tables:1. crop reference (list of crops, planting times, etc.) this reduces having to put crop info (spacing)2. inventory (i can link to a crop and add the "variety" info, quantities etc.) this worked GREAT!3. planting schedule (date of planting, variety planted.) I just need to pull ONE variety by choosing from the varieties listed in the inventory. i want this to be fed by available inventory which i'll update regularly.so in the planting schedule, i want to enter date planted, then choose which one variety i planted using a single select that is populated with the values of the varieties. the tables are successfully linked and i have the lookup values, i just want to choose ONE variety per planting date. i think i might be linking to the wrong table? or formula needed? see attached...I can't find where "crop copy" is? 

I am working on a field that pulls a result based on a number range.   I aim to convert the number of days from a person's hire date and an incident date to a set phrase such as "Less than 3 Months",  "4 Months to 1 Year", etc.   My first field converts the difference between their hire date and the incident date to the number of days.   My thought was to create a formula that says if the number of days is between 0-90, then the result is "less than three months," and so forth.   My fields are "Hire Date", "Incident Date", & "DateDiff".  The field I need to formulate is "Length of Service".   What is the best approach to get my outcome?  

Here is my formula which I need to add a few more statements too.    IF(AND({LOS2} >= .0, {LOS2} <.26), "First 3 Months", IF(AND({LOS2} > .25, {LOS2} < 1.00), "Within 1 Year", IF(AND({LOS2} >= 1.00, {LOS2} < 2.00), "1 - 2 Years", IF(AND({LOS2} >= 2.00, {LOS2} < 3.00),"2 - 3 Years",  IF(AND({LOS2} >= 3.00,{LOS2} < 4.00,"3 - 4 Years")))))) Here is a screen shot of the table.  It isn't capturing the last statement in the formula; it is returning nothing. It should return 3-4 Years.  What am I missing?     

I'm having issues getting a formula to round down prices to the nearest $2.50.I've tried FLOOR() FLOOR({Price}, 2.5)and ROUNDDOWN()ROUNDDOWN({Price},2.5)but without any success. Thank you in advance.

We are using the "Groups" feature which is available on the enterprise tier, and would like to see what formulas are available when a group is used in a "user" type field. Example:Column 1: type User - selecting "Group A"Column 2: type Formula - automatically showing "Person 1", "Person 2", "Person 3" that are part of "Group A".Not sure if a formula exist to expand groups into users as shown above, so how to go from column 1=> 2? Many thanks.

Hi everyone I have a primary field that’s a formula, concatenating from other fields, date-name-contributor(s) The contributors field is a linked field, and the formula relies on the lookup field “contributor last name”. So if the contributors column is “John Smith, Jane Doe” the lookup would show “Smith, Doe”. However, the formula will combine the last names without a comma, displaying “SmithDoe”. I’ve tried making a new formula field that takes from the lookup, but using that formula field in the primary field formula returns the same result. Is there any way to get the data in a linked field to be used in a formula with comma formatting? Thank you!